WhereHas + WhereIn + WhereNull not working?

I'm working on a SaaS project. I have certain features that are only available to certain customers via roles. I want to make sure these products do not show up for purchase to users who are not eligible and don't have the correct roles. When needed_role is null, it means the feature is available to everyone.

I attempted something like this;

return $query->whereHas('features', function ($query) {
	return $query->whereNull('needed_role')->orWhereIn('needed_role', Auth::user()->roles->toArray());
});

However, this causes the query to not work and basically causes it just spit out all products. If I remove the "whereNull" part and remove the "or", it works for the specific roles/customers as intended, but obviously then doesn't show the public features to everyone.

I've been trying to deal with this logic issue for a few hours at this point and it just doesn't make any sense to me, because the logic seems to make sense. We want to retrieve all products that have a relationship of features where needed_role is either null, or contained within their roles relationship. This seems to make perfect sense to me yet it doesn't give the expected results.

Hoping someone can perhaps explain where I'm going wrong here. Thanks.

jlrdw

4 years ago

Level 75

Do a ->toSql() to see what query is generated, I think the orWhereIn isn't being hit if whereNull is true first.

1 like

SilenceBringer

4 years ago

Level 55

@burano I think the problem here

Auth::user()->roles->toArray()

I think it returns array of arrays with role details, while actually you need to get role ids (or names?)

try to do the following

dd(Auth::user()->roles->map(fn($item) => $item->id)->toArray());
return $query->whereHas('features', function ($query) {
// ...

this will convert your roles array to array of values (just replace ->id with the actual param you need)

1 like

Please or to participate in this conversation.