Query Builder + Where + Inner Select

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.

Borisu

7 years ago

Level 37

The question is what are you exactly trying to do? Just by reading the query I gather you want to select tms.id, tms.dayoff, tms.user_id and just extract the last tms.id (max in the subquery). If this is correct you can just take the latest record in eloquent:

$q = MonthlyStats::whereDate('stat_date', '2019-03-01')->latest()->first(['id', 'dayoff', 'user_id']);

MonthlyStats is the Eloquent model associated with the table tmteher_monthly_stats.

rajcsanyiz

7 years ago

Level 9

@BORISU - I need this query to make a summary report. User can close a specific months multiple times. The first select gather the statistic data but sometimes exists data multiple times per user. I think your suggestion is not answer clearly my question. I replace the where expression to whereDate. (It's cool)

Borisu

7 years ago

Level 37

@RAJCSANYIZ - Probably the date field wasn't getting selected properly and now that you're using whereDate it's fine.

rajcsanyiz

7 years ago

Best Answer

Level 9

I found two solutions:

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

        $query = DB::table('tmteher_monthly_stats as tms')
            ->select('tms.user_id')
            ->addSelect('tms.dayoff')
            ->whereDate('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', '=', DB::raw('tms.user_id'))
                    ->where('tms2.stat_date', '=', DB::raw('tms.stat_date'));
            });

Explanation:

The where expression doesn't use properly the apostrophes.

The inner select's where without DB::raw() recognize the field as string. So it's wrong: $query->where(`tms2`.`user_id` = 'tms.user_id')

This code helped me:

        DB::enableQueryLog();
        $query_success->get();
        $query_empty->get();
        dd(DB::getQueryLog());

Result:

array:2 [
  0 => array:3 [
    "query" => "select `tms`.`user_id`, `tms`.`dayoff` from `tmteher_monthly_stats` as `tms` where date(`tms`.`stat_date`) = ? 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)"
    "bindings" => array:1 [
      0 => "2019-02-01"
    ]
    "time" => 11.65
  ]
  1 => array:3 [
    "query" => "select `tms`.`user_id`, `tms`.`dayoff` from `tmteher_monthly_stats` as `tms` where date(`tms`.`stat_date`) = ? and `tms`.`id` = (select max(tms2.id) from `tmteher_monthly_stats` as `tms2` where `tms2`.`user_id` = ? and `tms2`.`stat_date` = ?)"
    "bindings" => array:3 [
      0 => "2019-02-01"
      1 => "tms.user_id"
      2 => "tms.stat_date"
    ]
    "time" => 0.71
  ]
]

Please or to participate in this conversation.