[wp-hackers] Sorting queries by values in custom array?

[Mike Schinkel]

Than,

You can do it all in one query SQL and then even cache the results in a custom table since there are only 1600 permutations.  Here's a related question I asked on WordPress Answers:

http://wordpress.stackexchange.com/questions/468/optimizing-a-proximity-based-store-location-search-on-a-plain-vanilla-web-host

It could also be done with WP_Query() by adding post_where and post_join hooks but there's no need to use them for this since: 

1.) You'll be mucking with the SQL in the hooks anyway, 
2.) You'll be calling the query directly (i.e. it's not part of the loop) so you can have full control,  and
3.) The hooks could potentially cause a side effect elsewhere (although what I say next makes this point a bit moot.)

The reason for the custom table (and for not using something like the transients API) is you'll need to join it to your loop query with the posts_join hook, and set the order by using and posts_orderby hook.

Hope this helps.

-Mike

On Aug 19, 2010, at 12:27 PM, Nathaniel Taintor wrote:

> I'm working with about 40 cities to start, in two categories... 30 US and 10
> international, which get processed differently (i.e. someone searching in
> Tokyo doesn't need to see San Franciso as a "nearby city", just because it
> may be the closest geographically.
> 
> The sorting cities function performs ok, and I can cache the results of that
> function so it doesn't get run every page load. The big slowdown is running
> 20-30 get_posts queries on every index page. And it makes it very difficult
> and slow to page the results. I've been trying to come up with a hack to
> return these results in a single query that can be paged.
> 
> Thanks.
> 
> 
> 
> On Thu, Aug 19, 2010 at 11:35 AM, Mike Schinkel <mikeschinkel at newclarity.net
>> wrote:
> 
>> I was working on almost this exact problem yesterday.
>> 
>> How many total cities do you have?
>> 
>> -Mike
>> 
>> 
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers