WordPressでデータベース検索するとき、meta_queryを使うとよいらしい。
が、同時にめっちゃ遅いという罠があるようだ。
ちょっと調べてみた。
add_action('rest_api_init', function(){
$meta = [
'relation' => 'OR',
[
'key' => '_edit_last',
'value' => [1, 2, 3],
'compare' => 'IN',
'type' => 'integer'
],
[
'key' => 'kurage_memo',
'compare' => 'NOT EXISTS'
],
[
'key' => '_edit_lock',
'value' => 2,
'compare' => '>=',
'type' => 'integer'
],
[
'key' => 'test',
'compare' => 'NOT EXISTS'
],
[
'key' => 'warota',
'compare' => 'LIKE',
'value' => 'hello'
],
[
'relation' => 'OR',
[
'key' => 'xx',
'compare' => '>=',
'value' => 1,
],
[
'key' => 'test',
'compare' => 'NOT EXISTS'
],
[
'key' => 'vv',
'compare' => '!=',
'value' => 4
],
[
'key' => 'qq',
'compare' => '=',
'value' => 100
],
[
'relation' => 'AND',
[
'key' => 'yy',
'compare' => '>=',
'value' => 1
],
[
'key' => 'jj',
'compare' => '<=',
'value' => 4
],
[
'key' => 'cc',
'compare' => '=',
'value' => 100
],
]
]
];
add_filter('get_meta_sql', function($sql){
$where = $sql['where'];
$join = $sql['join'];
$join = str_replace(['INNER JOIN', 'LEFT JOIN'], ["\nINNER JOIN", "\nLEFT JOIN"], $join);
$x = "{$join}\n{$where}";
echo "<pre><code style='font-size:1.2em'>{$x}</code></pre>";
exit;
});
$p = get_posts([
'meta_query' => $meta
]);
});作成されるJOIN句とWHERE句を見てみよう。
LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
LEFT JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id AND mt1.meta_key = 'kurage_memo' )
LEFT JOIN wp_postmeta AS mt2 ON ( wp_posts.ID = mt2.post_id AND mt2.meta_key = 'test' )
LEFT JOIN wp_postmeta AS mt3 ON ( wp_posts.ID = mt3.post_id )
LEFT JOIN wp_postmeta AS mt4 ON ( wp_posts.ID = mt4.post_id AND mt4.meta_key = 'test' )
LEFT JOIN wp_postmeta AS mt5 ON ( wp_posts.ID = mt5.post_id )
LEFT JOIN wp_postmeta AS mt6 ON ( wp_posts.ID = mt6.post_id )
LEFT JOIN wp_postmeta AS mt7 ON ( wp_posts.ID = mt7.post_id )
LEFT JOIN wp_postmeta AS mt8 ON ( wp_posts.ID = mt8.post_id )
AND (
( wp_postmeta.meta_key = '_edit_last' AND wp_postmeta.meta_value IN ('1','2','3') )
OR
mt1.post_id IS NULL
OR
( wp_postmeta.meta_key = '_edit_lock' AND wp_postmeta.meta_value >= '2' )
OR
mt2.post_id IS NULL
OR
( wp_postmeta.meta_key = 'warota' AND wp_postmeta.meta_value LIKE '{60c019f51963a1d07a7f3cb2ba8de9ebc3cdfd2fc7d3dd08336a7520f1b7f861}hello{60c019f51963a1d07a7f3cb2ba8de9ebc3cdfd2fc7d3dd08336a7520f1b7f861}' )
OR
(
( mt3.meta_key = 'xx' AND mt3.meta_value >= '1' )
OR
mt4.post_id IS NULL
OR
( mt5.meta_key = 'vv' AND mt5.meta_value != '4' )
OR
( mt3.meta_key = 'qq' AND mt3.meta_value = '100' )
OR
(
( mt6.meta_key = 'yy' AND mt6.meta_value >= '1' )
AND
( mt7.meta_key = 'jj' AND mt7.meta_value <= '4' )
AND
( mt8.meta_key = 'cc' AND mt8.meta_value = '100' )
)
)
)ぎょぎょぎょっ！
JOINがいっぱい！！！
さらにrelationの値をANDにするとJOINがめっちゃ増える。
ORと違い、ANDの数だけJOINが増える。
LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
LEFT JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id AND mt1.meta_key = 'kurage_memo' )
LEFT JOIN wp_postmeta AS mt2 ON ( wp_posts.ID = mt2.post_id )
LEFT JOIN wp_postmeta AS mt3 ON ( wp_posts.ID = mt3.post_id AND mt3.meta_key = 'test' )
LEFT JOIN wp_postmeta AS mt4 ON ( wp_posts.ID = mt4.post_id )
LEFT JOIN wp_postmeta AS mt5 ON ( wp_posts.ID = mt5.post_id )
LEFT JOIN wp_postmeta AS mt6 ON ( wp_posts.ID = mt6.post_id AND mt6.meta_key = 'test' )
LEFT JOIN wp_postmeta AS mt7 ON ( wp_posts.ID = mt7.post_id )
LEFT JOIN wp_postmeta AS mt8 ON ( wp_posts.ID = mt8.post_id )
LEFT JOIN wp_postmeta AS mt9 ON ( wp_posts.ID = mt9.post_id )
LEFT JOIN wp_postmeta AS mt10 ON ( wp_posts.ID = mt10.post_id )
LEFT JOIN wp_postmeta AS mt11 ON ( wp_posts.ID = mt11.post_id )
AND (
( wp_postmeta.meta_key = '_edit_last' AND wp_postmeta.meta_value IN ('1','2','3') )
AND
mt1.post_id IS NULL
AND
( mt2.meta_key = '_edit_lock' AND mt2.meta_value >= '2' )
AND
mt3.post_id IS NULL
AND
( mt4.meta_key = 'warota' AND mt4.meta_value LIKE '{02589b199a7bf9eb13ec59fb3f1a7f389a58896c709abf3eb40de49bff1439c3}hello{02589b199a7bf9eb13ec59fb3f1a7f389a58896c709abf3eb40de49bff1439c3}' )
AND
(
( mt5.meta_key = 'xx' AND mt5.meta_value >= '1' )
AND
mt6.post_id IS NULL
AND
( mt7.meta_key = 'vv' AND mt7.meta_value != '4' )
AND
( mt8.meta_key = 'qq' AND mt8.meta_value = '100' )
AND
(
( mt9.meta_key = 'yy' AND mt9.meta_value >= '1' )
AND
( mt10.meta_key = 'jj' AND mt10.meta_value <= '4' )
AND
( mt11.meta_key = 'cc' AND mt11.meta_value = '100' )
)
)
)もう一つ。
結合のされ方に特徴がある。
普通にしていると結合は内部結合(INNER JOIN)になるが、NOT EXISTSが含まれていると全部外部結合(LEFT JOIN)になる。
NOT EXISTSはとりあえずメタキーを探して外部結合し、NULLの項目を探すことでこの機能を実現しているようだ。
ちなみにORを使っていても階層が増えるとJOINが増える。
ORで全部同じ階層にすると、
$meta = [
'relation' => 'OR',
[
'key' => '_edit_last',
'value' => [1, 2, 3],
'compare' => 'IN',
'type' => 'integer'
],
[
'key' => '_edit_lock',
'value' => 2,
'compare' => '>=',
'type' => 'integer'
],
[
'key' => 'warota',
'compare' => 'LIKE',
'value' => 'hello'
],
[
'key' => 'xx',
'compare' => '>=',
'value' => 1,
],
[
'key' => 'vv',
'compare' => '!=',
'value' => 4
],
[
'key' => 'qq',
'compare' => '=',
'value' => 100
],
[
'key' => 'yy',
'compare' => '>=',
'value' => 1
],
[
'key' => 'jj',
'compare' => '<=',
'value' => 4
],
[
'key' => 'cc',
'compare' => '=',
'value' => 100
]
];減る！
INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id )
AND (
( wp_postmeta.meta_key = '_edit_last' AND wp_postmeta.meta_value IN ('1','2','3') )
OR
( wp_postmeta.meta_key = '_edit_lock' AND wp_postmeta.meta_value >= '2' )
OR
( wp_postmeta.meta_key = 'warota' AND wp_postmeta.meta_value LIKE '{371c687d1ae76aec66b679054c820e8b4da70d835231f80bbae819eab5f24df5}hello{371c687d1ae76aec66b679054c820e8b4da70d835231f80bbae819eab5f24df5}' )
OR
( wp_postmeta.meta_key = 'xx' AND wp_postmeta.meta_value >= '1' )
OR
( mt1.meta_key = 'vv' AND mt1.meta_value != '4' )
OR
( wp_postmeta.meta_key = 'qq' AND wp_postmeta.meta_value = '100' )
OR
( wp_postmeta.meta_key = 'yy' AND wp_postmeta.meta_value >= '1' )
OR
( wp_postmeta.meta_key = 'jj' AND wp_postmeta.meta_value <= '4' )
OR
( wp_postmeta.meta_key = 'cc' AND wp_postmeta.meta_value = '100' )
)ORかAND、ネスト状態、式によってJOINは増えるようだ。
JOINが増えた時を想像・・・ しにくいのでコードにしてみた。
以下の場合JOINの数だけ結合の行数は倍々形式で増えていく。
mysql> SELECT * FROM items;
+----+-------+
| id | name |
+----+-------+
| 1 | apple |
| 2 | banna |
| 3 | peach |
+----+-------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM meta;
+----+----------+---------+
| id | items_id | itemkey |
+----+----------+---------+
| 1 | 1 | RED |
| 2 | 3 | PINK |
| 3 | 1 | RINGO |
| 4 | 3 | MOMO |
+----+----------+---------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM items
-> LEFT JOIN meta AS m1 ON items.id = m1.items_id
-> LEFT JOIN meta AS m2 ON items.id = m2.items_id
-> LEFT JOIN meta AS m3 ON items.id = m3.items_id
-> ;
+----+-------+------+----------+---------+------+----------+---------+------+----------+---------+
| id | name | id | items_id | itemkey | id | items_id | itemkey | id | items_id | itemkey |
+----+-------+------+----------+---------+------+----------+---------+------+----------+---------+
| 1 | apple | 3 | 1 | RINGO | 3 | 1 | RINGO | 3 | 1 | RINGO |
| 1 | apple | 3 | 1 | RINGO | 3 | 1 | RINGO | 1 | 1 | RED |
| 1 | apple | 3 | 1 | RINGO | 1 | 1 | RED | 3 | 1 | RINGO |
| 1 | apple | 3 | 1 | RINGO | 1 | 1 | RED | 1 | 1 | RED |
| 1 | apple | 1 | 1 | RED | 3 | 1 | RINGO | 3 | 1 | RINGO |
| 1 | apple | 1 | 1 | RED | 3 | 1 | RINGO | 1 | 1 | RED |
| 1 | apple | 1 | 1 | RED | 1 | 1 | RED | 3 | 1 | RINGO |
| 1 | apple | 1 | 1 | RED | 1 | 1 | RED | 1 | 1 | RED |
| 2 | banna | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 3 | peach | 4 | 3 | MOMO | 4 | 3 | MOMO | 4 | 3 | MOMO |
| 3 | peach | 4 | 3 | MOMO | 4 | 3 | MOMO | 2 | 3 | PINK |
| 3 | peach | 4 | 3 | MOMO | 2 | 3 | PINK | 4 | 3 | MOMO |
| 3 | peach | 4 | 3 | MOMO | 2 | 3 | PINK | 2 | 3 | PINK |
| 3 | peach | 2 | 3 | PINK | 4 | 3 | MOMO | 4 | 3 | MOMO |
| 3 | peach | 2 | 3 | PINK | 4 | 3 | MOMO | 2 | 3 | PINK |
| 3 | peach | 2 | 3 | PINK | 2 | 3 | PINK | 4 | 3 | MOMO |
| 3 | peach | 2 | 3 | PINK | 2 | 3 | PINK | 2 | 3 | PINK |
+----+-------+------+----------+---------+------+----------+---------+------+----------+---------+
17 rows in set (0.00 sec)なんでこんな仕様になってるん？
とりあえずget_meta_sqlで直接書いたほうがいいかな。