nested queries in laravel query builder

Question

I need to get 1 post according to request id with following structure:

postId;
postTitle;
postContent;
postImage;
bandName;
genreName;
tags: [tagId, tagName];
comments: [commentId, commentBody, commentCreatedAt].

Tables structure:

posts (id, title, content, image, band_id, timestamps);
tags (id, name);
post_tag(post_id, tag_id);
comments(id, body, post_id, user_id, timestamps).

I've tried different variations of query, such like:

$post = DB::table('posts as p')
    ->select('p.id as postId',
        'p.title as postTitle',
        'p.content as postContent',
        'p.image as postImage',
        'b.name as bandName',
        'g.name as genreName',
            DB::raw("(SELECT t.id as tagId, t.name as tagName
                     FROM tags as t
                     JOIN post_tag as pt ON t.id = pt.tag_id
                     WHERE pt.post_id = $request->postId
                     GROUP BY tagId) as tags"))
    ->join('bands as b', 'b.id', 'p.band_id')
    ->join('genres as g', 'g.id', 'b.genre_id')
    ->where('p.id', $request->postId)
    ->groupBy(
        'postId',
        'postTitle',
        'postContent',
        'postImage',
        'bandName',
        'genreName')
    ->get();

But I got stuck fetching tags(( it returns me error: SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 1 column(s), or other.

How to get tags for the post(the query for comments will be similar)? Can't handle such nested queries(( I appreciate any help.

Update 1.

Tried:

$post = DB::table('posts as p')
        ->select('p.id as postId',
            'p.title as postTitle',
            'p.content as postContent',
            'p.image as postImage',
            'b.name as bandName',
            'g.name as genreName',
            't.id as tagId',
            't.name as tagName')
        ->join('post_tag as pt', 'p.id', 'pt.post_id')
        ->join('tags as t', 't.id', 'pt.tag_id')
        ->join('bands as b', 'b.id', 'p.band_id')
        ->join('genres as g', 'g.id', 'b.genre_id')
        ->where('p.id', $request->postId)
        ->groupBy(
            'postId',
            'postTitle',
            'postContent',
            'postImage',
            'bandName',
            'genreName',
            'tagId')
        ->get();

Result:

[{
    "postId",
    "postTitle",
    "postContent",
    "postImage",
    "bandName",
    "genreName",
    "tagId",
    "tagName"
},{
    "postId",
    "postTitle",
    "postContent",
    "postImage",
    "bandName",
    "genreName",
    "tagId",
    "tagName"
}]

So,"postId","postTitle","postContent","postImage","bandName","genreName" are duplicated((

YouneL YouneL · Accepted Answer · 2017-12-19T19:12:37

When you use subquery inside select statement it has to return one column value that's why you are getting this error, however If you want to get tags name and id too why not just add another join with tags and post_tag tables, here is an example:

$post = DB::table('posts as p')
->select('p.id as postId',
    'p.title as postTitle',
    'p.content as postContent',
    'p.image as postImage',
    'b.name as bandName',
    'g.name as genreName',
    't.id as tagId',
    't.name as tagName')
->join('post_tag as pt', 'p.id', 'pt.post_id')
->join('tags as t', 't.tag_id', 'pt.tag_id')
->join('bands as b', 'b.id', 'p.band_id')
->join('genres as g', 'g.id', 'b.genre_id')
->where('p.id', $request->postId)
->groupBy(
    'postId',
    'postTitle',
    'postContent',
    'postImage',
    'bandName',
    'genreName')
->get();

Hope this helps

nested queries in laravel query builder

1 Answers