CODEWITHSUNDEEP
phplaravel
Blues Clues
Blues Clues

Reputation: 1848

How to get parents data that only has a child data in laravel

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: enter image description here

Customer Table: enter image description here

Upvotes: 2

Views: 7175

Answers (4)

Dhruv Raval
Dhruv Raval

Reputation: 1583

        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.

Upvotes: 0

Dhruv Raval
Dhruv Raval

Reputation: 1583

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

Upvotes: 8

user10186369
user10186369

Reputation:

You should try this:

$customers = Customer::whereHas('items', function($query){
     $query->where('status', 2);
})->get();

dd($customers);

Upvotes: 2

Ijas Ameenudeen
Ijas Ameenudeen

Reputation: 9259

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.

Upvotes: 3

Related Questions