Eloquent groupBy problem and Select columns from multiple tables

Question

I want to search the most recent business won (estado = 'Ganado') from each client from the current date to a period entered.

In addition, the client must have a business earned before the period entered.

I have a mySql query that works fine, but the Eloquent query doesn't.

MySql:

SELECT sortedTable.id, sortedTable.cliente_id, sortedTable.periodo, C.razon_social, U.name, U.apellido_p, U.apellido_m
FROM (SELECT * FROM negocios ORDER BY periodo_date DESC) as sortedTable INNER JOIN clientes C, users U //Order the table "negocios" by date to groupBy and keep the client with recently business won
WHERE C.id = sortedTable.cliente_id and sortedTable.user_id = U.id and sortedTable.estado = 'Ganado' and (sortedTable.periodo_date BETWEEN '2018-01-01 00:00:00' AND CURDATE()) //Search customers with a business won between the date entered and the current date
      and sortedTable.cliente_id in (SELECT cliente_id
                                     FROM negocios 
                                     WHERE estado = 'Ganado' and periodo_date < '2018-01-01 00:00:00') // The client has to have a business won before the period entered.
GROUP BY sortedTable.cliente_id

Eloquent:

$negociosGanados = \DB::table(\DB::raw("(SELECT * FROM negocios ORDER BY periodo_date DESC) as sortedTable"))
        ->join('clientes', 'sortedTable.cliente_id', '=', 'clientes.id')
        ->join('users', 'sortedTable.user_id', '=', 'users.id')
        ->where('sortedTable.estado', 'Ganado')
        ->whereBetween('sortedTable.periodo_date', array($periodo, $periodoActual))
        ->whereRaw("sortedTable.cliente_id in (SELECT cliente_id FROM negocios
                    WHERE estado = 'Ganado' AND periodo_date < '".$periodo."')")
        ->select('sortedTable.id',
            'sortedTable.cliente_id',
            'sortedTable.periodo_date'
        )
        ->groupBy('sortedTable.cliente_id')
        ->get();

That eloquent query works fine, but when I put columns on the Select from table "clientes" and "users" it doesn't.

->select('sortedTable.id',
            'sortedTable.cliente_id',
            'sortedTable.periodo_date',
            'clientes.razon_social',
            'users.name'
        )

Error:

SQLSTATE[42000]: Syntax error or access violation: 1055 'crm.clientes.razon_social' isn't in GROUP BY..

I try to put the new columns on the groupBy and fixed the error, but it doesn't group by cliente.id and show me several business from the same client.

->groupBy('sortedTable.cliente_id', 'clientes.razon_social', 'users.name')

The query is invalid for strict mode (which is default in laravel). In non-strict mode the query might run, but doesn't necessarily return what you want. What you probably need is something like this: [sql-select-only-rows-with-max-value-on-a-column](https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column). — Paul Spiegel, Oct 18 '19 at 16:09

Eloquent groupBy problem and Select columns from multiple tables

0 Answers0