Query Builder + Where + Inner Select

Posted 3 months ago by rajcsanyiz

I have a SQL query:

        SELECT tms.id,
                tms.user_id,
                tms.dayoff
               FROM tmteher_monthly_stats AS tms
              WHERE tms.stat_date = '2019-03-01'
                    AND tms.id = (
                    SELECT MAX(tms2.id)
                  FROM tmteher_monthly_stats AS tms2
                  WHERE tms2.user_id = tms.user_id
                    AND tms2.stat_date = tms.stat_date);

I would like to transform the previous SQL with the eloquent query builder. But this code returns an empty array (without error):

        $period = '2019-03';
        $query = DB::table('tmteher_monthly_stats as tms')
            ->select('tms.user_id')
            ->addSelect('tms.dayoff')       
            ->where('tms.stat_date', '=', $period.'-01')
            ->where('tms.id', '=', function($query) {
                $query->select(DB::raw('max(tms2.id)'))
                    ->from('tmteher_monthly_stats AS tms2')
                    ->where('tms2.user_id', '=', 'tms.user_id')
                    ->where('tms2.stat_date', '=', 'tms.stat_date');
            });

The outer (first) select works correct, but the inner select doesn't.

Please sign in or create an account to participate in this conversation.

Reply to

Use Markdown with GitHub-flavored code blocks.