Custom Query on wp_postmeta table

  • Cezar Ayran

    (@ayrancd)


    2 years, 2 months ago

    I am struggling to get the results I am looking for because a post has multiple rows on wp_postmeta… I tried to use GROUP_CONCAT but can’t get this working properly.

    SELECT DISTINCT wp_posts.ID, wp_posts.post_title,wp_wc_product_meta_lookup.min_price, wp_wc_product_meta_lookup.max_price FROM wp_posts inner join wp_postmeta ON wp_posts.ID = wp_postmeta.post_id inner join wp_wc_product_meta_lookup ON wp_posts.ID = wp_wc_product_meta_lookup.product_id where wp_posts.post_type = 'product' and wp_posts.post_status = 'publish' AND (wp_postmeta.meta_value = 'Blue Ridge' AND wp_postmeta.meta_key = 'venue_title') AND (wp_postmeta.meta_value = 'Race' AND wp_postmeta.meta_key = 'event_type') order by wp_posts.ID

    This is the code I have so far but it doesn’t work… how can I filter and display the meta_value for venue_title and event_type? I am using prepare();

Viewing 2 replies - 1 through 2 (of 2 total)
  • Thread Starter Cezar Ayran

    (@ayrancd)

    2 years, 2 months ago

    Just found a solution that I have to add multiple INNER JOINs… is this the only and good solution?

    inner join wp_postmeta m1 ON wp_posts.ID = m1.post_id AND m1.meta_key = 'event_type' AND m1.meta_value = 'Race'
inner join wp_postmeta m2 ON wp_posts.ID = m2.post_id AND m2.meta_key = 'venue_title' AND m2.meta_value = 'Blue Ridge'
    Robert Anderson

    (@noisysocks)

    2 years, 2 months ago

    Hi @ayrancd.

    Using multiple joins in your query like you discovered is a good solution.

    You could also use subqueries. That is, a nested SELECT within the outer SELECT. This might not be as performant.

    I say if you found a working solution then stick with that!

Viewing 2 replies - 1 through 2 (of 2 total)

The topic ‘Custom Query on wp_postmeta table’ is closed to new replies.