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