Using custom query scope in an eloquent subquery laravel

Dmytro Tus

Full Stack Web developer

In the video below, I will show you, how to use subqueries in Laravel application. Why do we need to this? The main reason - perfomance.

To be honest, I have seen this approach for the first time on the Laracon, when Johnatan Reinink showed all beauty of custom queries in Eloquent. More about dynamic relationships you can read in his blog https://reinink.ca/articles/dynamic-relationships-in-laravel-using-subqueries

But here I will show my approach, and how I am using this in some specific cases.

So let's get started.

First of all let's create tree tables in our database.

• Table for products
• Table for gallery
• Table for images

So let's create our products table:

class CreateProductsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('products', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->integet('gallery_id');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('products');
    }
}

This table have only product name and the id to the gallery. 

Next, let's create the images table 

class CreateImagesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('images', function (Blueprint $table) {
            $table->id();
            $table->string('path');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('images');
    }

And the last migration, we will create the table which called gallery. This page will take group the images from the images table and we can apply these images to some specific products, and also take an order for the images.

class CreateGalleriesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('galleries', function (Blueprint $table) {
            $table->id();
            $table->integer('product_id');
            $table->json('images_ids');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('galleries');
    }

 

Then, let's make relation between products and galleries

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\HasOne;

class Product extends Model
{
    public function gallery(): HasOne
    {
        return $this->hasOne(Gallery::class);
    }
}

 

Let's create 3 random images and add it into the database

 

class ImagesController extends Controller
{
    public function index()
    {   
        $images = ['one.jpg', 'two.jpg', 'three.jpg'];
        foreach($images as $image) {
            Image::create([ 'path' => $image ]) ;
         }
    }
}

 

And create one record in gallery table

class GalleryController extends Controller
{
    public function index()
    {
        Gallery::create([
            'product_id' => 1,
            'images_ids' => [1,2,3]
        ]);
}

 

And of course let's create our first product

 

class ProductController extends Controller
{
    public function index()
    {
        Product::create([
            'name' => 'My first product'
        ]);
}

 

Now we have our database and records set, so let's look how the gallery is related to our product and what it shows to us.

class ProductController extends Controller
{
    public function showGalleryOfProduct()
    {
        $product = Product::first();

        dd($product->gallery);
}

/// in dd we will see
App\Models\Gallery
"attributes" => [
     "id" => 1
    "product_id" => 1
    "images_ids" => "[1,2,3]"
]

 

I know that somebody will say that the DB structure is a little bit weird, and we can make many-to-many relations.

I will answer:

• Yes, making many to many relations, without Gallery table can be better approach in this case but I've made this structure because I want to show the benefits and power of Subqueries in Eloquent.

So let's keep out models as it is now, and let's get first image from the product.

 

Let's get the first image path related to our product. Like this 

class Gallery extends Model
{
    use HasFactory;

    protected $casts = [
        'images_ids' => 'array'
    ];

    public function images()
    {
        return Image::findMany($this->images_ids);
    }
}

And then we can retrieve what we want:

class MainController extends Controller
{
    public function index()
    {   
        $product = Product::first();

        dd($product->gallery->images()->first()->path);
}

 

And we received the path of the first product image. We need to notice that we made two requests into the database for getting this image path.

• First request was when we took all ids of images from the database
• Second request was when we took the image by the id

 

Here custom querries comes to play

Let's add to the product model this method:

class Product extends Model
{
     public function scopeWithMainImage($query)
    {
        $query->addSubSelect('main_image', function ($query) {
            $query->select('images.path')
                ->from('galleries')
                ->join(
                    'images',
                    'images.id',
                    '=',
                    DB::raw("JSON_EXTRACT(images_ids, '$[0]')")
                )
                ->whereColumn('product_id', 'products.id')
                ->limit(1);
        });
    }
}

 

Also let's register our addSubSelect macros in AppServiceProvider

class AppServiceProvider extends ServiceProvider
{

    public function boot()
    {

        Builder::macro('addSubSelect', function ($column, $query) {
            if (is_null($this->columns)) {
                $this->select($this->from . '.*');
            }
            return $this->selectSub($query, $column);
        });
    }

 

 

And now, when we look into our controller and make this: 

class MainController extends Controller
{
    public function index()
    {   
        $product = Product::withMainImage()->first();
        dd($product);
    }
}

//result will be
App\Models\Products\Product
     "attributes" => [
          "id" => 1,
           "name" => "My first product"
           "main_image" => "one.jpg",
      ]

 

So it this post I wanted to say, that in some cases custom query scope is very useful. It helps us to decrease the memory in our system, and our application will work so much faster.

---