我确定答案已经在某处,但我找不到我特别需要的东西.
I'm sure the answer is already in here somewhere, but I can't find what I need in particular.
很简单.我有一个通过 Wordpress(wp_posts 和 wp_postmeta)的一对多表.postmeta 表有任意数量的引用 post 表的行.
It's very simple. I have a one-to-many table via Wordpress (wp_posts and wp_postmeta). The postmeta table has an arbitrary number of rows referencing the posts table.
我需要比较不同 postmeta 行的两个值.
I need to compare two values of different postmeta rows.
示例:
SELECT * FROM wp_posts JOIN wp_postmeta ON wp_postmeta.post_id = wp_posts.ID WHERE ( wp_postmeta.meta_key = 'company' AND wp_postmeta.meta_value LIKE '%Company 01%' ) AND ( wp_postmeta.meta_key = 'description' AND wp_postmeta.meta_value LIKE '%Potato%' )在上面的示例中,我需要根据同一行中的值检查元键company"和description".但同时,我需要检查另一个元键/值对.
In the example above, I need to check the meta key "company" and "description" against the value within the same row. But at the same time, I need to check another meta key/value pair.
比较这些元键的正确方法是什么?我需要为每个项目比较大约 10 个单独的元键(将来会更多),所以我预计使用太多子查询会使其变慢.
What is the correct way to compare these meta keys? I will need to compare about 10 seperate meta keys for each item (more in the future) so I would expect using too many subqueries would make it slow.
(仅供参考,这是在 WordPress 之外使用的,无需通知我有关 get_post_meta() 函数的信息)
(FYI this is for use outside of WordPress, no need to inform me about the get_post_meta() function)
推荐答案虽然在我做进一步调查之前我不会相信性能,但我在原始问题的评论中放置的链接(此处) 似乎有效.Strawberry 已经解释说这种方法效果很好",所以我会继续这样做.
Although I won't be convinced of the performance until I do some further investigation, the link I put in the comments of the original question (here) seems to work. Strawberry has explained that this method "performs quite well" and so I'll go with that.
我想我会发布这个问题的答案,因为我现在可以使用它.如果有人对此答案有改进(如果有),请随意单独发布,我肯定会接受最好/最有效的查询.
I guess I will post an answer to this question as I have it working now. If anyone has an improvement to this answer (if there is one) feel free to post it separately, I will definitely accept the best/most efficient query.
最后,这是我现在使用的查询的简化版本,其中删除了大部分不必要的垃圾.这里的主要目标是对每个元字段进行连接并为其分配一个名称,以便该字段包含 WHERE 语句的唯一行:
Finally, here is a simplified version of the query I am now using, with most the unecessary junk pulled out of it. The main objective here is to do a join for each meta field and assign it a name, so that field contains a unique row for the WHERE statement:
set @search_term_1 = '%This string is only found in post meta skills%'; select * from wp_posts post join wp_postmeta meta_comp ON ( meta_comp.post_id = post.ID AND meta_comp.meta_key = 'post_company' ) join wp_postmeta meta_reas ON ( meta_reas.post_id = post.ID AND meta_reas.meta_key = 'post_reason' ) join wp_postmeta meta_skill ON ( meta_skill.post_id = post.ID AND meta_skill.meta_key = 'post_skills' ) join wp_postmeta meta_method ON ( meta_method.post_id = post.ID AND meta_method.meta_key = 'post_method' ) where -- Check post title and description post.post_title LIKE @search_term_1 or post.post_content LIKE @search_term_1 or meta_comp.meta_value LIKE @search_term_1 or meta_reas.meta_value like @search_term_1 or meta_skill.meta_value like @search_term_1 or meta_method.meta_value like @search_term_1 group by post.ID limit 10更多推荐
比较mysql中相同连接行的两列
发布评论