Eloquent does not return the same results as SQL

Asked

Viewed 78 times

1

I have that code SQL which when running straight into the bank works normal, but I had to convert it to the format of the Larable. It runs, but returns no results.

Where am I wrong? It seems that the problem is there where of subquery, where he picks up the columns from query main, because I changed the fields by manual values and it worked, I did so:

->where('m2.remetente_id', '=', 1)
->where('m2.destinatario_id', '=', 2);

SQL

SELECT
  m1.id
FROM
  mensagens m1
WHERE
  m1.destinatario_id = 1 AND m1.created_at < (
    SELECT
      m2.created_at
    FROM
      mensagens m2
    WHERE
      m2.remetente_id = m1.destinatario_id AND m2.destinatario_id = m1.remetente_id)
GROUP BY
    m1.id

LARAVEL

Mensagem::where('destinatario_id', 1)
            ->where('created_at', '<', function($q) {
                $q->from('mensagens AS m2')
                  ->select('created_at')
                  ->where('m2.remetente_id', '=', 'mensagens.destinatario_id')
                  ->where('m2.destinatario_id', '=', 'mensagens.remetente_id');
            })
            ->select('id')
            ->groupBy('id')
            ->get();
laravel laravel-eloquent

  • But what is the meaning of created_at be smaller than subquery?

  • @Wallacemaxters need to list the first message of every logged-in user conversation, but only those where they didn’t start the conversation, so I do this comparison of dates

  • @Wallacemaxters tried to use DB::select to run the code and it worked, the problem is only when I do it the way I mentioned in the post. Do you have any idea what the problem might be?

  • Solved! I traded Where for whereColumn and it worked!

1 answer

1

In case someone also can not compare the columns of query main with those of the subquery, was resolved as follows:

It is necessary to exchange the where for whereColumn. This is a method used specifically to check whether two columns are equal.

Example:

->whereColumn('m2.remetente_id', '=', 'mensagens.destinatario_id')
->whereColumn('m2.destinatario_id', '=', 'mensagens.remetente_id');

Browser other questions tagged laravel laravel-eloquent

You are not signed in. Login or sign up in order to post.