I’m working in a Laravel 10 project and need to just return tier’s where each tier’s options matches some criteria. Right now I need to do it based on a column called type. For reference, I only have 2 entries in my table out of around 700 that should match "private", but when I use a whereHas and set the where to pull back records that match private, I get everything returned.
If I change "private" to something that doesn’t exist, I get nothing.
What am I missing here to just return models that match a set of criteria and then don’t load tier options and tiers where options don’t match?
$buyers = Buyer::with([
'tiers.options'
])->whereHas('tiers.options', function ($query) {
$query->where([
['type', 'private']
]);
})->get();
Here’s an example of what I mean by options being returned with an empty array:
{
"model": null,
"metadata": {
"model_count": 1,
"model_data": [
{
"id": 3,
"user_id": 1,
"company_id": 1,
"name": "FakeBuyer",
"description": "A collection of dummy and faker buyer tiers.",
"is_default": false,
"is_enabled": true,
"last_used_at": "2023-04-18T09:59:50.000000Z",
"created_at": "2023-04-17T13:41:04.000000Z",
"updated_at": "2023-04-18T09:59:51.000000Z",
"deleted_at": null,
"is_deleting": false,
"tiers": [
{
"id": 69,
"user_id": 1,
"company_id": 1,
"buyer_id": 3,
"country_id": 1,
"product_id": 1,
"name": "Fake-accept 62",
"description": "Fake accept with delay",
"processing_class": "FakeBuyer",
"is_default": false,
"is_enabled": true,
"last_used_at": null,
"created_at": "2023-04-17T13:41:04.000000Z",
"updated_at": "2023-04-17T13:41:04.000000Z",
"deleted_at": null,
"is_deleting": false,
"options": []
},
{
"id": 70,
"user_id": 1,
"company_id": 1,
"buyer_id": 3,
"country_id": 1,
"product_id": 1,
"name": "Fake-accept 63",
"description": "Fake accept with delay",
"processing_class": "FakeBuyer",
"is_default": false,
"is_enabled": true,
"last_used_at": null,
"created_at": "2023-04-17T13:41:05.000000Z",
"updated_at": "2023-04-17T13:41:05.000000Z",
"deleted_at": null,
"is_deleting": false,
"options": [
{
"id": 764,
"user_id": 1,
"company_id": 1,
"buyer_tier_id": 70,
"name": "min_price",
"value": 2.5,
"description": null,
"cast": "float",
"type": "private",
"is_enabled": true,
"created_at": "2023-04-17T15:13:16.000000Z",
"updated_at": "2023-04-17T15:13:16.000000Z",
"deleted_at": null,
"type_formatted": "Private",
"is_deleting": false
}
]
},
{
"id": 71,
"user_id": 1,
"company_id": 1,
"buyer_id": 3,
"country_id": 1,
"product_id": 1,
"name": "Fake-accept 64",
"description": "Fake accept with delay",
"processing_class": "FakeBuyer",
"is_default": false,
"is_enabled": true,
"last_used_at": null,
"created_at": "2023-04-17T13:41:05.000000Z",
"updated_at": "2023-04-17T13:41:05.000000Z",
"deleted_at": null,
"is_deleting": false,
"options": [
{
"id": 765,
"user_id": 1,
"company_id": 1,
"buyer_tier_id": 71,
"name": "min_price",
"value": 5,
"description": null,
"cast": "float",
"type": "private",
"is_enabled": true,
"created_at": "2023-04-18T10:33:04.000000Z",
"updated_at": "2023-04-18T10:33:04.000000Z",
"deleted_at": null,
"type_formatted": "Private",
"is_deleting": false
}
]
},
{
"id": 72,
"user_id": 1,
"company_id": 1,
"buyer_id": 3,
"country_id": 1,
"product_id": 1,
"name": "Fake-accept 65",
"description": "Fake accept with delay",
"processing_class": "FakeBuyer",
"is_default": false,
"is_enabled": true,
"last_used_at": null,
"created_at": "2023-04-17T13:41:05.000000Z",
"updated_at": "2023-04-17T13:41:05.000000Z",
"deleted_at": null,
"is_deleting": false,
"options": []
}
]
}
]
}
}
You see that the buyer tier "Fake-accept 62" has been retrieved despite it having no options. For the ones that are retrieved, it should match the type of private.
Here’s loads of attempts I’ve done:
$buyers = Buyer::with([
'tiers.options'
])->whereHas('tiers.options', function ($query) {
$query->where([
['type', 'private']
]);
})->get();
$buyers = Buyer::with([
'tiers.options'
])->withWhereHas('tiers.options', function ($query) {
$query->where('type', 'private');
})->get();
$buyers = Buyer::with('tiers.options', function ($query) {
$query->where('type', 'private')
})->get();
$buyers = Buyer::withWhereHas('tiers.options', function ($query) {
$query->where('type', 'private');
})->get();
They all continue to bring back tiers with no options despite the where clause.
2
Answers
try to add
tiersto with and then call the nested relation :Your query means you get buyers who have the type of tiers.options = private and get all tiers.options of those buyers
If you want to get buyers who have the type of tiers.options = private and also just get tiers.options that has type = private, you use withWhereHas: