Forum Query with Query Builder. What's wrong?

haseeb90

posted 10 years ago

Database

Hi I want to run the following query with the query builder ... this is what i have

Query

    SELECT 
    taggedUserName, count(taggedUserId) AS freq
FROM
    feedMentions
WHERE
    postId IN (SELECT postId FROM feedPosts WHERE postedByUserName = 'User Name')
GROUP BY taggedUserId
ORDER BY freq DESC;

Query Builder

    $users = DB::table("feedMentions")
                ->select(
                    DB::raw('taggedUserName'),
                    DB::raw('COUNT(taggedUserId) AS freq')
                )
                ->where(
                    DB::raw("postId IN (SELECT postId FROM feedPosts WHERE postedByUserName = '{$userId}') ")
                )
                ->groupBy('taggedUserId')
                ->orderBy('freq', 'DESC');
                    
    return $users;

The query builder query is giving an error. I know I'm doing something wrong but cant spot it.

Last updated 3 years ago.

usm4n

replied 10 years ago

Solution

Hi Haseeb, You should not use the user input directly inside a raw query expression. Neways I hope this solves your problem:

    $users = DB::table("feedMentions")
                ->select(
                    DB::raw('taggedUserName'),
                    DB::raw('COUNT(taggedUserId) AS freq')
                )
                ->whereRaw(DB::raw('postId IN (SELECT postId FROM feedPosts WHERE postedByUserName = :userId)'),['userId'=>$userId])
                ->groupBy('taggedUserId')
                ->orderBy('freq', 'DESC')->get();