⚠ In case you've missed it, we have migrated to our new website, with a brand new forum. For more details about the migration you can read our blog post for website migration. This is an archived forum. ⚠

  •     

profile picture

Need help in set_relation_n_n custom query



yooghe

yooghe
  • profile picture
  • Member

Posted 22 October 2013 - 12:35 PM

I have two tables 

 

citytable:   city_id, city_name

 

routetable:   route_id, fromcity_id, tocity_id

 

I would like to show two fields

fromcity_id is first field which is a combo where all city_name values will be displayed.

 

for that I wrote fallowing code.

 

$crud->set_table('routetable');
$crud->set_subject('Route');
$crud->set_relation('city_id','citytable','city_name');
 
for second field
tocity_id, I would like to display two lists.
in one list I want show all the values of tocity_id which are not exists in the route for the selected value in the comobo.
 
  select city_id  from citytable,routetable where
                                                   city_id notequalsto (value selected in firstcombo)
                                                         and
                                                   city_id notequalsto (select tocity_id from routetable where fromcity_id equalsto  (value selected in firstcombo) )
 
 
in anotherlist I want to show all the tocity_id which are already exists in the route for the selected value in the combo.
 
  select tocity_id from routetable where 
                                                   tocity_id qualsto (value selected in firstcombo) 
 
 
Now I can move values by selecting value from first list and add them to second list. I need this type of relation. which i saw in set_relation_n_n. but there are three tables relation.
here I need from two tables only.
 
one more alternative even if I can able to display values of first list  which means of first query in a combo as a second field then also my requirement will be satisfied, without using two lists.
 
Can you please suggest me the best practice using grocery crud.
 
 
Thanks,
 
Yooghe.