Efficient Criteria Matching

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
chris12295
Forum Contributor
Posts: 113
Joined: Sun Jun 09, 2002 10:28 pm
Location: USA
Contact:

Efficient Criteria Matching

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

It should be possible to do with subqueries or a clever self JOIN.
chris12295
Forum Contributor
Posts: 113
Joined: Sun Jun 09, 2002 10:28 pm
Location: USA
Contact:

Post by chris12295 »

Good idea, could you give pseudo-code that you think is the most efficient?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

not really... try some stuff first; we'll nudge.
User avatar
johno
Forum Commoner
Posts: 36
Joined: Fri May 05, 2006 6:54 am
Location: Bratislava/Slovakia
Contact:

Post by johno »

Would you mind posting at least your table schema?
chris12295
Forum Contributor
Posts: 113
Joined: Sun Jun 09, 2002 10:28 pm
Location: USA
Contact:

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
johno
Forum Commoner
Posts: 36
Joined: Fri May 05, 2006 6:54 am
Location: Bratislava/Slovakia
Contact:

Post 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
chris12295
Forum Contributor
Posts: 113
Joined: Sun Jun 09, 2002 10:28 pm
Location: USA
Contact:

Post 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?
Post Reply