SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Guru Webinsane's Avatar
    Join Date
    Oct 2005
    Location
    Montenegro
    Posts
    898
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Question Something like where in, but to get all

    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:


    Code:
    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:


    Code:
    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
    CUBE SCRIPTS MEDIA
    REAL ESTATE SCRIPT 2.0 | Software for Real Estate Agencies

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    698
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    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. #3
    SitePoint Guru Webinsane's Avatar
    Join Date
    Oct 2005
    Location
    Montenegro
    Posts
    898
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.
    CUBE SCRIPTS MEDIA
    REAL ESTATE SCRIPT 2.0 | Software for Real Estate Agencies

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    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. #5
    SitePoint Guru Webinsane's Avatar
    Join Date
    Oct 2005
    Location
    Montenegro
    Posts
    898
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    With small modifications it is working without changing search query:

    Code:
    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
    CUBE SCRIPTS MEDIA
    REAL ESTATE SCRIPT 2.0 | Software for Real Estate Agencies

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Great

  7. #7
    SitePoint Guru Webinsane's Avatar
    Join Date
    Oct 2005
    Location
    Montenegro
    Posts
    898
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    All looking good, but now we have problem with pagination since no properties are shown on the next page.
    CUBE SCRIPTS MEDIA
    REAL ESTATE SCRIPT 2.0 | Software for Real Estate Agencies


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •