Sql with multiple post_meta statements

  • Resolved MvdP

    (@mvdp)


    12 years, 11 months ago

    Hi all,

    I need to query my WordPress db based on the posts metadata. No special tricks there. Unfortunately though I need to match for 2 specific statements. Here is my, not working, query so far:

    SELECT
	*
FROM
	wp_postmeta
WHERE
	(
			(meta_key = 'lat' AND meta_value >= '60.23457047672217')
		OR
			(meta_key = 'lat' AND meta_value <= '60.23457047672217')
	)
	AND
	(
			(meta_key = 'long' AND meta_value >= '24.879140853881836')
		OR
			(meta_key = 'long' AND meta_value <= '24.879140853881836')
	)
GROUP BY
	post_id

    If I remove the “AND” part everything goes well and I get results back only based on the latitude or longitude values. I need to match on both values however.

    How can I accomplish this?

    Thanks in advance.

Viewing 7 replies - 1 through 7 (of 7 total)
  • Moderator keesiemeijer

    (@keesiemeijer)

    Have you looked at meta_query?

    https://codex.www.ads-software.com/Function_Reference/WP_Query#Custom_Field_Parameters

    Why do you need the “OR” statement when both values are the same?

    Thread Starter MvdP

    (@mvdp)

    Yes I have looked at the meta_query.

    My example query was a bit wrong it should be something like this i think:

    `SELECT
    wp_posts.*
    INNER JOIN
    wp_postmeta
    FROM
    wp_postmeta
    WHERE
    (
    (meta_key = ‘lat’ AND meta_value >= ’55’)
    OR
    (meta_key = ‘lat’ AND meta_value <= ’65’)
    )
    AND
    (
    (meta_key = ‘long’ AND meta_value >= ’20’)
    OR
    (meta_key = ‘long’ AND meta_value <= ’30’)
    )
    GROUP BY
    post_id’

    Moderator keesiemeijer

    (@keesiemeijer)

    see if this meta_query works [untested]:

    <?php
$args = array(

 	'meta_query' => array(
		'relation' => 'AND',
 		array(
 			'key' => 'lat',
 			'value' => array( 55, 65 ),
 			'type' => 'numeric',
 			'compare' => 'BETWEEN'
 		),
 		array(
 			'key' => 'long',
 			'value' => array( 20, 30 ),
 			'type' => 'numeric',
 			'compare' => 'BETWEEN'
 		)
 	)
  );
 $query = new WP_Query( $args );

 ?>

    Thread Starter MvdP

    (@mvdp)

    @keesiemeijer you are my hero!

    Sometimes the answer can be so simple… yet so hard. Totally over thought this whole thing. Sometimes it’s the simpleness that does the job!

    Thanks a lot!!

    Moderator keesiemeijer

    (@keesiemeijer)

    No problem. Glad you got it resolved.

    Thread Starter MvdP

    (@mvdp)

    If one is interested in the produced final query it looks like this:

    SELECT
	SQL_CALC_FOUND_ROWS wp_posts.*
FROM
	wp_posts
INNER JOIN
	wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
INNER JOIN
	wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)
WHERE
	1=1
AND
	wp_posts.post_type = 'post'
AND
	(wp_posts.post_status = 'publish')
AND
	( (wp_postmeta.meta_key = 'lat' AND CAST(wp_postmeta.meta_value AS SIGNED) BETWEEN '55' AND '65')
AND
	(mt1.meta_key = 'long' AND CAST(mt1.meta_value AS SIGNED) BETWEEN '20' AND '30') )
GROUP BY
	wp_posts.ID
ORDER BY
	wp_posts.post_date
DESC LIMIT 0, 10

    I’m trying to do the same thing, but it always (without fail it appears) returns no posts when the values in the array being compared are negative.

    Any ideas?

Viewing 7 replies - 1 through 7 (of 7 total)
  • The topic ‘Sql with multiple post_meta statements’ is closed to new replies.