Page 1 of 1

Efficient Criteria Matching

Posted: Mon Dec 04, 2006 4:49 pm
by chris12295
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?

Posted: Mon Dec 04, 2006 5:05 pm
by feyd
It should be possible to do with subqueries or a clever self JOIN.

Posted: Mon Dec 04, 2006 5:13 pm
by chris12295
Good idea, could you give pseudo-code that you think is the most efficient?

Posted: Mon Dec 04, 2006 5:16 pm
by feyd
not really... try some stuff first; we'll nudge.

Posted: Tue Dec 05, 2006 2:57 am
by johno
Would you mind posting at least your table schema?

Posted: Tue Dec 05, 2006 10:36 am
by chris12295
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.

Posted: Tue Dec 05, 2006 10:42 am
by feyd
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.

Posted: Tue Dec 05, 2006 11:50 am
by johno
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

Posted: Sat Dec 16, 2006 2:53 pm
by chris12295
The above code doesn't match anything. It returns results of people wanting things that user1 is not offering. I have tried modifying it and changing it around but I cannot get it to work. Can anyone help me make this work?