I'm getting WordPress database error: [Unknown column 'wp_posts.ID' in 'field list'], but column does exist, can someone please tell me what I'm doing wrong?

        SELECT wp_posts.ID,wp_posts.post_date,d.name from wp_posts as a
        WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id
        JOIN wp_term_relationships AS b ON ( a.ID = b.object_id)
        JOIN wp_term_taxonomy AS c ON (b.term_taxonomy_id = c.term_taxonomy_id)
        JOIN wp_terms AS d ON (c.term_id = d.term_id)
        WHERE c.taxonomy = 'my_custom_taxonomy'
        GROUP BY d.name 
        HAVING (wp_posts.post_date = max(wp_posts.post_date))
  • in the table in the database yes, but what about the temporary table created by your nested SQL query? Could wp_posts.ID work? Is there a reason you didn't use WP_Query to do this? WP_Query can generate the SQL itself and draw on the various caches and optimisations, as well as handling those edge cases yours doesn't, e.g. post status, post passwords, post types, etc. Generally you shouldn't be writing SQL for the main posts table, the situations where it makes sense are exceptionally rare
    – Tom J Nowell
    Feb 9, 2022 at 12:13
  • I need to show all taxonomies ordered by last custom post, with pagination. I used the foreach loop ( here's the code: wordpress.stackexchange.com/questions/402326/… ) but it only sorts items if I show all on one page, so I tried with SQL.
    – Darko
    Feb 9, 2022 at 12:27
  • 2
    Shouldn't it be a.ID since you alias wp_posts AS a?
    – kero
    Feb 9, 2022 at 12:28
  • @kero I added WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id below last select and now I'm getting this error 'WHERE c.taxonomy = 'my_custom_taxonomy' GROUP BY d.name HAVING (' at line 10]
    – Darko
    Feb 9, 2022 at 12:41
  • @Darko Please edit the question with updated code. In general this seems more like a general SQL question than a WP one. If you alias tables, you need to use that alias and not the original name. Probably same issue for the WHERE clause.
    – kero
    Feb 9, 2022 at 13:08


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.