0

If I have a collection with many levels, are there any tricks to find out what I need to put in my where() to be able to search by it? This is what I have so far, but I get an empty array from it. $contacts->where('contacts.tags.id', $tag->id) I've also tried contacts.tags.tags.id This is what I get if I run dd($contacts)

enter image description here

SeaBass
  • 1,584
  • 4
  • 20
  • 46

1 Answers1

0

You may be looking for Eloquent - Eager Loading With Constraints.

$id = 'Your ID you are searching for';

$contacts= App\Models\Contact::with(['tags' => function ($query) {
    $query->where('id', '=', $id');
}])->get();

show me all the contacts where tags = id. if it's only one you may switch ->get() for ->first() to return the object instead of a collection (array)

Cameron
  • 532
  • 3
  • 9
  • Thanks! I've already eager loaded `tags` so they're in the `tags` relationship as in the picture. I can't filter by tag higher up in the chain. Now I want to run something like `$contacts->where('contacts.tags.id', $tag->id)` to access the tags relationship. It's all there in the array, I just don't know how to reach it, but maybe it's too complicated for using where in a collection. – SeaBass Sep 07 '21 at 01:21
  • After looking at your code again. It will give all contacts, the only thing it filters is the tags relationship, or am I wrong? I want to filter contacts by tag, so maybe a whereHas or something is what I need. – SeaBass Sep 07 '21 at 04:05
  • I think its supposed to only return contacts whose relationship tag id match the id. If it doesn't you can try whereHas (check this thread) https://stackoverflow.com/questions/30231862/laravel-eloquent-has-with-wherehas-what-do-they-mean – Cameron Sep 07 '21 at 13:22
  • Thanks! I’m already eager loading all tags into $contacts and then loop through $contacts several times so I wanted to avoid making more db queries, but if I can’t reach the tag id with where in a collection, maybe there is a whereHas for collections as well. What I really wanted to do was a group by tags, but I didn’t get it to work. My desired end result is a list of tags with respective contacts under each tag. – SeaBass Sep 07 '21 at 15:05
  • if your inverse of tags->contacts is setup, cant you do `$tags = Tag::with('contacts')->find($id);` then you get the tag and the associated contacts with the tag? – Cameron Sep 07 '21 at 15:24
  • That will also give me one query per tag. I may have to start from scratch here and get some groupBy working. I tried this `Contact::with(['tags' => function ($query) { $query->groupBy('tags.title'); }])` but it gives me `SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'tags.title' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by` – SeaBass Sep 07 '21 at 16:17
  • I'm still interested in my initial question, if it's possible to use `where()` on collections to make advanced filters, but I think the issue may have been that I tried to do it on nested arrays. I've restructured my whole code and is now querying a pivot table, which made it a lot easier to use group by, since I only need to search one level down and group by `tag` right away instead of an array of tags. – SeaBass Sep 07 '21 at 22:49