In cakephp how can I do a find with conditions on a related field?

Question

I've got a model (Listings) that has and belongs to a few different models, I'd like to find all of this model where it's related model (Openhouses) have a condition. The 'has and belongs to' are setup in the model files. Listings hasmany Openhouses and Openhouses belong to Listings. (And listings has many and blongs to a few other models where I want the data.)

I've been trying.

$this->Listing->find('all', 
array('conditions' => 
array('Openhouse.date >' => $openhouse_start->format('Y-m-d H:i:s'),
'Openhouse.date <' => $openhouse_end->format('Y-m-d H:i:s'))
));

but to no avail.

Error: 1054: Unknown column 'Openhouse.date' in 'where clause

I know I can search on the Openhouse model and get related Listings but then the data is returned in a different format and I need to turn recursion way up to get data from my other models. (And I end up with duplicate openhouse data!). I can post some more code examples if needed.

My question is basically do I need to just query the openhouse model and live with it or is my syntax for putting conditions on related models incorrect?

This is really useful for me but is it also possible to sorting by the Openhouse.date field. When i tried in my example which is same with this one it has no sense.I debuged the queries and sorting field and the join is in separated queries. $this->List->find('all', array( 'contain' => array( 'Openhouse.conditions' => array( 'Openhouse.date >' => $openhouse_start->format('Y-m-d H:i:s'), 'Openhouse.date <' => $openhouse_end->format('Y-m-d H:i:s')) ), 'Openhouse.order' => array( 'Openhouse.date DESC' ) ) ) — , Jun 24 '09 at 13:52

score 2 · Answer 1 · edited Jul 15 '16 at 13:09

If you have a lot of linked models, settings recursive to 2 might bring more data than you might want.

If that is true, there is an alternative to what mavarro said, you can also try using the Containable behaviour:

$this->Listing->find
(
    'all', 
    array
    (
        'conditions' => array
        (
            'Openhouse.date >' => $openhouse_start->format('Y-m-d H:i:s'),
            'Openhouse.date <' => $openhouse_end->format('Y-m-d H:i:s')
        ),
        'contain' => array('Openhouse')
    )
);

score 1 · Accepted Answer · answered May 02 '09 at 11:24

1

Try this:

$this->List->find('all', array(
    'contain' => array(
        'Openhouse.conditions' => array(
            'Openhouse.date >' => $openhouse_start->format('Y-m-d H:i:s'),
            'Openhouse.date <' => $openhouse_end->format('Y-m-d H:i:s'))
        )
    )
)

answered May 02 '09 at 11:24

Felix Geisendörfer

2,902
5
27
36

Works like a charm thanks! (sorry for the delay I got sick this weekend - but I'm finally back to work) – reconbot May 06 '09 at 19:48
could one also specify conditions on the top-level model (List) in this example? could I just add this 'conditions' => array('List.name LIKE'=>'foo%'), right after find('all', array( – Randy L Jun 23 '10 at 15:53

score 0 · Answer 3 · edited Jan 20 '10 at 02:42

0

$this->List->find('all', array(
    'contain' => array(
        'conditions' => array(
            'Openhouse.date >' => $openhouse_start->format('Y-m-d H:i:s'),
            'Openhouse.date <' => $openhouse_end->format('Y-m-d H:i:s')
                             ),
        'order' => array('Openhouse.date DESC')
                      )
                             )
)

edited Jan 20 '10 at 02:42

sth

222,467
53
283
367

answered Jan 19 '10 at 08:30

BiBi

1,271
2
8
2

score 0 · Answer 4 · answered May 02 '09 at 00:25

0

Try adding a $this->Listing->recursive = 2; before the call to find all. That should link up the tables before the call giving you access to the Openhouse model from the Listing model.

answered May 02 '09 at 00:25

rnavarro

173
1
4
12

In cakephp how can I do a find with conditions on a related field?

4 Answers4

Linked