Confusion over relationship of wp_post and wp_postmeta tables

  • Resolved kc11

    (@kc11)


    14 years ago

    Hello all,

    I am confused over the relationship of the wp_posts and wp_postmeta tables.

    I would like to select all the posts from wp_posts, which do not have a custom field ( which I have created ) in the wp_postmeta table. I had been assuming that ‘ID’ in wp_posts is the primary key and that ‘post_id’ in wp_postmeta corresponds to it, but I’m no longer sure that this is the case.

    If this is not the case , then how does one link the 2 tables?

    I have been experimenting with the following query:

    $postIDs =  $wpdb->get_col("SELECT ID FROM $wpdb->posts INNER JOIN $wpdb->postmeta ON  ID=post_id WHERE meta_key<>'keyword'");

    this returns the correct post_ids from the wp_postmeta table.

    However I am looking for The IDs from the wp_posts table.

    Any ideas on how to write a query to get the latter?

    Thanks in advance,

    KC

Viewing 4 replies - 1 through 4 (of 4 total)
  • popper

    (@julialasarte)

    You are getting the ids from the post table, just once for every meta value corresponding to that ID in the wp_postmeta table, if you just want to get the id once, you could group them

    SELECT ID from wp_posts inner join wp_postmeta on (id = post_id) HERE meta_key<>'keyword' group by id

    or maybe do something different like

    SELECT ID from wp_posts where not exists ( SELECT * from wp_postmeta where (post_id =ID) and (meta_key='keyword'));

    Thread Starter kc11

    (@kc11)

    Thank you. That worked!

    Regards,

    KC

    Adding this in as a point of reference on previous discussion on grabbing posts “without” given post meta.

    https://www.ads-software.com/support/topic/get-posts-that-doesnt-have-meta-keys

    Thread Starter kc11

    (@kc11)

    Thanks Mark

    KC

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘Confusion over relationship of wp_post and wp_postmeta tables’ is closed to new replies.