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

[Mike Schinkel]

I was working on almost this exact problem yesterday. 

How many total cities do you have?

-Mike

Sent from my iPad

On Aug 19, 2010, at 11:25 AM, Nathaniel Taintor <goldenapplesdesign at gmail.com> wrote:

> Hi all,
> 
> I'm working on a project that's basically a craigslist clone. Visitors
> choose their city on first visiting the site, which is stored in a cookie.
> On browsing the site, they see posts from their own city first, then from
> other cities, sorted by proximity.
> 
> I have 'City' set up as a custom post type, with custom fields for latitude
> and longitude. This post type is mirrored in a custom taxonomy, kind of the
> same way Mike S. was talking about recently on the list. My problem is in
> figuring out an efficient way of generating the queries to show posts. As it
> is now, my "find nearby cities" looks like this:
> 
> function sort_nearest_cities() {
>    global $wpdb;
>    $thiscity = $wpdb->get_var("SELECT id FROM wp_posts WHERE
> post_title='".BCD_CITY."'"); // BCD_CITY is the value of the cookie
>    $thislat = get_post_meta($thiscity,'lat',true);
>    $thislon = get_post_meta($thiscity,'lon',true);
>    $othercities = $wpdb->get_results("SELECT `ID`,`post_title` FROM
> `wp_posts` WHERE `post_type`='city'",ARRAY_A);
>    $othercities_sorted = array();
>    foreach ($othercities as $othercity) {
>        if ($othercity['post_title'] != BCD_CITY) {
>            $otherlat=get_post_meta($othercity['ID'],'lat',true);
>            $otherlon=get_post_meta($othercity['ID'],'lon',true);
>            $othercities_sorted[$othercity['post_title']] = (int) sqrt(
> pow((69.1*($otherlat-$thislat)),2) +
> pow((69.1*($otherlon-$thislon)*cos($thislat/57.3)),2) ) ;
>        }
>    }
>    asort ($othercities_sorted);
>    return array_keys($othercities_sorted);
> }
> 
> and then I loop through each of the results returned with a get_posts query
> for each. It works, but its a stupid brute-force approach that won't scale
> well at all.
> 
> What I'd like to do, given an array of nearby cities sorted, is somehow pass
> that to the get_posts query as a sort order field. But I'm drawing a
> complete blank. Is there a smart way of doing this? Maybe hooking into
> posts_where or posts_join?
> 
> Sorry if this is outside the scope of this list.
> 
> Thanks,
> Than
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers