😉How to Group By with Order By Desc in Laravel? unique thuộc collections, toSql, DB::raw (ok)

https://www.itsolutionstuff.com/post/how-to-group-by-with-order-by-desc-in-laravelexample.html

Chú ý: orderBy không thuộc collections

How to Group By with Order By Desc in Laravel?

By Hardik Savani June 24, 2020 Category : LaravelPlayUnmuteLoaded: 1.17%FullscreenThis post is focused on laravel group by with order by desc. if you want to see example of laravel eloquent group by order by desc then you are a right place. you can understand a concept of laravel group by orderby. i would like to show you laravel eloquent group by and order by. it will also usable with laravel 6, laravel 7, laravel 8 and laravel 9 app.

In this post, i will give you some solution for how to get records with order by desc with group by in laravel application. when you use laravel eloquent group by with order by desc then it's now working as we want. we need to get last added records first with group by.

Here, i will give you some solution with how you to order by before group by in laravel eloquent.

So let's see bellow solution:

Ví dụ 1:

/var/www/blog/routes/web.php

<?php
use Illuminate\Support\Facades\Route;
use App\Http\Controllers\HomeController;
/*
|--------------------------------------------------------------------------
| Web Routes
|--------------------------------------------------------------------------
|
| Here is where you can register web routes for your application. These
| routes are loaded by the RouteServiceProvider within a group which
| contains the "web" middleware group. Now create something great!
|
*/
Route::get('/', function () {
    return view('welcome');
});
Auth::routes();

Route::get('/user', [HomeController::class, 'myUsers'])->name('user');
Route::get('/test', [HomeController::class, 'test'])->name('test');

/var/www/blog/app/Http/Controllers/HomeController.php

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Models\User;
use App\Models\Message;
class HomeController extends Controller
{
    /**
     * Create a new controller instance.
     *
     * @return void
     */
    public function __construct()
    {
        $this->middleware('auth');
    }
    /**
     * Show the application dashboard.
     *
     * @return \Illuminate\Contracts\Support\Renderable
     */
    public function index()
    {
        return view('home');
    }
    public function test() {
    $tests = Message::select("*")
    ->where('receiver_id',2)
    ->orderBy('created_at', 'desc')
    ->get()
    ->unique('sender_id');
    return view('tests')->with(compact('tests'));
  }
}

/var/www/blog/resources/views/tests.blade.php

@extends('layouts.app')
@section('content')
<div class="container">
  <div class="row justify-content-center">
    <div class="col-md-8">
      <div class="card">
        <div class="card-header">Dashboard</div>
        <div class="card-body">
          @foreach ($tests as $test)
            {{ $test->content }}  <hr/>
          @endforeach
        </div>
      </div>
    </div>
  </div>
</div>
@endsection

/var/www/blog/database/migrations/2022_08_12_163337_create_messages_table.php

<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreatemessagesTable extends Migration {
  /**
   * Run the migrations.
   *
   * @return void
   */
  public function up() {
    Schema::create('messages', function (Blueprint $table) {
      $table->id();
      $table->string('receiver_id');
      $table->string('sender_id');
      $table->string('content');
      $table->timestamps();
    });
  }
  /**
   * Reverse the migrations.
   *
   * @return void
   */
  public function down() {
    Schema::dropIfExists('messages');
  }
}

/var/www/blog/database/factories/MessageFactory.php

<?php
namespace Database\Factories;
use App\Models\Message;
use Illuminate\Database\Eloquent\Factories\Factory;
class MessageFactory extends Factory {
  protected $model = Message::class;
  /**
   * Define the model's default state.
   *
   * @return array
   */
  public function definition() {
    return [
      'content' => $this->faker->text,
      'receiver_id' => $this->faker->numberBetween(1, 10),
      'sender_id' => $this->faker->numberBetween(1, 5),
    ];
  }
}

/var/www/blog/database/seeders/MessageSeeder.php

<?php
namespace Database\Seeders;
use App\Models\Message;
use Illuminate\Database\Seeder;
class MessageSeeder extends Seeder {
  /**
   * Run the database seeds.
   *
   * @return void
   */
  public function run() {
    Message::factory()->count(30)->create();
  }
}

Solution 1:

/**
 * Show the application dashboard.
 *
 * @return \Illuminate\Http\Response
 */
public function index()
{
    $messages = Message::select("*")
                            ->where('receiver_id',$id)
                            ->orderBy('created_at', 'desc')
                            ->get()
                            ->unique('sender_id');
  
    dd($messages);
}

Ví dụ 2.1:

/var/www/blog/app/Http/Controllers/HomeController.php

Solution 2:

/**
 * Show the application dashboard.
 *
 * @return \Illuminate\Http\Response
 */
public function index()
{
    $message = Message::orderBy('created_at','DESC');
   
    $messages = DB::table(DB::raw("({$message->toSql()}) as sub"))
        ->where('receiver_id',$id)
        ->groupBy('sender_id')
        ->get();
   
    dd($messages);
}

Solution 3:

/**
 * Show the application dashboard.
 *
 * @return \Illuminate\Http\Response
 */
public function index()
{
    $messages = Message::select(DB::raw('*, max(created_at) as created_at'))    
                            ->where('receiver_id',$id)
                            ->orderBy('created_at', 'desc')
                            ->groupBy('sender_id')
                            ->get();
   
    dd($messages);
}

Solution 4:

Read Also: Laravel Collection GroupBy with Examples

/**
 * Show the application dashboard.
 *
 * @return \Illuminate\Http\Response
 */
public function index()
{
    $messages = Message::select("*")    
                            ->where('receiver_id',$id)
                            ->orderBy('created_at', 'desc')
                            ->groupBy('sender_id')
                            ->get();
   
    dd($messages);

I hope it can help you...