How do I perform a between Query on a DB that all ready has multiple joins?

I am trying to write a query to check if the custom meta values in a custom post type(properties) when saved equals the meta values in another custom post type(alerts)

I can get this work for normal match fields however I am trying to get it to work for a range for example, if 'bedrooms' in 'properties' is between 'a_bedrooms_min' and 'a_bedrooms_max' in 'alerts'. My Query looks like this so far:

//Property Meta Fields
$meta_type = 'flat';
$meta_bedrooms = '2';

//Alert Meta Fields
a_bedrooms_min = 1
a_bedrooms_max = 3

SELECT ID
    FROM wp_posts
    INNER JOIN wp_postmeta m1
        ON ( wp_posts.ID = m1.post_id )    
    INNER JOIN wp_postmeta m2
        ON ( wp_posts.ID = m2.post_id )
    INNER JOIN wp_postmeta m3
        ON ( wp_posts.ID = m3.post_id )
WHERE
     wp_posts.post_type = 'alerts'
     AND wp_posts.post_status = 'publish'
     AND ( m1.meta_key = 'a_property_type' AND m1.meta_value = '$meta_type' )
     AND ( m2.meta_key = 'a_bedrooms_min' AND m2.meta_value >= '$meta_bedrooms' )
     AND ( m3.meta_key = 'a_bedrooms_max' AND m3.meta_value <= '$meta_bedrooms' )
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date
DESC;

The last two ANDs obviously will not work with what I am trying to do but how would can get this to work?

Thanks Robert

Solutions

I think you are close. you have a hybrid old ANSI vs more explicit JOIN. As you can see in this example, each one of the 3 criteria is joined to the meta table on all 3 parts explicitly. So, the ID should only appear ONCE per ID as each of the meta SHOULD AT MOST return one record, and the record will only qualify if all 3 meta find the respective match.

SELECT 
      ID
   FROM 
      wp_posts

         INNER JOIN wp_postmeta m1
            ON wp_posts.ID = m1.post_id
           AND m1.meta_key = 'a_property_type'
           AND m1.meta_value = '$meta_type'

         INNER JOIN wp_postmeta m2
           on wp_posts.ID = m2.post_id
          AND m2.meta_key = 'a_bedrooms_min' 
          AND m2.meta_value <= '$meta_bedrooms'

         INNER JOIN wp_postmeta m3
           on wp_posts.ID = m3.post_id
          AND m3.meta_key = 'a_bedrooms_max' 
          AND m3.meta_value >= '$meta_bedrooms'

   WHERE
          wp_posts.post_type = 'alerts'
      AND wp_posts.post_status = 'publish'

   GROUP BY 
      wp_posts.ID

   ORDER BY 
      wp_posts.post_date DESC;

I've revised as I believe the <= and >= were actually backwards. For example, your MINIMUM bedrooms from the meta_value was 1 and max is 3... you are LOOKING for 2 to be qualified.

So, you NEED MIN_BEDROOMS <= 2 and the 2 <= MAX_BEDROOMS what was originally presented is MIN_BEDROOMS >= 2 (1 is never greater than 2) and MAX_BEDROOMS <= 2 (2 is never less than 2).

Try again.

Tags: Mysql / Wordpress / Join

Similar questions

MYSQL: Update Database row Using SELECT and multiple JOINS
Essentially, I'm trying to repair a broken WooCommerce database for a client. I have a table wp_woocommerce_order_itemmeta and all of the _product_id values are erased. I am able to get the correct ID using this select statement. It will return all of the IDs, one per row as wanted. Now when I tried to write an update statement I can't get it to wo...
Query that joins the postmeta table twice
I believe what I'm trying to do is too complex for the standard meta_query parameter in get_posts, so I'm trying to use a custom SQL query to do the following: I have a game custom post that has custom fields for team1 and team2 stored as postmeta. Given team XXXX, I need to pull all posts that have the postmeta value of XXXX for either postmeta ke...
Optimising mysql query - many inner joins
I'm trying to optimise the following query in wordpress, as it takes nearly a minute and a half to return a result. The table relationships are expressed in the following diagram:
How can I improve a slow custom query? (subquery + inner joins with custom fields)
I have a query that is used to return available rooms for a booking system Using Advanced Custom Fields, I have a custom post type representing reservation with various fields The plan here is to find all the booked rooms with the subquery, and then get all the available rooms by selecting the posts who are not in the result set returned by the sub...
Improving a query using a lot of inner joins to wp_postmeta, a key/value table
am working with a wordpress website that is performing the following query, but I see this query is doing many inner joins and the website takes long to load and goes down a lot, and I have been trying to create a query that produces the same result but with no success yet I would like to know what could be a better way to do this Here's the explai...
Only execute jQuery function(on document ready) on the page has shortcode from plugin
What I eventually want to do is when user logged-in to the site, I want to redirect the to the page with all the post created by that user and I want to load them via AJAX request. I have save shortcode that will have all posts from current user. And have php function hooked to AJAX request jQuery function in Document ready Problem I have now is th...

Also ask

We use cookies to deliver the best possible experience on our website. By continuing to use this site, accepting or closing this box, you consent to our use of cookies. To learn more, visit our privacy policy.