带有自定义查询的 Wordpress 搜索序列化元数据 [英] Wordpress Search Serialized Meta Data with Custom Query

问题描述

我正在尝试在 wordpress 数据库中搜索序列化的帖子元值.我知道串行字符串的结构，因此我可以搜索前面的值，获取索引，然后获取我想要在该索引值之前的字符数.我无法在此特定查询中有效地使用正则表达式，因为我想根据结果进行排序.到目前为止，这是我所拥有的，但我在语法上失败了，我认为这与尝试使用 AS 关键字和对 AND 语句进行分组有关.

<前>SELECT SQL_CALC_FOUND_ROWS _posts.ID FROM _postsINNER JOIN _postmeta ON (_posts.ID = _postmeta.post_id)哪里 1=1AND _posts.post_type = '狗'AND (_posts.post_status = '发布')AND ( (_postmeta.meta_key = '_meta_general'AND CAST(_postmeta.meta_value AS CHAR)) AS dmetAND POSITION(';s:6:\"weight' IN dmet) AS ddexAND MID(dmet ,ddex,10)) AS dresGROUP BY dres ORDER BY dres ASC LIMIT 0, 10

嗯，我仍然对这个东西的结构有问题.仔细检查后，之前的代码不起作用，@fenway.这是我现在所拥有的.@fenway 的答案的问题在于，在选择FROM"帖子的语句的选择部分中调用了 MID 和 POSITION 值.它们位于 postmeta 中.所以我试图在 INNER JOIN 之后重新排列字符串过滤，它通过 id 将 postmeta 表连接到 post 表.这是行不通的.我知道这个问题只是由于我缺乏 SQL 知识，但我正在尝试在这里学习.

这些都不是我想要的.存在语法错误.该代码的目的是按 serial(json) 字符串内的值对返回的查询进行分组.该方法是搜索以下值(在这种情况下它将是 - ";s:6:"weight -)当我有这个字符串的索引时，我想返回前面的 10 个值(日期 xx/xx/xxx ).我想标记这个字符串(AS dres)并按dres对结果进行排序.Wordpress 从帖子表中收集帖子，然后从 postmeta 表中收集帖子元数据.后元表是存储 json 的地方.这真的是一个简单的算法，只是语法让我感到困惑.

<前>SELECT SQL_CALC_FOUND_ROWS {$wpdb->posts}.IDFROM {$wpdb->posts} INNER JOIN {$wpdb->postmeta}MID(CAST({$wpdb->postmeta}.meta_value AS CHAR),POSITION(';s:6:\"weight' IN CAST({$wpdb->postmeta}.meta_value AS CHAR) ),10 ) AS dresON ({$wpdb->posts}.ID = {$wpdb->postmeta}.post_id)哪里 1=1AND {$wpdb->posts}.post_type = 'dog'AND ({$wpdb->posts}.post_status = 'publish')AND {$wpdb->postmeta}.meta_key = '_meta_general'AND POSITION(';s:6:"weight' IN CAST({$wpdb->postmeta}.meta_value AS CHAR)) > 0GROUP BY {$wpdb->posts}.ID ORDER BY dres ASC LIMIT 0, 10

解决方案

你不能在你的 WHERE 子句中使用列别名——而且，在某些情况下，那些表达式总是计算为 TRUE，所以我不看看为什么会有.

也许你的意思是:

SELECT SQL_CALC_FOUND_ROWS_posts.ID，中(CAST(_postmeta.meta_value AS CHAR),POSITION(';s:6:\"weight' IN CAST(_postmeta.meta_value AS CHAR)),10) AS 连衣裙来自_postsINNER JOIN _postmeta ON (_posts.ID = _postmeta.post_id)哪里 1=1AND _posts.post_type = '狗' AND _posts.post_status = '发布'AND _postmeta.meta_key = '_meta_general'AND POSITION(';s:6:\"weight' IN CAST(_postmeta.meta_value AS CHAR)) > 0GROUP BY dres ORDER BY _postmeta.meta_value ASC LIMIT 0, 10

I'm trying to do a search on serialized post meta values in a wordpress database. I know the structure of the serial string so I can search for the preceding value, get the index and then get the number of characters I want before that index value. I cannot effectively use regexp in this particular query because I would like to sort based on the results. This is what I have so far, but I am failing on the syntax and I think it has to do with trying to use the AS keyword and my grouping the AND statements.

    SELECT SQL_CALC_FOUND_ROWS _posts.ID FROM _posts 
    INNER JOIN _postmeta ON (_posts.ID = _postmeta.post_id)
    WHERE 1=1
    AND _posts.post_type = 'dog' 
    AND (_posts.post_status = 'publish') 
    AND ( (_postmeta.meta_key = '_meta_general'
       AND CAST(_postmeta.meta_value AS CHAR)) AS dmet 
       AND POSITION(';s:6:\"weight' IN dmet) AS ddex 
       AND MID(dmet ,ddex,10)) AS dres 
    GROUP BY dres ORDER BY dres ASC LIMIT 0, 10

Well, I'm still having issues with the structure of this thing. The previous code did not work, @fenway, after closer inspection. Here is what I have now. The problem with @fenway's answer is that the MID and POSITION values were being called in the select part of the statement that was selecting "FROM" posts. They are located in postmeta. So I attempted to rearrange the string filtering after the INNER JOIN which is joining the postmeta table to the posts table by id. This is not working. I understand that this question is simply due to a lack of my knowledge in SQL, but I'm trying to learn here.

None of these are working as I want. There are syntactical errors. The purpose of the code is to group the returned query by a value that is inside of a serial(json) string. The method is to search for the following value (n this case it would be - ";s:6:"weight -) When I have the index of this string I want to return the preceding 10 values ( a date xx/xx/xxxx ). I want to label this string (AS dres) and have the result sort by dres. Wordpress gathers the posts from the posts table, then gathers the post meta from the postmeta table. The post meta table is where the json is stored. It is really a simple algorithm, it's just the syntax that is screwing with me.

    SELECT SQL_CALC_FOUND_ROWS {$wpdb->posts}.ID 
    FROM {$wpdb->posts} INNER JOIN {$wpdb->postmeta} 
    MID(CAST({$wpdb->postmeta}.meta_value AS CHAR), 
    POSITION(';s:6:\"weight' IN CAST({$wpdb->postmeta}.meta_value AS CHAR) ),10 ) AS dres 
    ON ({$wpdb->posts}.ID = {$wpdb->postmeta}.post_id) 
    WHERE 1=1 
    AND {$wpdb->posts}.post_type = 'dog'
    AND ({$wpdb->posts}.post_status = 'publish') 
    AND {$wpdb->postmeta}.meta_key = '_meta_general' 
    AND POSITION(';s:6:"weight' IN CAST({$wpdb->postmeta}.meta_value AS CHAR)) > 0 
    GROUP BY {$wpdb->posts}.ID ORDER BY dres ASC LIMIT 0, 10

解决方案

You can't use column aliases in your WHERE clause -- what's more, in some cases, those expressions with always evaluate to TRUE, so I don't see why there are there at all.

Perhaps you mean:

SELECT SQL_CALC_FOUND_ROWS 
_posts.ID
,MID(
   CAST(_postmeta.meta_value AS CHAR),
   POSITION(';s:6:\"weight' IN CAST(_postmeta.meta_value AS CHAR) ),
   10
) AS dres  
FROM _posts 
INNER JOIN _postmeta ON (_posts.ID = _postmeta.post_id)
WHERE 1=1
AND _posts.post_type = 'dog' AND _posts.post_status = 'publish'
AND _postmeta.meta_key = '_meta_general'
AND POSITION(';s:6:\"weight' IN CAST(_postmeta.meta_value AS CHAR)) > 0
GROUP BY dres ORDER BY _postmeta.meta_value ASC LIMIT 0, 10

这篇关于带有自定义查询的 Wordpress 搜索序列化元数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！