Laravel - How to create a query with where clause using subquery?

Question

I'm trying to generate this query using Laravel:

select mygames.id, mygames.name, mygames.slug, mygames.cover from mygames 
left join mygame_mygenre on mygames.id = mygame_mygenre.mygame_id 
left join mygame_myplatform on mygames.id = mygame_myplatform.mygame_id 
where mygame_mygenre.mygenre_id in (8, 9, 31, 32, 33) 
and mygame_myplatform.myplatform_id in (3, 6, 14, 34, 37, 39, 46, 48, 72, 130) 
and mygames.id <> 1990
and mygames.summary is not null 
and (select count(mygame_id) from mygame_myplatform where mygame_id = mygames.id) > 1 
group by mygames.id, mygames.name, mygames.slug, cover 
order by RAND() 
limit 6

My current code is:

$games = DB::table('mygames')
            ->leftjoin('mygame_mygenre', 'mygames.id', '=', 'mygame_mygenre.mygame_id')
            ->leftjoin('mygame_myplatform', 'mygames.id', '=', 'mygame_myplatform.mygame_id')
            ->select('mygames.id', 'mygames.name', 'mygames.slug', 'cover')
            ->when($genres_id, function ($query, $genres_id) {
                return $query->whereIn('mygame_mygenre.mygenre_id', $genres_id);
            })
            ->when($platforms_id, function ($query, $platforms_id) {
                return $query->whereIn('mygame_myplatform.myplatform_id', $platforms_id);
            })
            ->where('mygames.id', '<>', $this->id)
            ->whereNotNull('mygames.summary')
            ->where(function ($query) {
                $query->selectRaw('count(mygame_id)')
                ->from('mygame_myplatform')
                ->where('mygame_id', 'mygames.id');
            }, '>', 1)
            ->groupBy('mygames.id', 'mygames.name', 'mygames.slug', 'cover')
            ->inRandomOrder()
            ->take(6)
            ->get();

This code is not working because inside the closure function I was unable to pass the name of the mygames table with the id field. Laravel is interpreting as a text parameter and not as a table.field

->where(function ($query) {
                    $query->selectRaw('count(mygame_id)')
                    ->from('mygame_myplatform')
                    ->where('mygame_id', 'mygames.id'); <<<<<<<<<<<<<
                }, '>', 1)

I tried to use 'use ()' but it didn't work either.

Could you help me?

Instead of using DB helper class, trying using Eloquent Relations and and functions like whereHas(), with(). Once you define valid relations, with() and whereHas() can make your life simple and code easy to understand. — Insane Developer
– Insane Developer, Commented May 5, 2021 at 20:22

IGP · Accepted Answer · 2021-05-05 20:34:35Z

2

Here I assume you're trying to compare 2 columns, right?

->where('mygame_id', 'mygames.id');

In that case, use the whereColumn/orWhereColumn method.

->whereColumn('mygame_id', 'mygames.id')

answered May 5, 2021 at 20:34

IGP

16.4k5 gold badges35 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jascar_destin · Accepted Answer · 2021-05-05 20:42:28Z

It's because you must use the whereColumn method to achieve this. https://laravel.com/docs/8.x/queries#additional-where-clauses Another solution is to use the whereRaw method. See :

DB::table('mygames')
            ->select(['mygames.id', 'mygames.name', 'mygames.slug', 'mygames.cover'])
            ->leftJoin('mygames_mygenre', 'mygames.id', '=', 'mygame_mygenre.mygame_id')
            ->leftJoin('mygame_myplatform', 'mygames.id', '=', 'mygame_myplatform.mygame_id')
            ->whereIn('mygame_mygenre.mygenre_id', [8, 9, 31, 32, 33])
            ->whereIn('mygame_myplatform.myplatform_id', [3, 6, 14, 34, 37, 39, 46, 48, 72, 130])
            ->where('mygames.id', '<>', 1990)
            ->whereNotNull('mygames.summary')
            ->where(1, '<', function ($query) {
                $query->selectRaw('COUNT(mygame_id)')
                    ->from('mygame_myplatform')
                    ->whereColumn('mygame_id', 'mygames.id');
            })
            ->groupBy('mygames.id', 'mygames.name', 'mygames.slug', 'cover')
            ->inRandomOrder()
            ->take(6)
            ->get();

ridwan_iridwan · Accepted Answer · 2021-05-05 21:55:57Z

looks similar to @jascar_destin answer, but on the group by, you ll have to specify which table the cover is been picked from except if cover does not exists on other tables rather than mygames.

DB::table('mygames AS mg')
        ->leftJoin('mygame_mygenre AS mgmg', 'mgmg.mygame_id', '=', 'mg.id')
        ->leftJoin('mygame_myplatform AS mgmp', 'mgmp.mygame_id', '=', 'mg.id')
        ->select(['mg.id', 'mg.name', 'mg.slug', 'mg.cover'])
        ->whereIn('mgmg.my_genre_id', [8, 9, 31, 32, 33])
        ->whereIn('mgmp.my_platform_id'. [3, 6, 14, 34, 37, 39, 46, 48, 72, 130])
        ->where('mg.id', '<>', 1990)
        ->whereNotNull('mg.summary')
        ->where(1, '<', function ($query) {
            $query->selectRaw('COUNT(mygame_id)')
                ->from('mygame_myplatform')
                ->whereColumn('mygame_id', 'mg.id');
        })
        ->groupBy('mg.id', 'mg.name', 'mg.slug', 'mg.cover')
        ->inRandomOrder()
        ->take(6)
        ->get();

Collectives™ on Stack Overflow

Laravel - How to create a query with where clause using subquery?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related