custom query based on custom fields – SQL related

  • I have a bit complex wp query which is based on custom fields. It’s probably more related to SQL it self…

    query:

    $querystr = "SELECT * FROM $wpdb->posts
LEFT JOIN $wpdb->postmeta ON($wpdb->posts.ID = $wpdb->postmeta.post_id)
LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
WHERE $wpdb->term_taxonomy.term_id = $cat->cat_ID
AND $wpdb->term_taxonomy.taxonomy = 'category'
AND $wpdb->posts.post_status = 'publish'
AND $wpdb->posts.post_type = 'post'
AND (
$wpdb->posts.post_end_date IS NULL
OR
$wpdb->posts.post_end_date >= DATE_FORMAT(NOW(), '%Y-%m-%d')
)
AND $wpdb->postmeta.meta_key = 'Type'
AND $wpdb->postmeta.meta_value != 'Offer'
GROUP BY $wpdb->posts.ID
ORDER BY $wpdb->posts.post_date DESC

    I need to fetch entries that are newer then todays date (this works) and based on the one additional custom field “Type”. Field can have values “code” or “offer” or it’s not defined.

    Now… this query works for all entries that have custom field “type” defined, but the problem is it doesnt work on the ones that dont have custom field “type” set.

    So I need to test if the field is null OR is code/offer and fetch them all together. Any help would be much appreciated.

    Thanks!

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

  • To get the ones that don’t have a Type meta key set, you’ll have to un-join the postmeta table and exclude the IDs of the posts that do have that meta value.

    There’s probably a better way overall of doing this; maybe you could describe what you’re trying to do?

    Thread Starter teknoledge

    (@teknoledge)

    I’m using Flutter plugin to add custom fields to posts and I introduced new field called “Type” for all new post entries. Type field is dropdown with 2 possible values “code” and “offer”. For all new entries “Type” is defined with one of the 2 possible values but for older entries it’s undefined and because I have around 1000+ entries it’s impossible to edit each one to add and define new custom field. I set if the “Type” is not defined I take it as “code”.

    What I want to achieve is to have one query that will fetch all entries and have “Type” field values at the end:

    1) if “Type” is not set return meta_value = NULL at the end of the recordset
    2) if “Type” is “Code” return meta_value = Code at the end of the recordset
    3) if “Type” is “Offer” return meta_value = Offer at the end of the recordset

    This way I can identify each entry and act accordingly. Hope this makes sense

    because I have around 1000+ entries it’s impossible to edit each one to add and define new custom field.

    Actually, it would probably be simpler to insert the meta keys and values for your existing entries than to try to code around it for the indefinite future: your database performance will improve and you can use the WordPress posts’ query API.

    Something like the following should insert default postmeta key of “Type” and value of NULL for all post ids that don’t already have one (this will work as long as your version of MySQL isn’t really old):

    INSERT INTO wp_postmeta (post_id, meta_key, meta_value) SELECT ID AS post_id, 'Type' AS meta_key, NULL AS meta_value FROM wp_posts WHERE ID NOT IN (SELECT post_id FROM wp_postmeta WHERE meta_key = 'Type');

    (Of course, do this at your own risk; be sure to back up your database before trying, etc.)

    Then you should be able to get a particular “Type” of post using the WordPress API:

    query_posts(array('meta_key' => 'Type', 'meta_value' => 'Code'));

    Thread Starter teknoledge

    (@teknoledge)

    This seems like it can solve the problem. Thanks! ??

    Just to confim in 2.8.4, this does work to place an empty custom field called “Type” in all posts:

    INSERT INTO wp_postmeta (post_id, meta_key, meta_value) SELECT ID AS post_id, 'Type' AS meta_key, NULL AS meta_value FROM wp_posts WHERE ID NOT IN (SELECT post_id FROM wp_postmeta WHERE meta_key = 'Type');
Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘custom query based on custom fields – SQL related’ is closed to new replies.