Advanced query in laravel 5.5

Advanced query in laravel 5.5


A while back I was working on client website where he needed to have dynamic posts in his homepage by choosing category. The logic was simple: SELECT CATEGORY -> SHOW POSTS IN THAT CATEGORY SUBCATEGORIES.

But to make that simple logic works, that was something :) today I’m going to share my experience with you and tell you how to that.

I might say if you publish your posts directly under your categories you might not need this tutorial or at least part of it, but if you publish your posts under subcategories you definitely need to learn this in case you need to show posts by category.

Let’s get start:


  1. Posts table
  2. Categories table
  3. Subcategories table
  4. Designs table (name is optional, name it what you like)
  5. Remember in this table you have to save category ID. Make column name it column name it “category_id”)

1. Create your posts table:

Php artisan make:model Post –m

2. Create your categories table:

Php artisan make:model Category –m

3. Create your subcategories table:

Php artisan make:model Subcategory –m

4. Create your design table:

Php artisan make:model Design –m

-m will make you migration schema along with creating model.

Note: I am not going through process of making CRUD system, for now just add some data in your database from your database management such as PhpMyAdmin or laravel tinker.

Now let’s set relations between posts,categories and subcategories.

1. Open Post.php (posts model) Add these lines and save it:

public function category(){
     return $this->belongsTo(Category::class);
public function subcategory(){
     return $this->belongsTo(Subcategory::class);

2. Open Category.php (categories model) Add these lines and save it:

public function posts(){
     return $this->hasMany(Post::class);
public function subcategories(){
     return $this->hasMany(Subcategory::class);

3. Open Subcategory.php (subcategories model) Add these lines and save it:

public function category(){
     return $this->belongsTo(Category::class, 'category_id');
public function posts(){
     return $this->hasMany(Post::class);

Now that our database is ready set is time to make query and show the result in front-end.

  • Open your AppServiceProvider.php located in AppProviders.
  • Add your models such as use AppPost; use AppCategory; use AppSubcategory;
  • Create composer view function.

This is what composer view looks like:

View::composer('welcome', function ($view) {
            $category = DB::table('subcategories')
            ->join('designs', 'designs.category_id', '=', 'subcategories.category_id')
            ->join('posts', function ($join) {
              $join->on('', '=', 'posts.subcategory_id');
            $view->with('category', $category);

Now we need to group our result in case to show each posts in their own category panel, but somehow I wasn’t able to get groupby(‘category_id’) in this function so I made it in my view(blade) which is next step.

Open your index page view in my case I used welcome.blade.php as you see in first line of my composer view function.

Next add these code wherever you like to show your posts.

@foreach($category->groupby('category_id') as $key => $value)
  @foreach($value as $ki)
       {{$ki->title}}, Cat: {{$key}}, Sub: {{$ki->subcategory_id}}


Now refresh your page and see the result.

- Last updated 2 years ago

Be the first to leave a comment.

You must login to leave a comment