Something like where in, but to get all

Databases
1

We are developing real estate software and with new version we are launching amenities search. That means that each property can have unlimited number of amenities and each search can be based on checked amenity (smoking allowed, pets, parking etc).

Tables important for this problem:

  1. properties - id, title, description, price, …
  2. amenities - id, name
  3. amenities_properties - 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 we tried something like this:

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

This gets all properties that have at least one of the selected amenities, but we need only the properties that have them all! For example search is checked to search “smoking permitted” and “pets allowed”. The result will show all the properties that have “smoking permitted” even with pets allowed option not checked.

Then we tried something like this:

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

However, this shows no results. What we need is to show results that have all properties that have all the parameters checked. I hope this makes sense :slight_smile:

2 

select properties.*
  from properties
  join amenities_properties
    on properties.id = amenities_properties.property_id
 where amenities_properties.amenity_id in (9,15,24)
 group
    by properties.id
having count(*) = 3
3

Thanks a bunch Swamp.

This works nicely from phpMyAdmin, but we have no results in live search since we have large search query that is generated through ORM depending on checked options with more joined tables.

4

If you don’t post the real query, it’s hard to get a useful answer.
I’d say swampBoogie’s solution is the way to go, you’ll just have to find a way to implement it in your extended query.

5

With small modifications it is working without changing search query:

select properties.*
from properties
join amenities_properties
on properties.id = amenities_properties.property_id
where amenities_properties.amenity_id in (9,15,24)
group
by properties.id
having count(distinct amenities_properties.amenity_id) = 3

Thanks :slight_smile:

6

Great :slight_smile:

7

All looking good, but now we have problem with pagination since no properties are shown on the next page.