Return only the newest record of each author

Question

I have a table with the following fields:

id, texto, autor_id, data

I need the records to be grouped by autor_id (because it has several texts with the same author) and return me in ascending order the newest only. I’m using Lavarel and my Eloquent code is as follows::

Discursos::take(10)->distinct('membro_id')->orderBy('data_discurso', 'asc')->get();

Place groupBy in place of distinct is returning me only the oldest of the records. How to do otherwise?

What I am doing is returning a text list in which the author has not registered for a long time, I want you to return to me to know which author has not registered anything for a long time.

Answer 1

The query you need, in pure SQL, is like this:

SELECT membro_id, MAX(data_discurso)
FROM tabela
GROUP BY membro_id
ORDER BY MAX(data_discurso) DESC, membro_id

If you need any more fields in the SELECT (for example, the text itself), you will need to use a subquery.

Using Eloquent the above query would look like this:

$resultado = DB::table('tabela')->select(DB::raw('membro_id, MAX(data_discurso)'))
                     ->groupBy('membro_id')
                     ->orderBy(DB::raw('MAX(data_discurso) DESC, membro_id'))->get();

Edited: Eloquent accepts Query Builder expressions, such as wherein, you just need to edit the sub query:

Discursos::whereIn(DB::raw('SELECT MAX(data_discurso) FROM discursos GROUP BY membro_id'))

For more information see the documentation:

http://laravel.com/docs/eloquent

http://laravel.com/docs/queries

Answer 2

The problem is that you are asking for data sorting to be field-based data_discurso in order ascendant (from the oldest to the youngest), when in fact it should be descending (from the newest to the oldest). The method get() returns always the first search result a group of ten records (by the use of take(10)) and in this case the first record should be the oldest. To correct, simply change the following section

->orderBy('data_discurso', 'asc')

for

->orderBy('data_discurso', 'desc')

Answer 3

Order dates in descending order:

Discursos::take(10)->distinct('membro_id')->orderBy('data_discurso', 'desc')->get();