|
|
Posted by Richard Lynch on 04/29/05 03:12
On Thu, April 28, 2005 3:17 pm, Nick Zukin said:
> 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
But this will:
WHERE (mymodel = 'Chevy' OR mymodel = 'Any')
AND (mymake = 'Silverado' OR mymake = 'Any')
AND (myyear <= 2005 OR myyear IS NULL)
You probably should use NULL to represent 'Any' and just use IS NULL in
all three.
> 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.
This, at least, is clearly addressed by the above.
I can't guarantee it will address *EVERY* profile -- Only time and
practice will tell you that.
> 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
This won't work any better if the ad doesn't bother to list Make or Year
or...
Plus, you haven't really addressed the issue of somebody who wants a Chevy
(no make specified) newer than 2003.
> 2) Create some crazily complex query with nested ands and ors out the
> wazoo.
I wouldn't say my query was crazily complex...
But if it is, then so be it.
> 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?
Create keys on all the search columns.
--
Like Music?
http://l-i-e.com/artists.htm
Navigation:
[Reply to this message]
|