CODEWITHSUNDEEP
phplaraveleloquent
Cecily Miller
Cecily Miller

Reputation: 390

Laravel - Eloquent - How to sum related data and query agaist it?

I'm looking for help with database queries, not collection solution

I have the following models:

User hasMany orders, and orders belongsToMany products

I'm in a place where I would need to query users and select all sold products, meaning the sum of all products quantities that are attached to the orders.

quantity value is stored in the order_product pivot table.

Table name: users, orders, products & order_product

Ideally, I would like to make queries like: select all users that have sold at least 100 products, for example.

DB::raw() & selectRaw is most likely the way to go, I think(?), but I'm not sure about the syntax and how to actually make the query, with and without where clause.

Thanks a lot in advance, this has bothered me for a while

Database Schemas

Schema::create('users', function (Blueprint $table) {
    $table->bigIncrements('id');
});

Schema::create('orders', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->unsignedBigInteger('user_id')->index();
});

Schema::create('order_product', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->unsignedBigInteger('order_id')->index();
    $table->unsignedBigInteger('product_id')->index();
    $table->integer('quantity')->unsigned()->default(1);
});
Schema::create('products', function (Blueprint $table) {
    $table->bigIncrements('id');
});

UPDATE This far I have come:

\App\User::addSelect([
    'sold_products_count' => \App\Order::whereColumn('user_id', 'users.id')
                            ->join('order_product', 'orders.id', '=', 'order_product.order_id')
                            ->select(DB::raw('sum(order_product.quantity) as qty')),
])->where('users.sold_products_count', '>=', 100);

HOWEVER, the last statement where('users.sold_products_count', '>=', 100) throws error, cuz there's no sold_products_count column.

So I think I'm on the right track, but how I can use the new sum column in where clause?

Can I use addSelect, or do I have to use something else?

Upvotes: 0

Views: 324

Answers (2)

Aashish gaba
Aashish gaba

Reputation: 1776

DB::table('users')
    ->join('orders', 'orders.user_id', '=', 'users.id')
    ->join('order_product', 'orders.id', '=', 'order_product.order_id')
    ->select('users.*', DB::raw('sum(order_product.quantity) as qty'))
    ->having('qty', '>=', 100)
    ->get();

Upvotes: 0

Cecily Miller
Cecily Miller

Reputation: 390

Finally, I solved this

Here's the answer:

\App\User::addSelect([
    'sold_products_count' => \App\Order::whereColumn('user_id', 'users.id')
                            ->join('order_product', 'orders.id', '=', 'order_product.order_id')
                            ->select(DB::raw('sum(order_product.quantity) as qty')),
])->having('sold_products_count', '>=', 100);

The idea is to first count the sum via addSelect and then we can query against the value using having, neat

Upvotes: 1

Related Questions