WordPress.org

Make WordPress Core

Opened 8 years ago

Closed 8 years ago

#4822 closed defect (bug) (fixed)

get_previous_post_join and get_next_post_join cannot be used in certain cases

Reported by: Cimmo Owned by: ryan
Milestone: 2.3 Priority: normal
Severity: normal Version: 2.2.2
Component: General Keywords: dev-reviewed
Focuses: Cc:

Description

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

Attachments (2)

link-template.php.diff (2.4 KB) - added by Cimmo 8 years ago.
Patch against 2.3 beta1, check it please
4822.diff (3.6 KB) - added by westi 8 years ago.
Patch for svn diff so trac understands it and it has context

Download all attachments as: .zip

Change History (7)

comment:1 @Cimmo8 years ago

  • Milestone changed from 2.4 (next) to 2.3

comment:2 @Cimmo8 years ago

  • Version set to 2.2.2

@Cimmo8 years ago

Patch against 2.3 beta1, check it please

@westi8 years ago

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

comment:3 @westi8 years ago

  • Owner changed from anonymous to ryan

Patch looks interesting.

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

comment:4 @ryan8 years ago

  • Keywords dev-reviewed added

Looks good.

comment:5 @westi8 years ago

  • Resolution set to fixed
  • Status changed from new to closed

(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.

Note: See TracTickets for help on using tickets.