Order by Before Group by Using Eloquent (Laravel)

Order By before Group By using Eloquent (Laravel)

I found a way to do that! Basically, creating a subquery and running it before, so that results are ordered as expected and grouped after.

Here is the code:

$sub = Message::orderBy('createdAt','DESC');

$chats = DB::table(DB::raw("({$sub->toSql()}) as sub"))
    ->where('toId',$id)
    ->groupBy('fromId')
    ->get();

Laravel Group By and Order By not working

I think you need max value ,

  myModel::select('id','purch','name',DB::raw("MAX(prcvalue) as prcvalue"))
  ->where('purch','=','10234')
  ->orderBy('prcvalue','DESC')
  ->groupBy('name')
  ->get();

And if it still not working , edit in config/database.php.
In mysql array , set to strict => false

How can i orderby and groupby in one query in laravel

$errors = UserErrors::groupBy('feild_name')->orderBy('feild_name','DESC')->get();

Example as below

$query->where('feild_name', '=', 'active')
    ->groupBy('feild_name')
    ->orderBy('date','DESC')
    ->get();

Query builder - combining orderBy and groupBy

Your current strategy for the desired output is correct, and you don't need a subquery. The following raw MySQL query should suffice:

SELECT company, MAX(date)
FROM companies
GROUP BY company

We can try the following Laravel code:

return $query->where('period', '=', 'live')
    ->groupBy('company')
    ->orderBy('date','desc')
    ->get(['company', DB::raw('MAX(date) AS max_date')]);

To the contrary of what you said in your question, if you query with ORDER BY and the underlying data in the table does not change then the results of the query should be completely reproducible.

Edit:

Contrary to your question, your comments indicate that you want the entire matching row for each max date per company. If so, then you really want the following raw MySQL query:

SELECT c1.*
FROM companies c1
INNER JOIN
(
    SELECT company, MAX(date) AS max_date
    FROM companies
    GROUP BY company
) c2
    ON c1.company = c2.company AND
       c1.date = c2.max_date

We can try writing the following Laravel code to handle this:

DB::table('companies')
    ->select('*')
    ->join(DB::raw('(SELECT company, MAX(date) AS max_date
                     FROM companies GROUP BY company) c2'), function($join)
        {
            $join->on('companies.company', '=', 'c2.company');
            $join->on('companies.date', '=', 'c2.max_date');
        })
    ->orderBy('companies.date', 'DESC')
    ->get();

How to order grouped results using Eloquent?

You need to add a MAX function in the SELECT clause for each column to be ordered in DESCending order. The inverse goes for columns ordered in ASCending order, you need to add MIN function in the SELECT clause.

Your Eloquent query has to include a raw select:

$issues = DB::table('issues')
    ->select(DB::raw('id, max(year) as year, max(yearly_issue) as yearly_issue, stock, created_at, updated_at, magazine_id'))
    ->groupBy('magazine_id')
    ->orderBy('year', 'desc')
    ->orderBy('yearly_issue', 'desc')
    ->take(10)
    ->get();

The only drawback is that you need to specify each column you want to retrieve. And do not use the * selector, it will override the aggregate function in the select clause.

---

Update: Seems like adding the * selector before the aggregate functions in the SELECT clause works too. This means that you rewrite the raw select as:

->select(DB::raw('*, max(year) as year, max(yearly_issue) as yearly_issue'))

I think putting the * selector before makes the aggregate functions overrides their columns.

(Laravel) can't use group by with order by

The issues is that you SHOULD really include the ORDER BY in the GROUP BY list as this is best practise.

The reason it works when you are on the the sql mode set to ''. However, Laravel by default (I think) has Strict as TRUE,

You have 2 options:

1. Add the created_at to the GROUP BY clause (Recommended)
2. Change the strict mode to false

A bit more info on the 2nd option:

How can I solve incompatible with sql_mode=only_full_group_by in laravel eloquent?

Order by and Group by with Laravel

You have them after get(), which means it is now a Collection query and no longer a QueryBuilder object. There are two options:

Move it before get(), so that MySQL does the ordering and grouping:

$courses = CourseTopic::select([
    'course_topics.id',
    'course_topics.course_id',
    'course_topics.description',
    'course_topics.visible',
    'course_topics.name',
    'course_activities.order_no',
    'course_activities.activity_id',
    'activity_types.table_name'
])
->where('course_id', $course_id)
->leftJoin('course_activities', 'course_activities.course_topic_id', 'course_topics.id')
->leftJoin('activity_types', 'activity_types.id', 'course_activities.activity_type_id')
->groupBy('course_topics.id')
->orderBy('course_activities.order_no')
->get();

Or use sortBy, which is the Collection method. You'll need to use the returned column name instead of the MySQL relation

$courses = CourseTopic::select([
    'course_topics.id',
    'course_topics.course_id',
    'course_topics.description',
    'course_topics.visible',
    'course_topics.name',
    'course_activities.order_no',
    'course_activities.activity_id',
    'activity_types.table_name'
])
->where('course_id', $course_id)
->leftJoin('course_activities', 'course_activities.course_topic_id', 'course_topics.id')
->leftJoin('activity_types', 'activity_types.id', 'course_activities.activity_type_id')
->get()
->groupBy('id')
->sortBy('order_no');

---

Related Topics