Cyrille Giquello

Outils pour utilisateurs

Outils du site

Vous êtes ici : accueil » informatique » wordpress » wp_mysql_inner_join
Piste : • resize_image • documentation • drag_drop • airtable • wp_mysql_inner_join • symfony2 • jquery • symfony3 • spring_framework • symfony
informatique:wordpress:wp_mysql_inner_join

Différences

Ci-dessous, les différences entre deux révisions de la page.

Lien vers cette vue comparative

Les deux révisions précédentesRévision précédente
Prochaine révision		Révision précédente
informatique:wordpress:wp_mysql_inner_join [29/09/2020 16:58] – cyrilleinformatique:wordpress:wp_mysql_inner_join [07/09/2021 20:52] (Version actuelle) – [Le bug dans le core WP] cyrille
Ligne 1: Ligne 1:
 +====== Wordpress INNER JOIN plante MySql ======
  
-La base de données: {{ :informatique:wordpress:wp_mysql_inner_join:gicy1456_wp517.200929.sql.tgz |}}+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 BdD ===== 
 + 
 +La base de données: {{ :informatique:wordpress:wp_mysql_inner_join:gicy1456_wp517.200929.sql.tgz |}}. Désolé, le SQL fait 14 Mo à cause de DIVI. 
 + 
 +===== Le SQL ===== 
 + 
 +Le SQL généré par WP: 
 +<code sql> 
 +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  
 + 
 +</code> 
 + 
 + 
 +===== Le tableau WP_Query =====
  
 Le tableau `args` pour WP_Query: Le tableau `args` pour WP_Query:
Ligne 187: Ligne 300:
 </code> </code>
  
-Le SQL généré par WP: +===== Le bug dans le core WP ===== 
-<code sql> + 
-SELECT SQL_CALC_FOUND_ROWS  wp2r_posts.* +''class-wp-meta-query.php'' ligne 557. 
- FROM wp2r_posts + 
- INNER JOIN wp2r_postmeta ON ( wp2r_posts.ID = wp2r_postmeta.post_id ) +<code php> 
- INNER JOIN wp2r_postmeta AS mt1 ON ( wp2r_posts.ID = mt1.post_id ) +// JOIN clauses for NOT EXISTS have their own syntax. 
- INNER JOIN wp2r_postmeta AS mt2 ON ( wp2r_posts.ID = mt2.post_id ) +if ( 'NOT EXISTS' === $meta_compare ) { 
- INNER JOIN wp2r_postmeta AS mt3 ON ( wp2r_posts.ID = mt3.post_id ) + $join .= " LEFT JOIN $this->meta_table"; 
- INNER JOIN wp2r_postmeta AS mt4 ON ( wp2r_posts.ID = mt4.post_id ) + $join .= $i ? " AS $alias" : '';
- 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 +
  
 + 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'] );
 +}
 </code> </code>
  
informatique/wordpress/wp_mysql_inner_join.1601391527.txt.gz · Dernière modification : de cyrille
Sauf mention contraire, le contenu de ce wiki est placé sous les termes de la licence suivante : CC0 1.0 Universal
CC0 1.0 Universal Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki