2

I'm using the Containable behavior to get a list of Comments (belongsTo Post, which belongs to Question; Question hasMany Post, and Post hasMany Comments; all of these belong to Users).

$data = $this->Question->find ( 'first', 
    array ('contain' => 
        array ('User', 
               'Post' => array ('User', /* 'order' => 'User.created DESC'*/ )
        ) 
    ) 
);

It works, when I comment out the section in comments above. I suppose this is to be expected, but what I want is all of the Posts that are found, should be sorted in order of the 'created' field of the 'User' they belong to. How do I accomplish this deeper level sorting in CakePHP? I always get, "Warning (512): SQL Error: 1054: Unknown column 'User.created' in 'order clause'"

Thanks for your help!

KB22
  • 6,899
  • 9
  • 43
  • 52
atp
  • 30,132
  • 47
  • 125
  • 187

5 Answers5

3

Also, you might be trying to group on a related table from a find call that doesn't use joins.

Set your debug level to something greater than 1 so you can see the query log and make sure that Cake isn't doing two queries to fetch your data. If that is the case then the first query is not actually referencing the second table.

If you want to manually force a join in these situations you can use the Ad-Hoc joins method outlined by Nate at the following link.

http://bakery.cakephp.org/articles/view/quick-tip-doing-ad-hoc-joins-in-model-find

Abba Bryant
  • 4,012
  • 22
  • 18
  • I looked at the debug output and you're right, it's not performing a `JOIN`. This is unfortunate, is there really no other way to get CakePHP to do it? – atp Jan 19 '10 at 06:25
  • http://teknoid.wordpress.com/2008/07/17/forcing-an-sql-join-in-cakephp/ Has a workaround from teknoid on how to force a left join by unbinding the hasMany and replacing that with a temporary hasOne which does use joins. Read it and let me know if that works for you, otherwise I can post the ad-hoc joins code for you to look at. – Abba Bryant Jan 19 '10 at 15:32
  • glad to help, sometimes finding what works in CakePHP is hard. There is a ton of old, out-dated info out there and the good stuff is sometimes buried. – Abba Bryant Jan 20 '10 at 01:12
1

I have found two ways to get around this. The first is to define the second level associacion directly in the model. Now you will have access to this data everywhere. It should look something like this.....

var $belongsTo = array(
'Foo' => array(
  'className' => 'Foo', //unique name of 1st level join ( Model Name )
  'foreignKey' => 'foo_id', //key to use for join
  'conditions' => '',
  'fields' => '',
  'order' => ''
),
'Bar' => array(
  'className' => 'Bar', //name of 2nd level join ( Model Name )
  'foreignKey' => false,
  'conditions' => array(
    'Bar.id = Foo.bar_id' //id of 2nd lvl table = associated column in 1st level join
  ),
  'fields' => '',
  'order' => ''
)
);

The problem with this method is that it could make general queries more complex than they need be. You can thus also add the second level queries directly into te find or paginate statement as follows: (Note: I found that for some reason you can't use the $belongsTo associations in the second level joins and will need to redefine them if they are already defined. eg if 'Foo' is already defined in $belongsTo, you need to create a duplicate 'Foo1' to make the association work, like the example below.)

$options['joins'] = array(

array('table' => 'foos',
  'alias' => 'Foo1',
  'type' => 'inner',
  'conditions' => array(
    'CurrentModel.foo_id = Foo1.id'
  )
),
array('table' => 'bars',
  'alias' => 'Bar',
  'type' => 'inner',
  'foreignKey' => false,
  'conditions' => array(
    'Bar.id = Foo1.bar_id'
  )
)
);

$options['conditions'] = array('Bar.column' => "value");
$this->paginate = $options;
$[modelname] = $this->paginate();
$this->set(compact('[modelname]'));

I hope this is clear enough to understand and that it helps someone.

Dieter Gribnitz
  • 5,062
  • 2
  • 41
  • 38
0

Check your recursive value. If it's too limiting, it will ignore the containable links, IIRC. I remember bumping into this a few times. I'd try containing multiple models, but my recursive option was set to 0 and nothing would get pulled. For your example, I'd think that a value of 1 (the default) would suffice, but maybe you've explicitly set it to 0 somewhere?

Rob Wilkerson
  • 40,476
  • 42
  • 137
  • 192
  • I actually leave the recursive option out, since I believe Containable will choose the most appropriate option for you (if you specify it, things can only get worse). – atp Jan 19 '10 at 05:55
  • That's not been my experience. It's been a while, but I vaguely recall losing hours at one point because I assumed the same thing. It could be that my memory is inaccurate on this point. – Rob Wilkerson Jan 19 '10 at 12:35
0

You can add before your call to find() the following:

 $this->Question->order = 'Question.created DESC';
pcp
  • 1,918
  • 11
  • 11
  • 1
    This will sort the Question by its `created` field, which is not what I want. Perhaps you meant `$this->Question->Post->order = 'User.created DESC';` ? – atp Jan 19 '10 at 06:24
  • Anyway, I tried purohit's example but it doesn't seem to work. Perhaps it's setting the sort order on a different instance of the model than what the Question model actually uses...? – Simon East Jan 18 '12 at 01:24
0

Yeah, I couldn't work out how to sort based on the related/associated model, so ended up using the Set::sort() method. Checkout this article for a good explanation.

// This finds all FAQ articles sorted by:
// Category.sortorder, then Category.id, then Faq.displaying_order
$faqs = $this->Faq->find('all', array('order' => 'displaying_order'));
$faqs = Set::sort($faqs, '{n}.Category.id', 'ASC');
$faqs = Set::sort($faqs, '{n}.Category.sortorder', 'ASC');

...And yes, it should probably be a Category->find() but unfortunately the original developer didn't code it that way, and I didn't wanna rework the views.

Simon East
  • 55,742
  • 17
  • 139
  • 133