Wordpress et certains INNER JOIN plante MySql.
Quand je chercher events après une date ça fonctionne, on a seulement des OR sur les meta contenant la date de fin.
Par contre pour chercher events entre 2 dates c'est là que MySql part dans les choux, on a dans ce cas des OR contenant un AND sur deux meta.
La base de données: gicy1456_wp517.200929.sql.tgz. Désolé, le SQL fait 14 Mo à cause de DIVI.
Le SQL généré par WP:
SELECT SQL_CALC_FOUND_ROWS wp2r_posts.* FROM wp2r_posts INNER JOIN wp2r_postmeta ON ( wp2r_posts.ID = wp2r_postmeta.post_id ) INNER JOIN wp2r_postmeta AS mt1 ON ( wp2r_posts.ID = mt1.post_id ) INNER JOIN wp2r_postmeta AS mt2 ON ( wp2r_posts.ID = mt2.post_id ) INNER JOIN wp2r_postmeta AS mt3 ON ( wp2r_posts.ID = mt3.post_id ) INNER JOIN wp2r_postmeta AS mt4 ON ( wp2r_posts.ID = mt4.post_id ) INNER JOIN wp2r_postmeta AS mt5 ON ( wp2r_posts.ID = mt5.post_id ) INNER JOIN wp2r_postmeta AS mt6 ON ( wp2r_posts.ID = mt6.post_id ) INNER JOIN wp2r_postmeta AS mt7 ON ( wp2r_posts.ID = mt7.post_id ) INNER JOIN wp2r_postmeta AS mt8 ON ( wp2r_posts.ID = mt8.post_id ) INNER JOIN wp2r_postmeta AS mt9 ON ( wp2r_posts.ID = mt9.post_id ) INNER JOIN wp2r_postmeta AS mt10 ON ( wp2r_posts.ID = mt10.post_id ) INNER JOIN wp2r_postmeta AS mt11 ON ( wp2r_posts.ID = mt11.post_id ) INNER JOIN wp2r_postmeta AS mt12 ON ( wp2r_posts.ID = mt12.post_id ) INNER JOIN wp2r_postmeta AS mt13 ON ( wp2r_posts.ID = mt13.post_id ) INNER JOIN wp2r_postmeta AS mt14 ON ( wp2r_posts.ID = mt14.post_id ) INNER JOIN wp2r_postmeta AS mt15 ON ( wp2r_posts.ID = mt15.post_id ) INNER JOIN wp2r_postmeta AS mt16 ON ( wp2r_posts.ID = mt16.post_id ) INNER JOIN wp2r_postmeta AS mt17 ON ( wp2r_posts.ID = mt17.post_id ) INNER JOIN wp2r_postmeta AS mt18 ON ( wp2r_posts.ID = mt18.post_id ) INNER JOIN wp2r_postmeta AS mt19 ON ( wp2r_posts.ID = mt19.post_id ) INNER JOIN wp2r_postmeta AS mt20 ON ( wp2r_posts.ID = mt20.post_id ) WHERE 1=1 AND ( ( wp2r_postmeta.meta_key = 'geo_city' AND wp2r_postmeta.meta_value LIKE 'Tours' ) AND ( ( ( mt1.meta_key = 'dates_0_date_start' AND mt1.meta_value <= '2020-10-04 00:00:00' ) AND ( mt2.meta_key = 'dates_0_date_end' AND mt2.meta_value >= '2020-10-03 00:00:00' ) ) OR ( ( mt3.meta_key = 'dates_1_date_start' AND mt3.meta_value <= '2020-10-04 00:00:00' ) AND ( mt4.meta_key = 'dates_1_date_end' AND mt4.meta_value >= '2020-10-03 00:00:00' ) ) OR ( ( mt5.meta_key = 'dates_2_date_start' AND mt5.meta_value <= '2020-10-04 00:00:00' ) AND ( mt6.meta_key = 'dates_2_date_end' AND mt6.meta_value >= '2020-10-03 00:00:00' ) ) OR ( ( mt7.meta_key = 'dates_3_date_start' AND mt7.meta_value <= '2020-10-04 00:00:00' ) AND ( mt8.meta_key = 'dates_3_date_end' AND mt8.meta_value >= '2020-10-03 00:00:00' ) ) OR ( ( mt9.meta_key = 'dates_4_date_start' AND mt9.meta_value <= '2020-10-04 00:00:00' ) AND ( mt10.meta_key = 'dates_4_date_end' AND mt10.meta_value >= '2020-10-03 00:00:00' ) ) OR ( ( mt11.meta_key = 'dates_5_date_start' AND mt11.meta_value <= '2020-10-04 00:00:00' ) AND ( mt12.meta_key = 'dates_5_date_end' AND mt12.meta_value >= '2020-10-03 00:00:00' ) ) OR ( ( mt13.meta_key = 'dates_6_date_start' AND mt13.meta_value <= '2020-10-04 00:00:00' ) AND ( mt14.meta_key = 'dates_6_date_end' AND mt14.meta_value >= '2020-10-03 00:00:00' ) ) OR ( ( mt15.meta_key = 'dates_7_date_start' AND mt15.meta_value <= '2020-10-04 00:00:00' ) AND ( mt16.meta_key = 'dates_7_date_end' AND mt16.meta_value >= '2020-10-03 00:00:00' ) ) OR ( ( mt17.meta_key = 'dates_8_date_start' AND mt17.meta_value <= '2020-10-04 00:00:00' ) AND ( mt18.meta_key = 'dates_8_date_end' AND mt18.meta_value >= '2020-10-03 00:00:00' ) ) OR ( ( mt19.meta_key = 'dates_9_date_start' AND mt19.meta_value <= '2020-10-04 00:00:00' ) AND ( mt20.meta_key = 'dates_9_date_end' AND mt20.meta_value >= '2020-10-03 00:00:00' ) ) ) ) AND wp2r_posts.post_type = 'caf37_event' AND ((wp2r_posts.post_status = 'publish')) GROUP BY wp2r_posts.ID ORDER BY wp2r_posts.post_date ASC LIMIT 0, 10
Le tableau `args` pour WP_Query:
array ( 'post_type' => 'caf37_event', 'post_status' => 'publish', 'posts_per_page' => 10, 'orderby' => 'date', 'order' => 'ASC', 'meta_query' => array ( 'relation' => 'AND', 0 => array ( 'key' => 'geo_city', 'value' => 'Tours', 'compare' => 'LIKE', ), 1 => array ( 'relation' => 'OR', 0 => array ( 'relation' => 'AND', 0 => array ( 'key' => 'dates_0_date_start', 'value' => '2020-10-04 00:00:00', 'compare' => '<=', ), 1 => array ( 'key' => 'dates_0_date_end', 'value' => '2020-10-03 00:00:00', 'compare' => '>=', ), ), 1 => array ( 'relation' => 'AND', 0 => array ( 'key' => 'dates_1_date_start', 'value' => '2020-10-04 00:00:00', 'compare' => '<=', ), 1 => array ( 'key' => 'dates_1_date_end', 'value' => '2020-10-03 00:00:00', 'compare' => '>=', ), ), 2 => array ( 'relation' => 'AND', 0 => array ( 'key' => 'dates_2_date_start', 'value' => '2020-10-04 00:00:00', 'compare' => '<=', ), 1 => array ( 'key' => 'dates_2_date_end', 'value' => '2020-10-03 00:00:00', 'compare' => '>=', ), ), 3 => array ( 'relation' => 'AND', 0 => array ( 'key' => 'dates_3_date_start', 'value' => '2020-10-04 00:00:00', 'compare' => '<=', ), 1 => array ( 'key' => 'dates_3_date_end', 'value' => '2020-10-03 00:00:00', 'compare' => '>=', ), ), 4 => array ( 'relation' => 'AND', 0 => array ( 'key' => 'dates_4_date_start', 'value' => '2020-10-04 00:00:00', 'compare' => '<=', ), 1 => array ( 'key' => 'dates_4_date_end', 'value' => '2020-10-03 00:00:00', 'compare' => '>=', ), ), 5 => array ( 'relation' => 'AND', 0 => array ( 'key' => 'dates_5_date_start', 'value' => '2020-10-04 00:00:00', 'compare' => '<=', ), 1 => array ( 'key' => 'dates_5_date_end', 'value' => '2020-10-03 00:00:00', 'compare' => '>=', ), ), 6 => array ( 'relation' => 'AND', 0 => array ( 'key' => 'dates_6_date_start', 'value' => '2020-10-04 00:00:00', 'compare' => '<=', ), 1 => array ( 'key' => 'dates_6_date_end', 'value' => '2020-10-03 00:00:00', 'compare' => '>=', ), ), 7 => array ( 'relation' => 'AND', 0 => array ( 'key' => 'dates_7_date_start', 'value' => '2020-10-04 00:00:00', 'compare' => '<=', ), 1 => array ( 'key' => 'dates_7_date_end', 'value' => '2020-10-03 00:00:00', 'compare' => '>=', ), ), 8 => array ( 'relation' => 'AND', 0 => array ( 'key' => 'dates_8_date_start', 'value' => '2020-10-04 00:00:00', 'compare' => '<=', ), 1 => array ( 'key' => 'dates_8_date_end', 'value' => '2020-10-03 00:00:00', 'compare' => '>=', ), ), 9 => array ( 'relation' => 'AND', 0 => array ( 'key' => 'dates_9_date_start', 'value' => '2020-10-04 00:00:00', 'compare' => '<=', ), 1 => array ( 'key' => 'dates_9_date_end', 'value' => '2020-10-03 00:00:00', 'compare' => '>=', ), ), ), ), )
class-wp-meta-query.php ligne 557.
// JOIN clauses for NOT EXISTS have their own syntax. if ( 'NOT EXISTS' === $meta_compare ) { $join .= " LEFT JOIN $this->meta_table"; $join .= $i ? " AS $alias" : ''; if ( 'LIKE' === $meta_compare_key ) { $join .= $wpdb->prepare( " ON ($this->primary_table.$this->primary_id_column = $alias.$this->meta_id_column AND $alias.meta_key LIKE %s )", '%' . $wpdb->esc_like( $clause['key'] ) . '%' ); } else { $join .= $wpdb->prepare( " ON ($this->primary_table.$this->primary_id_column = $alias.$this->meta_id_column AND $alias.meta_key = %s )", $clause['key'] ); } // All other JOIN clauses. } else { $join .= " INNER JOIN $this->meta_table"; $join .= $i ? " AS $alias" : ''; $join .= " ON ( $this->primary_table.$this->primary_id_column = $alias.$this->meta_id_column )"; // Ce qu'il faudrait à la place : //$join .= $wpdb->prepare( // " ON ($this->primary_table.$this->primary_id_column = $alias.$this->meta_id_column // AND $alias.meta_key = %s )", $clause['key'] ); }