Datamapper: Something like where_in_related, but including all

[eluser]avramovic[/eluser]
Ok, I'm working on a realestate website and I have few tables (of importance here) such as:

properties: id, title, desc, price,
amenities: id, name
amenities_properties: id, amenity_id, property_id

Now, within a search there are checkboxes that are used to select amenities that must be present in a property. So, in my controller I had something like:

Code:

$results = new Property();
$results->distinct();
//other search queries go here...
$results->where_in_related_amenity('id', (array)$amenities_ids);
//other search queries go here...
$results->get();

This gets all properties that have at least one of the selected amenities, but I need only the properties that have them all!

So I thought something like this will solve my problems:

Code:

$results = new Property();
$results->distinct();
//other search queries go here...
$results->group_start();
foreach ($amenities_ids as $am_id)
{
   $results->where_related_amenity('id', (int)$am_id);
}
$results->group_end();
//other search queries go here...
$results->get();

Unfortunately, this returns no properties in my $results, although I'm sure at least one of them had amenities I tested with. The generated query has no errors but retrieves nothing. Here it is:

Code:

SELECT DISTINCT `properties`.*
FROM (`properties`)
...
LEFT OUTER
JOIN `amenities_properties` amenities_properties ON `properties`.`id` =
`amenities_properties`.`property_id`
WHERE
...
AND  (
`amenities_properties`.`amenity_id` = 24
AND
`amenities_properties`.`amenity_id` = 9
)

Any other ideas how to get properties that have ALL selected amenities related to them?

[eluser]avramovic[/eluser]
OK, I did some research and I've found out that I need to make multiple joins of the amenities_properties table for each checkbox checked... something like this:

Code:

...
LEFT JOIN `amenities_properties` ap1 ON `properties`.`id` =
ap1.`property_id`

LEFT JOIN `amenities_properties` ap2 ON `properties`.`id` =
ap2.`property_id`

WHERE
ap1.`amenity_id` = 9 AND
ap2.`amenity_id` = 24

Is this even possible with Datamapper? Or is there another (simpler) way to do this?

[eluser]avramovic[/eluser]
OK, I'm looking like lunatic replying to myself constantly, but here's how I've done it, in case someone else needs similar functionality:

Code:

$results->where_in_related_amenity('id', $am_ids);
$results->group_by('id');
$results->having('COUNT(DISTINCT `'.$this->db->dbprefix.'amenities_properties`.`amenity_id`) =', count($am_ids), false);