How to load posts assigned to sub categories

I have some Categories, they have multiple sub Categories (same model for both, using a parent_id field).
Posts are assigned to the sub Categories.
There's a sort_order field on the category_post pivot table.

How can I get all Posts for a Category (where the posts are assigned to the sub Categories) with the sort_order field?

I've tried using the following whereHas, which does work but that loses the sort_order

$posts = Post::whereHas('categories', function($query) use ($categoryId) {
  $query->whereHas('parentCategory', function($query) use ($categoryId) { 
    $query->where('id', $categoryId); 
  });
})->get();

I know how to do with with SQL, but I can't work out how to translate that into Eloquent, and I really need the data to be in Eloquent models

SELECT * FROM `posts`
INNER JOIN `category_post` ON `posts`.`id` = `category_post`.`post_id` 
WHERE `category_post`.`category_id` IN( SELECT `id` FROM categories WHERE parent_id = :category_id)
AND `status` = 'published' 
ORDER BY `category_post`.`sort_order` DESC, `search_rank` DESC, `published_at` DESC, `title` ASC;

LaryAI

2 years ago

Best Answer

Level 58

To achieve the desired functionality using Eloquent in Laravel, you can utilize eager loading with constraints and join clauses to maintain the sort order from the pivot table. Here's how you can modify your Eloquent query to include the sort order from the category_post pivot table while fetching posts assigned to subcategories of a given category.

use App\Models\Post;
use Illuminate\Support\Facades\DB;

// Assuming $categoryId is the ID of the main category
$posts = Post::query()
    ->select('posts.*')
    ->join('category_post', 'posts.id', '=', 'category_post.post_id')
    ->join('categories as sub_categories', 'category_post.category_id', '=', 'sub_categories.id')
    ->where('sub_categories.parent_id', $categoryId)
    ->where('posts.status', 'published')
    ->orderBy('category_post.sort_order', 'DESC')
    ->orderBy('posts.search_rank', 'DESC')
    ->orderBy('posts.published_at', 'DESC')
    ->orderBy('posts.title', 'ASC')
    ->get();

// The posts are now loaded in the correct order, and are Eloquent models.

Explanation:

Selecting Posts: The select('posts.*') ensures that only the columns from the posts table are included in the final result set, avoiding any ambiguity in column names when joining tables.
Join Operations:
- The first join connects posts with the category_post pivot table on the post ID.
- The second join connects the category_post table to the categories table (aliased as sub_categories) on the category ID.
Filtering:
- The where('sub_categories.parent_id', $categoryId) clause filters categories to include only those that are subcategories of the specified parent category.
- The where('posts.status', 'published') ensures that only posts with a status of 'published' are included.
Ordering: The orderBy clauses ensure that the results are ordered first by the sort_order in the pivot table, then by search_rank, published_at, and title of the posts.

This approach should give you the desired posts, correctly ordered, and as Eloquent models, allowing you to utilize all Eloquent features on the results.

1 like

dan1234827

2 years ago

Level 4

That's useful, shame the Laravel docs don't mention that you can access the QueryBuilder on an Eloquent model like that.

Please or to participate in this conversation.