0

i'm trying to access only those cities where country id = 2 and city id = 5

$this->Country->find('all', array(
        'conditions' => array('Country.id' => 2),
        'contain' => array(
          'City' => array(
            'conditions' => array('City.id' => 5)))));

I tried this to get city with id 5 but all cities are listed where country id is 2.

I know this is not good approach to get cities using this query . I can use 

$this->City->findById('5');
$this->City->find('all', 
           array('conditions' => array('id' => 5 , 'country_id' => 2)));

But I want to know if I wanna use above way then how it can be used in proper way because some where else it can be used in future.

coder
  • 156
  • 3
  • 23

1 Answers1

0

What you're looking for is JOINs.

When you use contain in the 1st example, you can basically think of it like two queries (which is what it runs most of the time using Containable):

1) get all Countries of id 2
2) get all Cities of id 5

(Notice how it's NOT joining them together - it does two completely separate queries, then just puts them both into an array)

Instead, use INNER JOIN to create a single query where it only returns the results that match both conditions.

Dave
  • 28,833
  • 23
  • 113
  • 183
  • can you help me to write inner join query for this situation – coder Mar 16 '15 at 08:27
  • If you post another question with your attempts at writing a join, I'd be happy to help point out where you're off..etc. – Dave Mar 16 '15 at 14:39
  • i have posted another question, kindly check it here http://stackoverflow.com/questions/29092064/how-use-multiple-conditions-to-get-data-from-2-habtm-tables-in-cakephp-2-x – coder Mar 17 '15 at 05:45