2
How can I convert this code sql
in the eloquent
?
SELECT
CONCAT(FLOOR(sum(diferenca)/60),'h',MOD(sum(diferenca),60),'m') as tempo
FROM
(SELECT
TIMESTAMPDIFF(MINUTE, m1.created_at, min(m2.created_at)) as diferenca
FROM
mensagens m1
JOIN
mensagens m2 ON m1.remetente_id = m2.destinatario_id AND m2.remetente_id = m1.destinatario_id AND m2.created_at > m1.created_at
GROUP BY
m1.remetente_id,
m1.destinatario_id,
m1.created_at,
m1.id) AS table1
WHERE
(SELECT
MIN(id)
FROM
mensagens
WHERE
destinatario_id = 2);
I tried it the way below, but he’s making a mistake because he’s treating the whole select
of concat
as a column, and I was also in doubt on how to assemble the join
when you have 4 parameters.
$mensagem = Mensagem::whereIn('id', function($query) {
$query->selectRaw('TIMESTAMPDIFF(MINUTE, m1.created_at, min(m2.created_at)) as diferenca')
->from('mensagens as m1')
->join('mensagens as m2', 'm1.remetente_id', '=', 'm2.destinatario_id')
->where('m2.created_at', '>', 'm1.created_at')
->groupBy('m1.remetente_id', 'm1.destinatario_id', 'm1.created_at', 'm1.id');
})
->where(function($query) {
$query->selectRaw('min(id)')
->from('mensagens')
->where('destinatario_id', 2);
})
->select("CONCAT(FLOOR(sum(diferenca)/60),'h',MOD(sum(diferenca),60),'m') as tempo")
->get();
Hello Diego, I had a similar problem, see if this helps you: https://answall.com/questions/293793/como-utilizar-with-no-retorno-de-um-dbselect-laravel
– David Dias
Specifically the answer, of course: https://answall.com/a/301595/21836
– David Dias