mysql - Get posts from category with custom query

admin管理员组

文章数量:1333184

I've got a custom sql query to get latest posts from specific category (ID = 62). It looks like this:

SELECT p.ID,
       u.display_name AS author,
       post_date_gmt,
       post_title,
       p.post_author AS author_id,
       rel.term_taxonomy_id,

  (SELECT guid
   FROM wp_BOMEGAposts
   WHERE id = m.meta_value) AS image,

  (SELECT term_taxonomy_id
   FROM wp_BOMEGAterm_relationships
   WHERE object_id = p.ID) AS categories
FROM wp_BOMEGAposts p
LEFT JOIN wp_BOMEGAusers u ON p.post_author = u.ID
LEFT JOIN wp_BOMEGApostmeta m ON p.ID = m.post_id
INNER JOIN wp_BOMEGAterm_relationships rel ON p.ID = rel.object_id
WHERE p.post_type = 'post'
  AND p.post_status = 'publish'
  AND m.meta_key = '_thumbnail_id'
  AND rel.term_taxonomy_id IN (62)
ORDER BY p.post_date DESC
LIMIT 1
OFFSET 0

This posts have multiple categories and I want to get them. So I wrote a sub-query to revice them. But I'm getting an error:

#1242 - Subquery returns more than 1 row

How can I fix this?

I've got a custom sql query to get latest posts from specific category (ID = 62). It looks like this:

SELECT p.ID,
       u.display_name AS author,
       post_date_gmt,
       post_title,
       p.post_author AS author_id,
       rel.term_taxonomy_id,

  (SELECT guid
   FROM wp_BOMEGAposts
   WHERE id = m.meta_value) AS image,

  (SELECT term_taxonomy_id
   FROM wp_BOMEGAterm_relationships
   WHERE object_id = p.ID) AS categories
FROM wp_BOMEGAposts p
LEFT JOIN wp_BOMEGAusers u ON p.post_author = u.ID
LEFT JOIN wp_BOMEGApostmeta m ON p.ID = m.post_id
INNER JOIN wp_BOMEGAterm_relationships rel ON p.ID = rel.object_id
WHERE p.post_type = 'post'
  AND p.post_status = 'publish'
  AND m.meta_key = '_thumbnail_id'
  AND rel.term_taxonomy_id IN (62)
ORDER BY p.post_date DESC
LIMIT 1
OFFSET 0

This posts have multiple categories and I want to get them. So I wrote a sub-query to revice them. But I'm getting an error:

#1242 - Subquery returns more than 1 row

How can I fix this?

• query
• mysql
• wpdb

Share Improve this question asked Jun 15, 2020 at 14:17 Daniel KoczułaDaniel Koczuła 10322 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

It looks like this is the new part of this query causing the problem, is that right?

(SELECT term_taxonomy_id
FROM wp_BOMEGAterm_relationships
WHERE object_id = p.ID) AS categories

If so the error message is clear: a sub-select in this query should only return one row. If it returns more than one row it doesn't know how to mash all those many rows into the one row of the rest of the query.

So you need to find a way to join all the rows of this subquery together into one row, and the SQL command GROUP_CONCAT will do that

You want to make the subquery something like:

(SELECT GROUP_CONCAT(term_taxonomy_id)
FROM wp_BOMEGAterm_relationships
WHERE object_id = p.ID) AS categories

If there were more than one term_taxonomy_id's to be returned, they'll then be returned in a single row comma delimited, like:

10,50,100

So you'll need to figure out what you want to do with those.

本文标签： mysqlGet posts from category with custom query