Posted by Nick Zukin on 04/29/05 01:17

I have a request from a client that has me a little confounded. He wants to
have a system where when someone posts a public request with certain
criteria, that criteria is then used to find established profiles/agents
that fit the criteria. Then emails are sent out to the people corresponding
to those profiles/agents.

An example:

Someone posts an ad saying they have a car for sale:

MODEL: Chevy
MAKE: Silverado
YEAR: 2005

Meanwhile, potential clients have saved search agents so that when a car is
posted if the description meets their criteria they are sent an email. Thus,
you could have three people, each with agents such as these:

PERSON1
MODEL: Chevy
MAKE: Silverado
YEAR (Newer than): 2003

PERSON2
MODEL: Chevy
MAKE: Any
YEAR (Newer than): 2004

PERSON3
MODEL: Any
MAKE: Any
YEAR (Newer than): 2000

You would like all of these people to be emailed after the ad is posted, but
how? This won't work:

SELECT * FROM agents WHERE mymodel = "Chevy" AND mymake = "Silverado" AND
myyear <= 2005

It will limit the results too much. But neither will this:

SELECT * FROM agents WHERE mymodel = "Chevy" OR mymake = "Silverado" OR
myyear <= 2005

That will give too many results. If you had a profile such as:

PERSON4
MODEL: Chevy
MAKE: Corvette
YEAR (Newer than): 2003

They would be sent the email, too. But they aren't looking for trucks.

Two options I see would be to:

1) Do it in reverse. Each time a new ad is posted to then loop through the
agents doing a search for the criteria and limiting the search to only the
previously posted ad, such as:

SELECT * FROM ads WHERE admodel = "Chevy" AND admake = "Silverado" AND
adyear >= 2003 AND adid = 8

2) Create some crazily complex query with nested ands and ors out the wazoo.


Is there something I'm not seeing? Is there a better way? If not, which of
these seems like less of a strain on the server?

TIA. Hopefully I'm not just being an idiot here.

Nick

[Back to original message]