DEV Community

loading...
Cover image for Eloquent queriying - "Grandparents" and beyond

Eloquent queriying - "Grandparents" and beyond

6daniel32
Junior fullstack developer
Updated on ・3 min read

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!

Discussion (0)

Forem Open with the Forem app