dimanche 30 mai 2021

Laravel subquery in from clause

I need to use subquery in from clause but i can not find such thing in Laravel docs

Laravel version 5.4

$sub = Chat::join("chats as _chats", function ($query) {
    $query->on('chats.room_id', "=", "_chats.room_id")
          ->on('chats.user_type', "<>", "_chats.user_type")
          ->on('chats.created_at', "=", DB::raw("(SELECT MIN(created_at) FROM chats WHERE created_at > '_chats.created_at')"));
    })
    ->selectRaw('TIMESTAMPDIFF(MINUTE, _chats.created_at, chats.created_at) as res')
    ->where('chats.user_type', 'pharmacy_consultant')
    ->where('chats.user_id', 26)
    ->toSql();

       
dd(
    DB::connection('mysql2')
        ->table(DB::raw("({$sub}) as sub"))
        ->select('res')
        ->get()
);
(2/2) QueryException SQLSTATE[HY000]: General error: 2031 
(SQL: select `res` from (select TIMESTAMPDIFF(MINUTE, _chats.created_at, chats.created_at) as res
from `chats` inner join `chats` as `_chats` on `chats`.`room_id` = `_chats`.`room_id` and `chats`.`user_type` <> `_chats`.`user_type` and `chats`.`created_at` = 
(SELECT MIN(created_at) FROM chats WHERE created_at > _chats.created_at) where `chats`.`user_type` = ? and `chats`.`user_id` = ?) as sub)


via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire