get_previous_post_join and get_next_post_join cannot be used in certain cases

[Cimmo]

get_previous_post_join and get_next_post_join cannot be used in some cases because if someone add a INNER JOIN with another table that has the ID column too then SQL return the error:

WordPress database error: [Column 'ID' in field list is ambiguous]
SELECT ID, post_title FROM wp_posts INNER JOIN wp_a51_posts AS a51_posts ON wp_posts.ID=a51_posts.POST_ID WHERE post_date < '2007-07-12 12:31:13' AND post_type = 'post' AND post_status = 'publish' AND a51_posts.LEVEL<=-1 ORDER BY post_date DESC LIMIT 1

I suggest to change these rows in get_previous_post function in link-template.php:

line 325:
$join = " INNER JOIN $wpdb->post2cat ON p.ID= $wpdb->post2cat.post_id ";

line 346:
$where = apply_filters( 'get_previous_post_where', "WHERE p.post_date < '$current_post_date' AND p.post_type = 'post' AND p.post_status = 'publish' $posts_in_ex_cats_sql", $in_same_cat, $excluded_categories );

line 347:
$sort = apply_filters( 'get_previous_post_sort', 'ORDER BY p.post_date DESC LIMIT 1' );

line 349:
return @$wpdb->get_row("SELECT p.ID, p.post_title FROM $wpdb->posts AS p $join $where $sort");

similar can be done into get_next_post function:
lines: 362, 383, 384, 386

[Cimmo]

Patch against 2.3 beta1, check it please

[westi]

Patch for svn diff so trac understands it and it has context

[westi]

Patch looks interesting.

Ryan can you give feedback on this - my SQLfu is not upto it today ;-)

[ryan]

Looks good.

[westi]

(In [6019]) Change queries in get_{next|previous}_post so that plugins can INNER JOIN on other tables with an ID column. Fixes #4822 props Cimmo.