Many to Many Challenge

wowdezign · #1 (**permalink**) 01-20-10, 09:21

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.

mike_bike_kite · #2 (**permalink**) 01-20-10, 09:50

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

r937 · #3 (**permalink**) 01-20-10, 10:24

Quote:

Originally Posted by wowdezign

`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

wowdezign · #4 (**permalink**) 01-20-10, 13:41

Quote:

do you seriously anticipate more than four billion properties?

Quote:

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.

Quote:

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.

r937 · #5 (**permalink**) 01-20-10, 13:51

Quote:

Originally Posted by wowdezign

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

mike_bike_kite · #6 (**permalink**) 01-20-10, 14:40

Quote:

Originally Posted by wowdezign

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