Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

petersowah's avatar

Query Laravel nested relationship [Laravel 6.x]

I have a page in my Laravel app where I am fetching categories and eager loading events in that category. It works fine. What I want to do now is to fetch the categories alright but this time, fetch events based on a region/location selected by the user. These are the models I am working with;

1.Region Class

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Region extends Model
{
    /**
     * Get events within region
     *
     * @return \Illuminate\Database\Eloquent\Relations\HasMany
     */

    public function events()
    {
        return $this->hasMany(Event::class);
    }
}

  1. Category Class

     <?php

 namespace App\Models;

 use Illuminate\Database\Eloquent\Model;

 class Category extends Model
 {
     /**
      * The attributes that are mass assignable.
      *
      * @var array
      */
     protected $fillable = [
         'category_name', 'description', 'type', 'slug'
     ];

     /**
      * Get all events that belong to a category
      *
      * @return Illuminate\Database\Eloquent\Relations\HasMany
      */
     public function events()
     {
         return $this->hasMany(Event::class)->where('start_date', '>=', today())->orderBy('start_date', 'asc');
     }
 }

3.Event

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Event extends Model
{
     /**
     * Get an event's category
     *
     * @return Illuminate\Database\Eloquent\Relations\BelongsTo;
     */
    public function category()
    {
        return $this->belongsTo(Category::class);
    }

    /**
     * Get region of event
     *
     * @return [type] [description]
     */
    public function region()
    {
        return $this->belongsTo(Region::class);
    }
}

This is the query that returns categories with events;

if (session()->has('region')) {
            $region_name = session()->get('region');

            $region = Region::where('region_name', $region_name)->firstOrFail();

            $categories = Category::withCount('events')->with('events')->whereHas('events')->orderBy('events_count', 'desc')->get()->take(5);
}

Summary: I want to fetch top 5 categories with events in user's selected location.

Thanks in advance for your help.

0 likes
10 replies
Nakov's avatar

@petersowah something like this:

$region = Region::where('region_name', $region_name)->firstOrFail();
$categories = Category::withCount('events')
    ->with('events')
    ->whereHas('events', function($query) use ($region) {
        $query->where('region_id', $region->id);
    })
    ->orderBy('events_count', 'desc')
    ->get()->take(5);

This will filter the events based on the region. I assume you have region_id in your events table.

EDIT And you might need to first ->take(5)->get(); in this order. But try it out :)

1 like
petersowah's avatar

@nakov I tried this and it didn't work. I do have a region_id column on my events table.

petersowah's avatar

@nakov In my blade view, this is how I'm rendering the data;

@foreach($category->events as $event)
    ....
 @endforeach

I realise it's using the relationship and not the eager loaded collection. I think that's where the problem is.

Nakov's avatar

@petersowah well, you need to pass the categories to the view, and then use this:

@foreach ($categories as $category)

       @foreach($category->events as $event) .... @endforeach

@endforeach
petersowah's avatar

@nakov this is what I have in my controller;

public function index() { if (session()->has('region')) { $region_name = session()->get('region');

        $region = Region::where('region_name', $region_name)->firstOrFail();

        // $categories = Category::withCount('events')->with('events')->whereHas('events')->orderBy('events_count', 'desc')->get()->take(5);
        $categories = Category::withCount('events')
                                ->with('events')
                                ->whereHas('events', function ($query) use ($region) {
                                    $query->where('region_id', $region->id);
                                })
                                ->orderBy('events_count', 'desc')
                                ->take(5)
                                ->get();

        $weekEvents = Event::week()->where('region_id', $region->id)->get()->take(30);
    } else {
        $categories = Category::withCount('events')->whereHas('events')->orderBy('events_count', 'desc')->get()->take(5);

        $weekEvents = Event::week()->get()->take(30);
    }

    return view('pages.index', compact(['categories', 'weekEvents', 'region']));
}

and in my view;

  @foreach($categories as $category)
    @foreach ($category->events as $event)
    ...
    @endforeach
  @endforeach

and this is how the relationship between Category and Event is defined;

Category.php

 /**
     * Get all events that belong to a category
     *
     * @return Illuminate\Database\Eloquent\Relations\HasMany
     */
    public function events()
    {
        return $this->hasMany(Event::class)->where('start_date', '>=', today())->orderBy('start_date', 'asc');
    }

Event.php

    /**
     * Get an event's category
     *
     * @return Illuminate\Database\Eloquent\Relations\BelongsTo;
     */
    public function category()
    {
        return $this->belongsTo(Category::class);
    }
Nakov's avatar

@petersowah okay, you need to debug :) I don't know if this condition if (session()->has('region')) is ever true in your code. If it is not then you won't get the filtered results :)

petersowah's avatar

@nakov Yeah, it returns true when a location/region is selected/set, false when not.

petersowah's avatar

This is what I'm getting when I run the query you suggested.

App\Models\Event {#3518
               id: 163,
               event_name: "Quia quod molestiae sint.",
               slug: "quia-quod-molestiae-sint",
               rate: 33,
               description: "Numquam molestiae labore repudiandae officiis. Tempora consequatur eaque quod inventore tenetur. Officia velit aspernatur ut qui ut quis dolore.",
               category_id: 14,
               recurring: 1,
               lat: "-79.286661",
               long: "106.811532",
               location_description: "Corrupti voluptatem reiciendis qui expedita aspernatur. Quibusdam dolore accusamus dicta.",
               region_id: 6,
               ghana_post_gps: "GZ-200-8686",
               is_featured: 0,
               eventable_id: 18,
               eventable_type: "App\Models\User",
               start_date: "2019-12-24 12:41:35",
               end_date: "2019-12-25 12:41:35",
               created_at: "2019-12-17 12:41:36",
               updated_at: "2019-12-17 12:41:36",
             },
             App\Models\Event {#3522
               id: 170,
               event_name: "Repudiandae exercitationem numquam eligendi distinctio.",
               slug: "repudiandae-exercitationem-numquam-eligendi-distinctio",
               rate: 18,
               description: "Deleniti rerum non consequatur rerum qui qui. A magni est tempora ut rerum eum. Est quis tenetur officiis facere ea. Laboriosam aut non illum sed dolores aut id. Porro unde repellat ratione eos consequatur temporibus ut.",
               category_id: 14,
               recurring: 0,
               lat: "63.874091",
               long: "90.149218",
               location_description: "Non numquam sunt voluptatem veritatis facilis sequi laudantium. Molestiae unde quaerat debitis hic in quis. Soluta cumque hic eius recusandae animi.",
               region_id: 14,
               ghana_post_gps: "GZ-200-8686",
               is_featured: 1,
               eventable_id: 8,
               eventable_type: "App\Models\Agency",
               start_date: "2019-12-24 12:41:35",
               end_date: "2019-12-25 12:41:35",
               created_at: "2019-12-17 12:41:36",
               updated_at: "2019-12-17 12:41:36",
             },
             App\Models\Event {#3550
               id: 212,
               event_name: "Nisi molestias atque expedita occaecati aut.",
               slug: "nisi-molestias-atque-expedita-occaecati-aut",
               rate: 80,
               description: "Dolores porro dolorem et. Neque facilis dolor dolorem qui nihil unde. Dolores consequatur consequatur facilis ullam consectetur itaque.",
               category_id: 14,
               recurring: 0,
               lat: "58.904839",
               long: "-53.725352",
               location_description: "Aut sit qui voluptatem impedit et eligendi. Minima praesentium deserunt amet aut commodi vero dolores.",
               region_id: 16,
               ghana_post_gps: "GZ-200-8686",
               is_featured: 1,
               eventable_id: 6,
               eventable_type: "App\Models\User",
               start_date: "2019-12-24 12:41:50",
               end_date: "2019-12-25 12:41:50",
               created_at: "2019-12-17 12:41:50",
               updated_at: "2019-12-17 12:41:50",
             },
             App\Models\Event {#3552
               id: 218,
               event_name: "Pariatur qui eaque numquam incidunt.",
               slug: "pariatur-qui-eaque-numquam-incidunt",
               rate: 37,
               description: "Tenetur nihil odit optio id facilis enim et. A est itaque qui ut voluptates laboriosam. Deleniti est molestiae eveniet dolor dignissimos accusamus. Ipsum quia asperiores non quis harum et.",
               category_id: 14,
               recurring: 1,
               lat: "64.207187",
               long: "-103.00589",
               location_description: "Soluta repellendus et laudantium. Rem libero et ipsa officiis quos et. Eum unde minus est facere illo sint iste. Explicabo porro inventore perferendis quae. Aliquid facilis qui fugiat eaque.",
               region_id: 2,
               ghana_post_gps: "GZ-200-8686",
               is_featured: 1,
               eventable_id: 5,
               eventable_type: "App\Models\User",
               start_date: "2019-12-24 12:41:50",
               end_date: "2019-12-25 12:41:50",
               created_at: "2019-12-17 12:41:51",
               updated_at: "2019-12-17 12:41:51",
             },

You realise, the region_id is different for the various events, which shouldn't be the case.

petersowah's avatar
petersowah
OP
6 years ago
Best Answer
Level 10

This is what I had to get it working;

        $categories = Category::with(['events' => function ($query) use ($region) {
            $query->where('region_id', $region->id);
        }])->take(5)->get();

Please or to participate in this conversation.