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: