$data findAll query in CakePHP checking values from second table

Question

Hello there, I have been stumped by CakePHP in how to query the DB in CakePHP and return things to $data only when the $data query table [id] has a matching [sub_id] in a second table

a standard query:

$data = $this->Table1->findAll(array("Table1.deleted" => "0"), null, "Table1.id DESC", 25, null, 1);

But I want to only have values put into $data when the $data['Table1']['id'] is found in ['table2']['sub_id']

Thanks!

Answer 1

If you have your relationships setup properly it should do this automatically. Can you paste your Model relationship setup for Table1 and Table2?

Answer 2

Supernovah -

Please clarify one thing for me: you write that you want to only have values put into $data when table1.id is found in table2.sub_id. Do you mean that table2.sub_id is a foreign key, linking to table1?

I think Beau is right -- if you have the models correctly linked, using a HABTM or belongsTo, etc., variable, the findAll should automatically pull the associated records from table2.

The final caveat is that the model associations are affected by the value of Model->recursive. If you have changed the value of the recursive property in your code, it would alter how deep the model relations are allowed to go on a given query.

HTH!

Answer 3

Beau is right, this should happen automagically if you have the relationships properly defined for the model.

Answer 4

In the model, in the relation array add:

$hasMany = array(
.....
'required' => true
....
);

This should make it do an inner join in sql rather than a left join. Hope this helps.