CakePHP database query - am I overcomplicating things?

Question

So, I need to search a real estate database for all homes belonging to realtors who are part of the same real estate agency as the current realtor. I'm currently doing this something like this:

$agency_data = $this->Realtor->find('all',array(
    'conditions'=>
        array(business_name'=>$realtor_settings['Realtor']['business_name']),
    'fields'=>array('num'), 
    'recursive'=> -1
));

foreach($agency_data as $k=>$v){
    foreach($v as $k=>$v1){
        $agency_nums[] = $v1['num'];
    }
}

$conditions = array(
    'realtor_num'=>$agency_nums
);

It seems a bit crazy to me that I'm having to work so hard to break down the results of my first query, just to get a simple, one-dimensional array of ids that I can use to build a condition for my subsequent query. Am I doing this in an insanely roundabout way? Is there an easy way to write a single CakePHP query to communicate "select * from homes where realtor_num in (select num from realtors where business_name = 'n')"? If so, would it be any more efficient?

Answer 1

For sure it's complicated (in your way) :)

Depending from the results you can do following:

 $agency_data = $this->Realtor->find('list',array(
    'conditions'=>array('business_name'=>$realtor_settings['Realtor']['business_name']),
    'fields'=>array('num', 'num'), 
    'recursive'=> -1
));
$agency_data; //this already contain array of id's

Method 2 - building a sub query there are 2 ways strict and not so strict :) The first one you can see here (search for Sub-queries).

The other option is to have following conditions parameter:

$this->Realtor->find('all', array('conditions'=>array('field in (select num from realtors where business_name like "'.$some_variable.'"))));

Of course be careful with the $some_variable in the sub-query. You shold escape it - use Sanitize class for example.

Answer 2

$agency_data = $this->Realtor->find('all',array(
    'conditions'=>
        array('business_name'=>$realtor_settings['Realtor']['business_name']),
    'fields'=>array('num'), 
    'recursive'=> -1
));

$conditions = Set::extract("{n}.Realtor.num", $agency_data);

I would use something like Set::extract to grab the list of data you are looking for. The advantage of doing it this way is that you can reuse the same dataset in other places and save queries. You could also write the set::extract statement in this format:

$conditions = Set::extract("/Realtor/num", $agency_data);