Wordpress Search Serialized Meta Data with Custom Query

Question

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

That's what a Core Developer has to say about search and serialized data: the SQL language does not understand serialized data. — brasofilo
I understand that you should structure your meta input around the purpose for it, and if you plan on running queries with the data, it's best to have separate fields assigned to each bit of data. However, there are times when this is not necessarily possible (ie. developing over someone else's plugin). SQL may not understand serialized data, but it understands strings and as long as you know the format of the serial string, you can search for substrings relative to the data you need. Fenway's restructuring of my attempts above helped to put me on the right path. — Aryan Duntley

fenway fenway · Accepted Answer · 2013-02-24T20:55:24

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 Search Serialized Meta Data with Custom Query

1 Answers