I'm working in a Laravel 9 project and need to show only records where the deeply nested relationship has records, in my case, tiers.

My relationship right now is still returning me pingtree_entries even though tiers are empty, what am I missing?

Here's my top level query:

$pingtree = Pingtree::where('company_id', $company_id)
                    ->where('id', $id)
                    ->has('pingtree_entries.tiers')
                    ->with('pingtree_entries.tiers')
                    ->first();

This should be saying something like:

Get me my Pingtree by company ID and ID with my tiers associated to the pingtree_entries for the Pingtrees where there is more than 0 tiers.

My Pingtree model defines:

/**
 * Get the pingtrees that the model has.
 */
public function pingtree_entries()
{
    return $this->hasMany(PingtreeEntry::class);
}

My PingtreeEntry model defines:

/**
 * Get the buyer tier that the model has.
 */
public function tiers()
{
    return $this->hasMany(BuyerTier::class, 'id', 'buyer_tier_id');
}

This is outputting the following via Postman:

{
    "model": {
        "id": 1,
        "user_id": 1,
        "company_id": 1,
        "pick_chance": 4,
        "name": "omnis iusto consequatur",
        "description": "Hic nihil suscipit error.",
        "is_enabled": false,
        "created_at": "2023-01-27T14:15:26.000000Z",
        "updated_at": "2023-01-27T14:15:26.000000Z",
        "deleted_at": null,
        "is_deleting": false,
        "pingtree_entries": [
            {
                "id": 1,
                "user_id": 1,
                "company_id": 1,
                "buyer_id": 2,
                "buyer_tier_id": 4,
                "pingtree_id": 1,
                "pingtree_group_id": null,
                "processing_order": 1,
                "is_enabled": true,
                "created_at": "2023-01-27T14:15:26.000000Z",
                "updated_at": "2023-01-27T14:15:26.000000Z",
                "deleted_at": null,
                "tiers": [
                    {
                        "id": 4,
                        "user_id": 1,
                        "company_id": 1,
                        "buyer_id": 2,
                        "country_id": 2,
                        "product_id": 3,
                        "name": "dignissimos voluptas et",
                        "description": "Dolore tempora et maxime nam.",
                        "processing_class": "et",
                        "is_default": false,
                        "is_enabled": false,
                        "created_at": "2023-01-27T14:15:25.000000Z",
                        "updated_at": "2023-01-27T14:15:25.000000Z",
                        "deleted_at": null,
                        "is_deleting": false
                    }
                ]
            },
            {
                "id": 3,
                "user_id": 1,
                "company_id": 1,
                "buyer_id": null,
                "buyer_tier_id": null,
                "pingtree_id": 1,
                "pingtree_group_id": 1,
                "processing_order": 1,
                "is_enabled": false,
                "created_at": "2023-01-27T14:15:26.000000Z",
                "updated_at": "2023-01-27T14:15:26.000000Z",
                "deleted_at": null,
                "tiers": []
            }
        ]
    }
}

Note that the last PingtreeEntry has no tiers. So I don't want to show the whole PingtreeEntry model at all.

Attempted with whereHas

$pingtree = Pingtree::where('company_id', $company_id)
                    ->where('id', $id)
                    ->whereHas('pingtree_entries.tiers')
                    ->with('pingtree_entries.tiers.buyer')
                    ->first();

CodePudding user response：

use :

$pingtree = Pingtree::where('company_id', $company_id)
        ->where('id', $id)
        ->with([
            'pingtree_entries' => fn($q) => $q->has('tiers'),
            'pingtree_entries.tiers',
        ])
        ->first();