Update DB with date from another table

Is there an easier way for me to do this rather than 3 separate DB queries?

        $cTest= \DB::table('client_tests')
                            ->select(\DB::raw('MAX(test_date) AS tDate'))
                            ->where('client_id',$this->id)
                            ->where('type','cTest')
                            ->first();

        $tTest= \DB::table('client_tests')
                            ->select(\DB::raw('MAX(test_date) AS tDate'))
                            ->where('client_id',$this->id)
                            ->where('type','tTest')
                            ->first();

        \DB::table('clients')
            ->where('id',$this->id)
            ->update(['last_c_test' => $cTest->tDate, 'last_t_test' => $tTest->tDate]);

mudasir

10 years ago

Level 2

I think so this will work.

$test = \DB::table('client_tests')
              ->select(\DB::raw('MAX(test_date) AS tDate'))
              ->where('client_id',$this->id)
              ->where('type','cTest')
              ->orWhere('type','tTest')
              ->first();

now both $ctest and $tTest are in $test. You just need to manipulate it to get proper result.

ale1981

10 years ago

Level 3

That won't reduce the amount of DB calls as I will now have to do 2 updates instead of the one?

I can get both dates like this but then need to do separate update queries;

        $dates = \DB::table('client_tests')
                    ->select(\DB::raw('type, MAX(test_date) as tDate'))
                    ->where('client_id',$this->id)
                    ->groupBy('type')
                    ->get();

mudasir

10 years ago

Level 2

I think so this will help you http://stackoverflow.com/questions/16815551/how-to-do-this-in-laravel-subquery-where-in This is not the exact one but its similar to sub query.

Please or to participate in this conversation.