|
|
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
Navigation:
[Reply to this message]
|