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