Adding a where to a nested query in Laravel

Question

I want some help with a WHERE clause $start date is set further up in the code, and the query works when running against SQL from within Laravel. I get an error saying that $startdate isn't set. I am not sure that I am even doing WHERE correctly.

$query = DB::query()
    ->select('CC AS CountOfVisits', DB::raw('count(CC) AS Users'))
    ->fromSub
    (function (\Illuminate\Database\Query\Builder $query) {
            $query->select('user_id', DB::raw('count(user_id) AS CC '))
                ->from('mytable')
                ->where('created_at', '>=', $startdate)
                ->groupBy('user_id');

        }, 'CC')
    ->groupBy('CC');

$result = DB::connection('mysql2')->select($query->toSql());

I think you need to show more code to see where $startdate is declared. — Jonathan
– Jonathan, Commented Oct 17, 2019 at 23:45
Thanks the SQL works SELECT CC AS CountOfVisits, Count(CC) AS Users FROM ( SELECT user_id, count(user_id) AS CC FROM mytable WHERE created_at <= '2019-10-29' AND created_at >= '2019-10-01' GROUP BY user_id ) AS CC GROUP BY CC; the issue is getting the variable into the function. I tried function (\Illuminate\Database\Query\Builder $query) use($startdate) { but that didn't work either — CamboCambo
– CamboCambo, Commented Oct 18, 2019 at 2:21

ryantxr · Accepted Answer · 2019-12-10 19:39:06Z

1

I'm putting this here for others who find this page. It should be noted that fromSub() can also take a Builder object instead of a closure.

$builder = DB::table('mytable')->where('created_at', '>=', $start_date)
   ->select('user_id', DB::raw('count(user_id) AS CC '))
   ->groupBy('user_id');

$query = DB::query()
    ->select('CC AS CountOfVisits', DB::raw('count(CC) AS Users'))
    ->fromSub($builder, 'temp')
    ->groupBy('CC');

In my case, I already had a Builder object that I wanted to use in a sub select.

answered Dec 10, 2019 at 19:39

ryantxr

4,2271 gold badge15 silver badges25 bronze badges

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

Comments

Raul · Accepted Answer · 2019-10-18 03:47:49Z

0

That's because your fromSub is an anonymous function, also known as a closures. It does not know about variables that you define outside of the closure. You can read more about them in the official PHP docs

Rewrite your query to:

$result = DB::connection('mysql2')
    ->query()
    ->select('CC AS CountOfVisits', DB::raw('count(CC) AS Users'))
    ->fromSub(function($query) use ($startdate) {
        $query->select('user_id', DB::raw('count(user_id) AS CC '))
            ->from('mytable')
            ->where('created_at','>=',$startdate)
            ->groupBy('user_id');
        }, 'temp')
    ->groupBy('CC')
    ->get();

edited Oct 18, 2019 at 3:47

answered Oct 18, 2019 at 0:43

Raul

5791 gold badge4 silver badges13 bronze badges

13 Comments

CamboCambo Over a year ago

Thanks for the input, i tried this and it returns SQLSTATE[HY000]: General error: 2031

Raul Over a year ago

well that’s a different error from your original post. if you need help with your query you’ll have to give us more info and paste more code as you only pasted partial code. also please post the full error that you are getting not just the code

CamboCambo Over a year ago

Error:

Illuminate \ Database \ QueryException (HY000) SQLSTATE[HY000]: General error: 2031 (SQL: select `CC` as `CountOfVisits`, count(CC) AS Users from (select `user_id`, count(user_id) AS CC from `mytable` where `created_at` >= ? group by `user_id`) as `CC` group by `CC`)

CamboCambo Over a year ago

$startdate = $request->startdate;          $query = DB::query()             ->select('CC AS CountOfVisits', DB::raw('count(CC) AS Users'))             ->fromSub                 (                     function (\Illuminate\Database\Query\Builder $query) use ($startdate) {                         $query->select('user_id', DB::raw('count(user_id) AS CC '))                         ->from('establishment_checkins')                         ->where('created_at','>=',$startdate)                         ->groupBy('user_id');                                 }, 'CC') ->groupBy('CC');

CamboCambo Over a year ago

$startdate = '2019-10-17'

|

Collectives™ on Stack Overflow

Adding a where to a nested query in Laravel

2 Answers 2

Comments

13 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

13 Comments

Your Answer

Sign up or log in

Post as a guest

Related