Return only the newest record of each author

Question

Return only the newest record of each author

Navigation

#1 by (1 votes)
#2 by (1 votes)
#3 by (-2 votes)

4

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 multiple texts with the same author) and I get the newest ones in ascending order only. I'm using Lavarel and my Eloquent code looks like this:

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

If I put groupBy instead of distinct , I'm returning only the oldest of the records. How to do it the other way around?

What I'm doing is returning a list of text in which the author has not registered for some time, I want you to return to me to know which author does not register anything.

php laravel-4 laravel-eloquent

asked by anonymous 16.03.2014 / 01:57

3 answers

1

The problem is that you are asking that the ordering of the data be based on the field data_discurso in ascending order (from oldest to newest), when in fact it should be descending (from the youngest to the oldest). The get() method returns ~~always the first search result~~ a group of ten records (by using take(10) ), and in this case, the first record should be the oldest. To correct, just change the following excerpt

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

for

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

16.03.2014 / 15:13

-2

Sort the dates in descending order:

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

16.03.2014 / 14:40

Creating graphs with JSON and Highcharts [closed] Artificial Intelligence using PHP and MySQL [closed]

score 1 · Accepted Answer

The query you need, in pure SQL, looks 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 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 as needed:

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

For more information, consult the documentation:

link