CakePHP: How do I order results based on a 2-level deep association model?

I'm hoping I won't need to resort to custom queries.

A related question would be: how do I retrieve data so that if an associated model is empty, no record is retrieved at all, as opposed to an empty array for an associated model?

As an oversimplified example, say I have the following models: City --> Street --> House

How do I sort City results by House numbers?

How do I retrieve City restuls that have at least one House in it? I don't want a record with a city name and details and an empty House array as it messes up pagination results.

CakePHP retrieves House records belonging to the Street in a separate query, so putting somthing like 'House.number DESC' into the 'order' field of the search query returns a 'field does not exist' error.

Any ideas?

From stackoverflow

• For your first question I think you can order the results by specifying the order in the model association.

  For example, from the Street Model:

  var $hasMany = array('House'=>array('order'=>'address desc'))
  

  It would be hard to get Cities with at least one house in it without a custom query, so you may be better off going that route instead of spending so much time trying to get it to work in cake.

  KcYxA : Thanks. I avoided a custom query by using an inner join that filtered out unwanted results before cake went ahead to fetch related models.

• Before doing the actual query operation in your controller, set the order clause in the model.

  $this->City->Street->hasMany['House']['order'] => "houseNo DESC";
  

  KcYxA : What is the Street also has an 'order' condition? Which one takes precedence and can it be controlled?

  matiasf : You could concatenate the conditions. Not tested: $this->Model->hasMany['OtherModel']['order'] => "field DESC" . $this->Model->hasMany['OtherModel']['order'];