WordPress Search Metadatos serializados con consulta personalizada

Estoy tratando de hacer una búsqueda en valores meta de publicaciones serializadas en una base de datos de wordpress. Conozco la estructura de la cadena de serie, por lo que puedo buscar el valor anterior, obtener el índice y luego obtener la cantidad de caracteres que quiero antes de ese valor de índice. No puedo usar expresiones regulares de manera efectiva en esta consulta en particular porque me gustaría ordenar según los resultados. Esto es lo que tengo hasta ahora, pero estoy fallando en la sintaxis y creo que tiene que ver con intentar usar la palabra clave AS y agrupar las declaraciones AND.

    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

Bueno, todavía tengo problemas con la estructura de esta cosa. El código anterior no funcionó, @fenway, después de una inspección más detallada. Esto es lo que tengo ahora. El problema con la respuesta de @fenway es que los valores MID y POSITION se llamaron en la parte seleccionada de la declaración que seleccionaba las publicaciones “DE”. Están ubicados en postmeta. Así que intenté reorganizar el filtrado de cadenas después de INNER JOIN que une la tabla postmeta a la tabla de publicaciones por id. Esto no está funcionando. Entiendo que esta pregunta se debe simplemente a mi falta de conocimiento en SQL, pero estoy tratando de aprender aquí.

Ninguno de estos está funcionando como yo quiero. Hay errores sintácticos. El propósito del código es agrupar la consulta devuelta por un valor que está dentro de una cadena serial (json). El método es buscar el siguiente valor (en este caso sería – “;s:6:”weight -) Cuando tenga el índice de esta cadena quiero devolver los 10 valores anteriores (una fecha xx/xx/ xxxx). Quiero etiquetar esta cadena (AS dres) y ordenar el resultado por dres. WordPress recopila las publicaciones de la tabla de publicaciones, luego recopila el meta de la publicación de la tabla postmeta. La metatabla de publicación es donde se almacena el json. Es realmente un algoritmo simple, es solo la sintaxis lo que me está jodiendo.

    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

No puede usar alias de columna en su cláusula WHERE; además, en algunos casos, esas expresiones siempre se evalúan como VERDADERO, por lo que no veo por qué están ahí.

Quizás te refieres a:

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