Datamapper: Something like where_in_related, but including all

Datamapper: Something like where_in_related, but including all - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Libraries & Helpers (https://forum.codeigniter.com/forumdisplay.php?fid=22)
+--- Thread: Datamapper: Something like where_in_related, but including all (/showthread.php?tid=58203)

Datamapper: Something like where_in_related, but including all - El Forum - 05-23-2013

[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?

Datamapper: Something like where_in_related, but including all - El Forum - 05-27-2013

[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?

Datamapper: Something like where_in_related, but including all - El Forum - 05-29-2013

[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);