1

I'm developing a listing ads website dedicated to used video games with Laravel 7. I've got a 'GAMES' table and a 'LISTINGS' table as such.

GAMES

CREATE TABLE `games` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `title` text COLLATE utf8mb4_unicode_ci NOT NULL,
 `slug` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `deleted_at` timestamp NULL DEFAULT NULL,
 `created_at` timestamp NULL DEFAULT NULL,
 `updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `fulltext_index` (`title`)
) ENGINE=InnoDB AUTO_INCREMENT=10230 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

LISTINGS

CREATE TABLE `listings` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) unsigned NOT NULL,
`game_id` bigint(20) unsigned NOT NULL,
`name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`deleted_at` timestamp NULL DEFAULT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `listings_user_id_foreign` (`user_id`),
KEY `listings_game_id_foreign` (`game_id`),
CONSTRAINT `listings_game_id_foreign` FOREIGN KEY (`game_id`) REFERENCES `games` (`id`) ON DELETE CASCADE,
CONSTRAINT `listings_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=412 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I need to have a paginated list of all the 'GAMES' (not listings) that are being sorted by the 'created_at' column of the latest 'LISTING' attached.

Thanks to @mayankmodi who helped sloving one of the problem, I've updated the below's part to focus on the sorting issue.

If I do this in my gameController:

$games = Game::leftJoin('listings', function($leftJoin)
 {
 $leftJoin->on('listings.game_id', 'games.id')
 ->whereNull('listings.deleted_at');
 })
->select('games.*', 'listings.created_at')
->orderByDesc('listings.created_at')
->groupBy('games.id')
->with(['listings' => function ($query) {
$query->latest();
}])
->simplePaginate(36);

My games are distincts, but not ordered by last attached listing.created_at.

Do you have any idea how to solve this ?

OMP
  • 11
  • 4
  • @Strawberry : Thanks for your input. Is it better now ? – OMP Apr 19 '20 at 11:20
  • Hi. No. Not yet. – Strawberry Apr 19 '20 at 11:45
  • 1
    https://stackoverflow.com/questions/43776758/how-can-i-solve-incompatible-with-sql-mode-only-full-group-by-in-laravel-eloquen check this one – mayank modi Apr 19 '20 at 12:07
  • Thanks @mayankmodi ! Its makes the error goes away. Therefore, the GAMES aren't sorted by last LISTING.CREATED_AT yet. I'm still investigating. – OMP Apr 19 '20 at 12:31
  • @OMP thanks.... try to call ->orderByDesc('listings.created_at') before ->simplePaginate(36); – mayank modi Apr 19 '20 at 16:56
  • I figured this out indeed, the games are now sorted properly but based on the very first Listing ID instead of the most recent one. I think I have to modify the leftJoin method, so I get only the last listing ID being ordered. I'm looking at the Laravel Documentation to perform this. Pretty complex matters ... :D – OMP Apr 19 '20 at 18:09

1 Answers1

0

Ok, I got it to work as expected.

Should anyone can benefit from 4+ hours of trying things out, I'll paste my solution down here.

$games = Game::leftJoin('listings', function($leftJoin){
 $leftJoin->whereNull('listings.deleted_at')
 ->on('listings.game_id', 'games.id');
 })
->select('games.*', DB::raw('MAX(listings.id) AS latest_listing'))
->groupBy('games.id')
->orderByDesc('latest_listing')
->with('listings')
->simplePaginate(36);

Thanks for your help !

OMP
  • 11
  • 4