Efficient Criteria Matching
Moderator: General Moderators
-
chris12295
- Forum Contributor
- Posts: 113
- Joined: Sun Jun 09, 2002 10:28 pm
- Location: USA
- Contact:
Efficient Criteria Matching
I am trying to figure out the best way to make a matching script. Here is the issue:
I have user1 that wants x,y,z and can offer a,b,c
I have user2 who wants a,d,e and can offer x,m,n
These users match because user1 wants x and offers a and user2 wants a and offers x.
I want to be able to take what user1 wants, find who is offering it, out of those results, find who wants what user1 is offering, and show user1 the results.
I use MySQL for my database so any ideas for the approach that will lead to the least number of database queries?
I have user1 that wants x,y,z and can offer a,b,c
I have user2 who wants a,d,e and can offer x,m,n
These users match because user1 wants x and offers a and user2 wants a and offers x.
I want to be able to take what user1 wants, find who is offering it, out of those results, find who wants what user1 is offering, and show user1 the results.
I use MySQL for my database so any ideas for the approach that will lead to the least number of database queries?
-
chris12295
- Forum Contributor
- Posts: 113
- Joined: Sun Jun 09, 2002 10:28 pm
- Location: USA
- Contact:
-
chris12295
- Forum Contributor
- Posts: 113
- Joined: Sun Jun 09, 2002 10:28 pm
- Location: USA
- Contact:
The tables are for users that want to trade hunting and fishing trips. My tables are as follows:
Users
user_id
name
etc.
Trips
trip_id
user_id
location
description
etc.
Offered
trip_id
species_id
species_type ('anm','saltfish')
Wanted
user_id
species_id
species_type
Animals
anm_id
name
Saltwater_Fish
swf_id
name
Trying to match user with their trips, then, per trip, match their offerings to the wants of another user. Then for the other user, get their trips and matching offerings as long as they are offering the first user's wants.
If you can think of a better schema let me know. I think this one seems a little inefficient.
Users
user_id
name
etc.
Trips
trip_id
user_id
location
description
etc.
Offered
trip_id
species_id
species_type ('anm','saltfish')
Wanted
user_id
species_id
species_type
Animals
anm_id
name
Saltwater_Fish
swf_id
name
Trying to match user with their trips, then, per trip, match their offerings to the wants of another user. Then for the other user, get their trips and matching offerings as long as they are offering the first user's wants.
If you can think of a better schema let me know. I think this one seems a little inefficient.
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
It would seem that "animals" and "saltwater_fish" could be consolidated into a single table. Possibly with a specifier that denotes type, but otherwise each record would appear the same.
The basic solution concept hasn't really changed however.. it should still be possible with a clever JOIN or subqueries.
The basic solution concept hasn't really changed however.. it should still be possible with a clever JOIN or subqueries.
- johno
- Forum Commoner
- Posts: 36
- Joined: Fri May 05, 2006 6:54 am
- Location: Bratislava/Slovakia
- Contact:
Just a try.
Code: Select all
SELECT *, COUNT(wanted.user_id) AS relevance
FROM trips
JOIN offered ON offered.trip_id = trips.trip_id
JOIN wanted ON wanted.species_id = offered.species_id
WHERE trips.trip_id = ?
GROUP BY wanted.user_id-
chris12295
- Forum Contributor
- Posts: 113
- Joined: Sun Jun 09, 2002 10:28 pm
- Location: USA
- Contact: