DEV Community

Cover image for Eloquent queriying - "Grandparents" and beyond
6daniel32
6daniel32

Posted on • Edited on

3 3

Eloquent queriying - "Grandparents" and beyond

Some warnings before you read my post

  1. This is not a formal post, humor and informal writing will be found in the contents. If you do not like informal posts or you're easily ofended by jokes, I recommend you not to read it.

  2. I'm not a senior/rockstar developer thus, you may not find the optimal solutions nor any Dalai Lama level programming wisdom. What you will find here would be nothing but something that solved me a problem that I didn't easily find on google.

  3. I'm not a native English speaker, you may find typos.

  4. I do not guarantee that this post is not being written under the influence of a psychotropic substance.

The problem

Let's think you have the following tables structure:

Table 1 - Users
id
email (String)
password (String)
name (String)
hair_color {Blonde, Red, Black} (Enum)

Table 2 - Houses
id
user_id (foreignId)
location (String)
description (String)

Table 3 - Furniture
id
house_id (foreignId)
name (String)
description (String)

Table 4 - FunkoPops
id
furniture_id (foreignId)
name (String)
description (String)

And the following relationships in the model files:

User.php

public function houses() {
    return $this->hasMany(House::class, 'user_id');
}
Enter fullscreen mode Exit fullscreen mode

House.php

public function user() {
    return $this->belongsTo(User::class, 'user_id');
}

public function furnitures() {
    return $this->hasMany(Furniture::class, 'house_id');
}
Enter fullscreen mode Exit fullscreen mode

Furniture.php

public function house() {
    return $this->belongsTo(House::class, 'house_id');
}

public function funkoPops() {
    return $this->hasMany(FunkoPop::class, 'furniture_id');
}
Enter fullscreen mode Exit fullscreen mode

FunkoPop.php

public function furniture() {
    return $this->belongsTo(Furniture::class, 'furniture_id');
}
Enter fullscreen mode Exit fullscreen mode

Let's say, you got envolved in a situation in which you wanna get a collection of FunkoPop items from users with the same hair_color as the current loged user and with the email of the owners (😃🔫).

The ugly solution :c

Leaving aside the fact that we normalized so quickly living in a world in which adult people (in some cases over their 40's) buy and collect bobblehead toys, let's jump into what we are doing.

Thankfully, despite you are thinking that all hope is gone and your live as a programmer ends here, now comes our big brother Eloquent to rescue you.

The tools you can use for successfully achieve your goal are, the "whereHas()" and "with()" eloquent methods. You can do it as follows:

$currentUserHairColor = Auth::user()->hair_color;
$funkoPopOwnersSameHairColor = FunkoPop::select('furniture_id')->whereHas(
    'furniture', function($query) use ($currentUserHairColor) {
        $query->select('id', 'house_id')->whereHas(
            'house', function($query) use ($currentUserHairColor) {
                $query->select('id', 'user_id')->whereHas(
                    'user', function($query) use ($currentUserHairColor) {
                       $query->where('hair_color', $currentUserHairColor)
                    }
                );
            }
        );
    }
)->with('furniture', function($query) {
    $query->select('id', 'house_id')->with(
        'house', function($query) {
            $query->select('id', 'user_id')->with(
                'user', function($query) {
                    $query->select('id', 'email');
                }
            );
        }
    );  
});
Enter fullscreen mode Exit fullscreen mode

Note that you must include foreign ID's within "whereHas()" and "with()" eloquent methods and the simple 'id' field in their callback functions for eloquent to find the related models.

The not so ugly solution c:

Ok, I know what you're thinking: Oh no! if I code something like this, my fancy programmers friends are going to know that I haven't read 'Clean Code by Robert Cecil Martin'.

Yeah, I also haven't read it so, we can do what follows for mocking that we know what we're doing and hopefully, not get killed in the next clean code crusade.

To improve the code quality what we can do is to use laravel local scopes:

In FunkoPopController.php

$currentUserHairColor = Auth::user()->hair_color;
$funkoPopOwnersSameHairColor = FunkoPop::ownerSameHairColor(
   $currentUserHairColor
)->withOwnerEmail();
Enter fullscreen mode Exit fullscreen mode

In FunkoPop.php:

public function scopeOwnerSameHairColor($query, $currentUserHairColor) {
   $query->select('furniture_id')->whereHas(
       'furniture', function($query) use ($currentUserHairColor) {
           $query->select('id', 'house_id')->whereHas(
               'house', function($query) use ($currentUserHairColor) {
                  $query->select('id', 'user_id')->whereHas(
                      'user', function($query) use ($currentUserHairColor) {
                         $query->where('hair_color', $currentUserHairColor)
                      }
                  );
               }
           );
       }
    );
}

public function scopeWithOwnerEmail ($query) {
    $query->with('furniture', function($query) {
        $query->select('id', 'house_id')->with(
            'house', function($query) {
               $query->select('id', 'user_id')->with(
                   'user', function($query) {
                       $query->select('id', 'email');
                   }
               );
            }
        );
    });
}
Enter fullscreen mode Exit fullscreen mode

Jesus, I had never thought that I would write something like 'FunkoPopController.php' at any time of my life but, at least I hope that this post could help someone.

See you soon folks!

Heroku

This site is built on Heroku

Join the ranks of developers at Salesforce, Airbase, DEV, and more who deploy their mission critical applications on Heroku. Sign up today and launch your first app!

Get Started

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Explore a sea of insights with this enlightening post, highly esteemed within the nurturing DEV Community. Coders of all stripes are invited to participate and contribute to our shared knowledge.

Expressing gratitude with a simple "thank you" can make a big impact. Leave your thanks in the comments!

On DEV, exchanging ideas smooths our way and strengthens our community bonds. Found this useful? A quick note of thanks to the author can mean a lot.

Okay