custom user meta query

时间：2013-06-22 作者：b747fp

我一直在尝试使此查询工作，但它始终无法返回任何行

SELECT a.ID 
  FROM wp_users a, wp_usermeta b 
WHERE b.user_id=a.ID 
AND (
  (b.meta_key=\'firstname-groom\' AND b.meta_value LIKE \'Darren\') 
  OR (b.meta_key=\'firstname-bride\' AND b.meta_value LIKE \'Someone\') 
  OR (b.meta_key=\'lastname-bride\' AND b.meta_value LIKE \'Cool\')
) 
AND (b.meta_key=\'weddingdate\' AND b.meta_value=\'12/14/2014\') 
GROUP BY a.ID

如果删除此部分，查询将返回行AND (b.meta_key=\'weddingdate\' AND b.meta_value=\'12/14/2014\')但我需要能够满足这一要求（weddingdate=2014年12月14日）

3 个回复

SO网友:b747fp

事实上我现在用join解决了

SELECT a.ID FROM wp_users a INNER JOIN wp_usermeta b ON b.user_id = a.ID AND 
(
( b.meta_key = \'firstname-groom\' AND b.meta_value LIKE \'Darren\' )
OR ( b.meta_key = \'firstname-bride\' AND b.meta_value LIKE \'Someone\' )
OR ( b.meta_key = \'lastname-bride\' AND b.meta_value LIKE \'Cool\' )
)
INNER JOIN wp_usermeta c ON c.user_id = a.ID AND 
( c.meta_key = \'weddingdate\' AND c.meta_value = \'12/14/2014\' )
GROUP BY a.ID

SO网友:s_ha_dum

这个SQL 不会触发错误，因此数据库中没有与所有条件匹配的数据。如前所述，您需要一个或多个：

(b.meta_key=\'firstname-groom\' AND b.meta_value LIKE \'Darren\') 
OR (b.meta_key=\'firstname-bride\' AND b.meta_value LIKE \'Someone\') 
OR (b.meta_key=\'lastname-bride\' AND b.meta_value LIKE \'Cool\')

还有这个：

AND (b.meta_key=\'weddingdate\' AND b.meta_value=\'12/14/2014\')

以匹配此项以返回结果。然而，如果你仔细观察，这种情况永远不会发生，因为这需要b.meta_key 兼而有之weddingdate 其中一个firstname-groom, firstname-bride, lastname-bride 同时，这永远不会发生。你需要JOIN 表格多次显示，一次显示名称，一次显示日期。（看起来你已经在评论中解决了这个问题，所以我不会写SQL.)

不过，我想再发表一点评论。

保存日期，如12/14/2014 这是个坏主意。There is very little rational sorting you can do with that date format 而且提取月、日或年数据不会特别有效或可靠，因为MySQL\'s日期函数将起作用user query 但是看看你的SQL 我认为这门课的逻辑太复杂了。

SO网友:Vitaliy Kukin

SELECT m1.`user_id` as `ID`, m1.p1, m2.p2, m3.p3, m4.p4 
        FROM 
            (SELECT `user_id`, `meta_value` as p1 FROM `wp_usermeta`
                WHERE (`meta_key` = \'firstname-groom\' AND `meta_value` LIKE \'Darren\') 
            GROUP BY `user_id`) m1,
            (SELECT `user_id`, `meta_value` as p2 FROM `wp_usermeta`
                WHERE (`meta_key` = \'firstname-bride\' AND `meta_value` LIKE \'Someone\') 
            GROUP BY `user_id`) m2,
            (SELECT `user_id`, `meta_value` as p3 FROM `wp_usermeta`
                WHERE (`meta_key` = \'lastname-bride\' AND `meta_value` LIKE \'Cool\') 
            GROUP BY `user_id`) m3,
            (SELECT `user_id`, `meta_value` as p4 FROM `wp_usermeta`
WHERE (`meta_key` = \'weddingdate\' AND `meta_value` = \'12/14/2014\' )
            GROUP BY `user_id`) m4
        WHERE
            m1.`user_id` = m2.`user_id` AND m1.`user_id` = m3.`user_id` AND m1.`user_id` = m4.`user_id`

如有必要，可以使用where-ready函数生成此类查询。函数返回一个用户ID数组

结束

小码农CODE

custom user meta query

相关推荐

WordPress中预定义了哪些jQueryUI句柄？