CakePHP query needs to be sorted by JOIN column

Question

I'm working on a CakePHP project that's giving me some issue when it comes to query result order. There are three tables used here: Buildings (all the info on the building), Lists, and ListBuildings (a link table for building and list). I didn't build the application, and can't change the db structure unfortunately. I want $arrBuildings to be a list of the buildings in order by ListBuildings.created (meaning the moment a building was added to a list), and not by Building.name as it appears to be right now.

function getList($list_id) {   
// Get buildings in the list
$building_ids = $this->_getBuildingsForList($list_id);
$conditions = array(
        'conditions'=>array('Building.id'=>$building_ids),
        'contain' => array(
                'List'=>array('conditions' => array ('List.id'=>$list_id))));

// Get the list information
$this->Building->List->recursive=-1;
$List=$this->Building->List->read(null,$list_id);

$options = array(
    'conditions' => array('Building.id'=>$building_ids),
    'contain' => array(
        'List'=>array('conditions' => array ('List.id'=>$list_id)),
        'Sqft'
    ),
);


$this->Building->myId=$this->getUserId();
$arrBuildings = $this->Building->find('all', $options);
print_r($arrBuildings);
}

Since I'm using CakePHP, the model portion of MVC seems to be set up properly. With the code above, print_r shows the following array:

Array ( [0] => Array ( 
[Building] => Array ( 
    [id] => 105 
    [name] => Stark Tower 
    [address] => 123 Main St. 
) 
[Sqft] => Array ( 
    [0] => Array ( 
        [id] => 51 
        [list_id] => 113 
        [building_id] => 105 
        [sq_feet] => 2200.000 
    ) 
) 
[List] => Array ( 
    [0] => Array ( 
        [id] => 113 
        [title] => Awesome buildings 
        [ListBuilding] => Array ( 
            [id] => 95 
            [list_id] => 113 
            [building_id] => 105 
            [created] => 2012-10-18 09:40:00 ))))

To re-state: How can I order the query (and resulting array) by ListBuildings.Created? I've tried to anonymize the code, and may have messed something out, so let me know if anything doesn't make sense :) Thanks in advance for any feedback!

@BarryChapman, CakePHP joins these tables within its Model "hasandbelongstomany" structure. All this "automagical" stuff makes my head spin. — JamieHoward

Rob Forrest Rob Forrest · Accepted Answer · 2012-10-25T15:30:13

Having had a look at your function again, I think its best to start from scratch here.

Change your model relationships to:

Building belongsTo ListBuilding, ListBuilding hasMany Building, List belongsTo ListBuilding, and finally ListBuilding hasMany List.

Rewrite your function

    function getList($list_id) {   
    $options = array(
        'conditions' => array(
            'ListBuilding.list_id' => $list_id
        ),
        'contain' => array(
            'Sqft'
        ),
        'order' => 'ListBuilding.created',
    );

    $arrBuildings = $this->Building->find('all', $options);
    print_r($arrBuildings);
    }

It really doesn't have to be more complicated than that.

CakePHP query needs to be sorted by JOIN column

2 Answers