I have two tables. The first one named Users and has an id, name, etc. The second one named Field and has an id, name, etc. These two tables have an N:M relationship that means a user can have multiples favorite fields and a field can have multiples followers. The relationship makes a third table named UsersFields and has an id, user_id, field_id.
The problem is that I need to get the name and id of a field associated to a user_id, in other words, all the user favorite fields. I have a SQL query like that. I'm not absolutely sure if that works. If I have a $user_id = 1 for example.
Select F.name, F.id
from Fields F, UsersFields UF
where UF.user_id = $user_id
and F.id = UF.field_id
I am new in CakePHP and I've been looking for a solution, but I can't make it works.
Now I have something like that.
$fields = $this->loadModel('Fields');
$query = $fields->find()->contain('UsersFields', function ($q) {
return $q
->select(['field_id'])
->where(['UsersFields.user_id' => $id]);
});
But in the view, I get all the Fields rows.
Anyone can help my, please.
Edit
Thanks to ndm I can resolve the question with https://stackoverflow.com/questions/26799094/how-to-filter-by-conditions-for-associated-models