[wp-trac] [WordPress Trac] #25775: WP_Date_Query table prefixing

[WordPress Trac]

#25775: WP_Date_Query table prefixing
---------------------------------+-----------------------------
 Reporter:  ew_holmes            |       Owner:
     Type:  defect (bug)         |      Status:  new
 Priority:  normal               |   Milestone:  Future Release
Component:  Query                |     Version:  3.7
 Severity:  major                |  Resolution:
 Keywords:  has-patch 3.9-early  |
---------------------------------+-----------------------------

Comment (by neoxx):

 here you go. - code has been simplified for the sake of readability:

 {{{
 /*
 basic-set consists of
 all published posts
 */

 $query_params=array(
         'posts_per_page' => -1,
         'post_status' => 'publish'
 );

 /*
 custom taxonomy
 */

 if (!empty($_REQUEST['writer']))
         $query_params['writers']=esc_sql($_REQUEST['writer']);

 /*
 start_date and end_date
 */

 $date_query=array(
         'column' => 'post_date_gmt',
         'inclusive' => true
 );

 if (!empty($_REQUEST['start_date'])) {

         /*
         start_date will be the
         input date at 00:00:00
         */

         $date_query['after']=esc_sql($_REQUEST['start_date']).' 00:00:00';
 }

 if (!empty($_REQUEST['end_date'])) {

         /*
         end_date will be the
         input date at 23:59:59
         */

         $date_query['before']=esq_sql($_REQUEST['end_date']).' 23:59:59';
 }

 $query_params['date_query']=array(
         $date_query
 );

 /*
 query database
 */

 $query=query_posts($query_params);
 }}}

 results in the following sql statement

 {{{
 SELECT wp_2_posts.* FROM wp_2_posts INNER JOIN wp_2_term_relationships ON
 (wp_2_posts.ID = wp_2_term_relationships.object_id) LEFT JOIN wp_2_posts
 AS p2 ON (wp_2_posts.post_parent = p2.ID) WHERE 1=1 AND ( ( post_date_gmt
 >= '2011-05-17 22:00:00' AND post_date_gmt <= '2013-12-06 22:59:59' ) )
 AND ( wp_2_term_relationships.term_taxonomy_id IN (5) ) AND
 wp_2_posts.post_type = 'post' AND ((wp_2_posts.post_status = 'publish') OR
 (wp_2_posts.post_status = 'inherit' AND (p2.post_status = 'publish')))
 GROUP BY wp_2_posts.ID ORDER BY wp_2_posts.post_date DESC
 }}}

 and causes a mysql exception

 ''#1052 - Column 'post_date_gmt' in where clause is ambiguous''

 I'll attach the quick-fix which works in our multi-site setup

 with the fix the sql-statement looks like this:

 {{{
 SELECT wp_2_posts.* FROM wp_2_posts INNER JOIN wp_2_term_relationships ON
 (wp_2_posts.ID = wp_2_term_relationships.object_id) LEFT JOIN wp_2_posts
 AS p2 ON (wp_2_posts.post_parent = p2.ID) WHERE 1=1 AND ( (
 wp_2_posts.post_date_gmt >= '2011-05-17 22:00:00' AND
 wp_2_posts.post_date_gmt <= '2013-12-06 22:59:59' ) ) AND (
 wp_2_term_relationships.term_taxonomy_id IN (5) ) AND wp_2_posts.post_type
 = 'post' AND ((wp_2_posts.post_status = 'publish') OR
 (wp_2_posts.post_status = 'inherit' AND (p2.post_status = 'publish')))
 GROUP BY wp_2_posts.ID ORDER BY wp_2_posts.post_date DESC
 }}}

--
Ticket URL: <http://core.trac.wordpress.org/ticket/25775#comment:16>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software