Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2009
    Posts
    7

    Unanswered: Many to Many Challenge

    I have the following for properties:

    Code:
    CREATE TABLE `properties` (
      `property_id` bigint(20) unsigned NOT NULL auto_increment,
      `property_title` varchar(127) default NULL,
      `property_description` text,
      `property_summary` varchar(255) default NULL,
      `property_keywords` varchar(255) default NULL,
      `property_lat` double default NULL,
      `property_lon` double default NULL,
      `property_beds` decimal(4,1) default NULL,
      `property_baths` decimal(4,1) default NULL,
      `property_sleeps` decimal(4,1) default NULL,
      `property_approved` binary(1) NOT NULL default '1',
      `property_active` tinyint(1) NOT NULL default '1',
      `complex_id` smallint(5) unsigned default NULL,
      `type_id` smallint(5) unsigned NOT NULL,
      `vicinity_id` smallint(5) unsigned default NULL,
      `destination_id` smallint(5) unsigned NOT NULL,
      `user_id` bigint(20) unsigned NOT NULL,
      PRIMARY KEY  (`property_id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;
    And available amenities:
    Code:
    CREATE TABLE `amenities` (
      `amenity_id` smallint(5) unsigned NOT NULL auto_increment,
      `amenity_name` varchar(31) NOT NULL,
      `amenity_active` tinyint(1) unsigned NOT NULL default '1',
      PRIMARY KEY  (`amenity_id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;
    And a cross reference table:
    Code:
    CREATE TABLE `property_x_amenity` (
      `property_id` bigint(20) unsigned NOT NULL,
      `amenity_id` smallint(5) unsigned NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    I have an "advanced search" form that allows the user to check off all amenities they are looking for when they submit a search.

    I am having trouble coming up with a way to find all properties that have ALL of the submitted amenities.

    Any suggestions? If clarification is needed, let me know.

    Thanks.

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by wowdezign
    I am having trouble coming up with a way to find all properties that have ALL of the submitted amenities.

    Any suggestions?
    Could you just count the number of amenities the user has asked for. Then do a join between the properties, amenities for each property and the amenities requested. Just show the properties where the count(*) of the property amenities is the same as the number of amenities entered.

    Another method might be to list the top 10 properties ordered by count(*) of matching amenities - so it will show the best matches first even if they don't have everything required. Another improvement might be to have a priority on each type of amenity (ie number of garage might get 10 while lawn deck chair might get 1) then combine this with the last suggestion to show the best matches.

    Mike

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by wowdezign View Post
    `property_id` bigint(20) unsigned NOT NULL auto_increment,
    do you seriously anticipate more than four billion properties?

    if not, you can speed up your application by using INTEGER instead of BIGINT

    if every person in the united states of america owned ten properties (ludicrous assumption) and every single property was listed with you (even ludicrouser) then you still wouldn't have four billion properties

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Dec 2009
    Posts
    7
    do you seriously anticipate more than four billion properties?
    if every person in the united states of america owned ten properties (ludicrous assumption) and every single property was listed with you (even ludicrouser) then you still wouldn't have four billion properties
    No. Sorry. I didn't mean to irritate you.

    I still don't make the connection between your answer and my question though. Other than the possibly implied response that I am too dumb to deserve one.

    Again, sorry for the stupidity on my part.

    Could you just count the number of amenities the user has asked for. Then do a join between the properties, amenities for each property and the amenities requested. Just show the properties where the count(*) of the property amenities is the same as the number of amenities entered.
    Thanks for your reply. I wasn't clear with my question. I do that sometimes.

    If I have 6 possible amenities, say DVD, Internet, Outdoor Pool, Indoor Pool, Jacuzzi, Tennis Court

    and the user checks off that they want DVD, Jacuzzi, and Internet, I don't want to receive all properties with any three of the six amenities.

    I want to get all properties with the exact three amenities checked.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by wowdezign View Post
    No. Sorry. I didn't mean to irritate you.
    you didn't

    i hope you aren't irritated, either

    mike described the method for doing what you want -- give it a try
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by wowdezign View Post
    and the user checks off that they want DVD, Jacuzzi, and Internet, I don't want to receive all properties with any three of the six amenities.

    I want to get all properties with the exact three amenities checked.

    So the user has selected 3 amenities (DVD, Jacuzzi and Internet). Now join your property and property_amenity table where the amenity is one of those above. Then total up the results to show the number of amenities per property and just display those having a count(*) of 3.

    Quote Originally Posted by wowdezign
    I still don't make the connection between your answer and my question though
    The types you are using ie bigint(20) imply some huge amounts of data.

Posting Permissions

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