Question 16538 in Laravel Question posted in
The official Laravel Documentation can be found here.

How to convert sql to Laravel Eloquent

Have specific SQL query need to convert to Laravel eloquent, I already converted getting error, does everyone help me pls?

my sql

select 
    `d1`.*, `a1`.`user_uuid` as `activity_user` 
from 
    `directors` as `d1` 
left join 
    `activities` as `a1` 
on `a1`.`entity_uuid` = `d1`.`uuid` 
and `a1`.`created_at` = (
    select 
        max(`created_at`) 
    from
        `activities` as `a2`
    where `a2`.`entity_uuid`=`d1`.`uuid`
)
where `a1`.`user_uuid`='4f858f46-2d7e-41c8-a185-c9c4a4b506d1'
group by `a1`.`entity_uuid` 
order by `d1`.`updated_at` 
desc limit 100;

and my laravel eloquent

DB::table('directors')
->select('directors.*')
->leftJoin('activities',function($join) {
    $join->on('a1.entity_uuid','=','directors.uuid')
    ->on('a1.created_at','=',function($query) {
        $query->from('activities')
            ->select(DB::raw('max'('activities.created_at'))
            ->where('activities.entity_uuid','=',DB::raw('d1.uuid'));
    });
}
->where('activities.user_uuid','=','4f858f46-2d7e-41c8-a185-c9c4a4b506d1')
->groupBy('activities.entity_uuid')
->orderBy('directors.updated_at','desc')
->get();

the error what I get

TypeError: stripos(): Argument #1 ($haystack) must be of type string, Closure given in file C:xampphtdocscrmMembersbackendvendorlaravelframeworksrcIlluminateDatabaseGrammar.php on line 62
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    thanks for everyone, it solved

    just change double ->on to ->where and works perfect

    ->where('a1.created_at', '=', function($q){$q->from('activities as a2')
                                                ->select(DB::raw('max(`a2`.`created_at`)'))
                                                ->where('a2.entity_uuidm', '=', DB::raw('`d1`.`uuid`'));
                                        });

  2. 0

    Your Laravel DB query should be like this

    DB::table('directors')->select('directors.*', 'activities.user_uuid as activity_user')
    ->leftJoin('activities',function($join) {
        $join->on('a1.entity_uuid','=','directors.uuid')
             ->on('a1.created_at','=',function($query) {
                  $query->from('activities')
                        ->select(DB::raw('max(created_at)')
                        ->where('activities.entity_uuid','=',DB::raw('d1.uuid'));
               });
    })
    ->where('activities.user_uuid','=','4f858f46-2d7e-41c8-a185-c9c4a4b506d1')
    ->groupBy('activities.entity_uuid')
    ->orderBy('directors.updated_at','desc')
    ->get();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search