Argthtjtr

I'm trying to get all the data from the parent that only has a child. Please see my code below.

$customers = Customer::with(['items' => function($query){

            return $query->where('status', 2);

        }])->get();



        dd($customers);

But the code above returns all the customer. By the way, I'm using laravel 4.2.

Items Table:

Customer Table:

with() is for eager loading. That basically means, along the main model, Laravel will preload the relationship(s) you specify. This is especially helpful if you have a collection of models and you want to load a relation for all of them. Because with eager loading you run only one additional DB query instead of one for every model in the collection.

has() is to filter the selecting model based on a relationship. So it acts very similarly to a normal WHERE condition. If you just use has('relation') that means you only want to get the models that have at least one related model in this relation.

e.g :

$users = Customer::has('items')->get();

// only Customer that have at least one item are contained in the collection

whereHas() works basically the same as has() but allows you to specify additional filters for the related model to check.

e.g

$users = Customer::whereHas('items', function($q){

    $q->where('status', 2);

})->get();

// only customer that have item status 2

Adding group by to calculating sum
this is another example from my code :

Customer::select(['customer.name', DB::raw('sum(sale.amount_to_pay) AS total_amount'), 'customer.id'])

            ->where('customer.store_id', User::storeId())

            ->join('sale', 'sale.customer_id', '=', 'customer.id')

            ->groupBy('customer.id', 'customer.name')

            ->orderBy('total_amount', 'desc')

            ->take($i)

            ->get()

in your case :

Customer::select(['customer_id', DB::raw('sum(quantity) AS total')])

            ->whereHas('items', function ($q) {

                $q->where('status', 2);

            })

            ->groupBy('customer_id')

            ->get();

whereHas() allow you to filter data or query for the related model in your case
those customer that have items and it status is 2

afetr getting data we are perform ->groupBy('customer_id')

The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.

select(['customer_id', DB::raw('sum(quantity) AS total')]) this will select customer id and calculate the sum of quantity column

You should use whereHas not with to check child existence.

$customers = Customer::whereHas('items', function($query){

    return $query->where('status', 2);

})->get();



dd($customers);

I assume you already defined proper relationship between Customer and Item.

You should try this:

$customers = Customer::whereHas('items', function($query){

     $query->where('status', 2);

})->get();



dd($customers);

        Customer::select(['items.customer_id',DB::raw('count(items.id) AS total_qty')])

        ->join('items', 'items.user_id', '=', 'customer.customer_id')

        ->groupBy('items.customer_id')

        ->havingRaw('total_qty > 2')

        ->get();

OR

$data=DB::select("select `items`.`customer_id`, count(items.id) AS total_qty

from `customers`

inner join `items`

on `items`.`customer_id` = `customers`.`customer_id`

group by `items`.`customer_id` having total_qty >= 2");

correct table name and column name.