informatique:wordpress:wp_mysql_inner_join
Différences
Ci-dessous, les différences entre deux révisions de la page.
Les deux révisions précédentesRévision précédenteProchaine révision | Révision précédenteDernière révisionLes deux révisions suivantes | ||
informatique:wordpress:wp_mysql_inner_join [29/09/2020 16:58] – cyrille | informatique:wordpress:wp_mysql_inner_join [29/09/2020 17:52] – cyrille | ||
---|---|---|---|
Ligne 1: | Ligne 1: | ||
+ | ====== Wordpress INNER JOIN plante MySql ====== | ||
- | La base de données: {{ : | + | Wordpress et certains INNER JOIN plante MySql. |
+ | |||
+ | Quand je chercher events après une date ça fonctionne, on a seulement des '' | ||
+ | |||
+ | Par contre pour chercher events entre 2 dates c'est là que MySql part dans les choux, on a dans ce cas des '' | ||
+ | |||
+ | ===== La BdD ===== | ||
+ | |||
+ | La base de données: {{ : | ||
+ | |||
+ | ===== Le SQL ===== | ||
+ | |||
+ | Le SQL généré par WP: | ||
+ | <code sql> | ||
+ | SELECT SQL_CALC_FOUND_ROWS | ||
+ | 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 = ' | ||
+ | AND | ||
+ | ( | ||
+ | ( | ||
+ | ( mt1.meta_key = ' | ||
+ | AND | ||
+ | ( mt2.meta_key = ' | ||
+ | ) | ||
+ | OR | ||
+ | ( | ||
+ | ( mt3.meta_key = ' | ||
+ | AND | ||
+ | ( mt4.meta_key = ' | ||
+ | ) | ||
+ | OR | ||
+ | ( | ||
+ | ( mt5.meta_key = ' | ||
+ | AND | ||
+ | ( mt6.meta_key = ' | ||
+ | ) | ||
+ | OR | ||
+ | ( | ||
+ | ( mt7.meta_key = ' | ||
+ | AND | ||
+ | ( mt8.meta_key = ' | ||
+ | ) | ||
+ | OR | ||
+ | ( | ||
+ | ( mt9.meta_key = ' | ||
+ | AND | ||
+ | ( mt10.meta_key = ' | ||
+ | ) | ||
+ | OR | ||
+ | ( | ||
+ | ( mt11.meta_key = ' | ||
+ | AND | ||
+ | ( mt12.meta_key = ' | ||
+ | ) | ||
+ | OR | ||
+ | ( | ||
+ | ( mt13.meta_key = ' | ||
+ | AND | ||
+ | ( mt14.meta_key = ' | ||
+ | ) | ||
+ | OR | ||
+ | ( | ||
+ | ( mt15.meta_key = ' | ||
+ | AND | ||
+ | ( mt16.meta_key = ' | ||
+ | ) | ||
+ | OR | ||
+ | ( | ||
+ | ( mt17.meta_key = ' | ||
+ | AND | ||
+ | ( mt18.meta_key = ' | ||
+ | ) | ||
+ | OR | ||
+ | ( | ||
+ | ( mt19.meta_key = ' | ||
+ | AND | ||
+ | ( mt20.meta_key = ' | ||
+ | ) | ||
+ | ) | ||
+ | ) | ||
+ | AND wp2r_posts.post_type = ' | ||
+ | AND ((wp2r_posts.post_status = ' | ||
+ | GROUP BY wp2r_posts.ID | ||
+ | ORDER BY wp2r_posts.post_date | ||
+ | ASC LIMIT 0, 10 | ||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | ===== Le tableau WP_Query ===== | ||
Le tableau `args` pour WP_Query: | Le tableau `args` pour WP_Query: | ||
Ligne 187: | Ligne 300: | ||
</ | </ | ||
- | Le SQL généré par WP: | + | ===== Le bug dans le core WP ===== |
- | <code sql> | + | |
- | SELECT SQL_CALC_FOUND_ROWS | + | '' |
- | FROM wp2r_posts | + | |
- | INNER JOIN wp2r_postmeta ON ( wp2r_posts.ID | + | <code php> |
- | INNER JOIN wp2r_postmeta AS mt1 ON ( wp2r_posts.ID | + | // JOIN clauses for NOT EXISTS have their own syntax. |
- | INNER JOIN wp2r_postmeta AS mt2 ON ( wp2r_posts.ID | + | if ( 'NOT EXISTS' === $meta_compare |
- | INNER JOIN wp2r_postmeta AS mt3 ON ( wp2r_posts.ID | + | $join .= " LEFT JOIN $this->meta_table"; |
- | INNER JOIN wp2r_postmeta AS mt4 ON ( wp2r_posts.ID | + | $join .= $i ? " AS $alias" |
- | INNER JOIN wp2r_postmeta AS mt5 ON ( wp2r_posts.ID | + | |
- | INNER JOIN wp2r_postmeta AS mt6 ON ( wp2r_posts.ID | + | |
- | INNER JOIN wp2r_postmeta AS mt7 ON ( wp2r_posts.ID | + | |
- | INNER JOIN wp2r_postmeta AS mt8 ON ( wp2r_posts.ID | + | |
- | INNER JOIN wp2r_postmeta AS mt9 ON ( wp2r_posts.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 | + | |
- | ( | + | |
- | ( | + | |
- | ( mt1.meta_key = ' | + | |
- | AND | + | |
- | ( mt2.meta_key = ' | + | |
- | ) | + | |
- | OR | + | |
- | ( | + | |
- | ( mt3.meta_key = 'dates_1_date_start' | + | |
- | | + | |
- | ( mt4.meta_key = ' | + | |
- | ) | + | |
- | OR | + | |
- | ( | + | |
- | ( mt5.meta_key = ' | + | |
- | AND | + | |
- | ( mt6.meta_key = ' | + | |
- | ) | + | |
- | OR | + | |
- | ( | + | |
- | ( mt7.meta_key = ' | + | |
- | AND | + | |
- | ( mt8.meta_key = 'dates_3_date_end' | + | |
- | ) | + | |
- | OR | + | |
- | ( | + | |
- | ( mt9.meta_key | + | |
- | AND | + | |
- | ( mt10.meta_key | + | |
- | ) | + | |
- | OR | + | |
- | ( | + | |
- | ( mt11.meta_key = ' | + | |
- | AND | + | |
- | ( mt12.meta_key = ' | + | |
- | ) | + | |
- | OR | + | |
- | ( | + | |
- | ( mt13.meta_key | + | |
- | AND | + | |
- | ( mt14.meta_key = 'dates_6_date_end' | + | |
- | ) | + | |
- | OR | + | |
- | ( | + | |
- | ( mt15.meta_key = ' | + | |
- | AND | + | |
- | ( mt16.meta_key = ' | + | |
- | ) | + | |
- | OR | + | |
- | ( | + | |
- | ( mt17.meta_key = ' | + | |
- | AND | + | |
- | ( mt18.meta_key = ' | + | |
- | ) | + | |
- | OR | + | |
- | ( | + | |
- | ( mt19.meta_key = ' | + | |
- | AND | + | |
- | ( mt20.meta_key = ' | + | |
- | ) | + | |
- | ) | + | |
- | ) | + | |
- | AND wp2r_posts.post_type = ' | + | |
- | AND ((wp2r_posts.post_status = ' | + | |
- | GROUP BY wp2r_posts.ID | + | |
- | ORDER BY wp2r_posts.post_date | + | |
- | ASC LIMIT 0, 10 | + | |
+ | if ( ' | ||
+ | $join .= $wpdb-> | ||
+ | } else { | ||
+ | $join .= $wpdb-> | ||
+ | } | ||
+ | // All other JOIN clauses. | ||
+ | } else { | ||
+ | $join .= " INNER JOIN $this-> | ||
+ | $join .= $i ? " AS $alias" | ||
+ | $join .= " ON ( $this-> | ||
+ | // Ce qu'il faudrait à la place : | ||
+ | //$join .= $wpdb-> | ||
+ | } | ||
</ | </ | ||
informatique/wordpress/wp_mysql_inner_join.txt · Dernière modification : 07/09/2021 20:52 de cyrille