Eloquent does not return the same results as SQL

1

I have this SQL code that when running directly in the database works fine, but I had to convert it to the laravel format. It performs but returns no results.

Where am I going wrong? It seems that the problem is there in% w / o% of% w / o, where it takes the columns of the main query , since I changed the fields by manual values and it worked,

->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();
    
asked by anonymous 06.08.2018 / 14:35

1 answer

1

If someone also can not compare the columns of query main with subquery , it is resolved as follows:

You need to change where to whereColumn . This is a method used specifically to check if two columns are the same.

Example:

->whereColumn('m2.remetente_id', '=', 'mensagens.destinatario_id')
->whereColumn('m2.destinatario_id', '=', 'mensagens.remetente_id');
    
06.08.2018 / 16:10