Cakephp 2: Select only entries which have a connection in HABTM table

#1

Hi,

I really dont know who to solve the follwing problem:

I got 3 tables: a, b, and a_b. a is the main table and b is a table with different flags. a_b is the habtm connection table.
Lets say I have a few entries in my flag table b and there are connected via the a_b.

Table a
ID | NAME
---------------------.
1 | first
2 | second

Table b
ID | NAME
-------------------.
1 | flag1
2 | flag2
3 | flag3

Table a_b
A_ID | B_ID
-----------------.
1 | 1
1 | 3
2 | 2
2 | 3

SO now I would like to select only entries from table a which have a flag1 and flag3 (so it should only return “first” from table a). MYSQL “IN” doesnt work here. How could I do this? My research only gave me this solution and I dont know how to implement it in cake: http://stackoverflow.com/questions/6121779/mysql-subset-operation

#2

I might can help you out. However, in your example you said that you only want to return entires from table a that have flag1 and flag3. According to your example, “second” also has flag3. However, the way you would return entries based on their flag records, would be something like this I think:

Assuming your model names are A and B, and your making the call from your AController and using the containable behavior…

$this->A->B->find('all', array( 'conditions' => array('name' => array('flag1', 'flag3')), 'contain' => array('A') ));

I haven’t tested this, but I think this should return flag1 and flag3 with there associated A entries. I’m not sure if that’s the array structure you will want though. There are probably other ways to do this.