How to retrieve a model with a filter on one of it's hasMany relationships?

Question

I'm trying to get a model and also add filter to one of it's hasMany relationships.

Let's say I have two models. One of is Location and other one is the LocationAnalytic.

Location model hasMany LocationAnalytic class. So when I want to retrieve a Location by Id, it returns all of the Location data and also returns a LocationAnalytic data as array. In my case a Location model can be related to thousands of LocationAnalytic model.

My need is to get the Location model but also get only related LocationAnalytic data that has filtered by the given filters. By this way I will get the same Location data and also get only couple of LocationAnalytic data with it in the same response.

It is similar to whereHas directive but it will not check for existence, it will only filter relationship of a model.

My Query will be like the following;

query($id:Int!) {
    location (id: $id) {
        id
        parent_id
        name
        admin_level
        country
        analytic {
            id
            category_id
            total_listings_count
            new_listings_count
            expired_listings_count
            created_at
        }
    }
}

And my filters will something like the following

{
  "location_idid": 100075,
  "analytics": {
      "AND": [
          { "column": "CATEGORY_ID", "operator": "EQ", "value": 390005 }
      ]
  }
}

did the answer below work for you ? if so, please mark it as The answer to close your question — N69S, Aug 18 '21 at 07:59

N69S · Answer 1 · 2021-08-17T23:04:51.367

2

Use the second parameter of the with() method to filter a relation

$locationId;

$filter = ['field' => 'category_id', 'operator' => '=', 'value' => 390005];

$location = Location::with('locationAnalytics', function($locationAnQuery) use ($filter) {
    $locationAnQuery->where($filter['field'], $filter['operator'], $filter['value']);
})->find($locationId);

edited Aug 17 '21 at 23:04

answered Aug 17 '21 at 17:33

N69S

16,110
3
22
36

Thanks for the idea, it gives a good point to start with but I was looking for an answer in graphql and lighthouse package. – Can Atuf Kansu Aug 18 '21 at 10:44

score -1 · Answer 2 · answered Aug 18 '21 at 10:54

For now best solution I found is;

By using @method directive, I was able to create a method that gets all the filters and returns result.

https://lighthouse-php.com/master/api-reference/directives.html#method

I added a new field named filteredAnalytic to my Location type

type Location {
    id: Int
    filteredAnalytic(category_id: Int, tenure_id: Int, type_group_id: Int, contact_type_id: Int, source_id: Int, age_group_id: Int, room_count: Int): LocationAnalytic @method
}

I have added a new method with filteredAnalytic name to Location model

public function filteredAnalytic($category_id, $tenure_id, $type_group_id, $contact_type_id, $source_id, $age_group_id, $room_count)
    {
        return $this->hasMany(LocationAnalytic::class)
            ->where('category_id',$category_id)
            ->where('tenure_id', $tenure_id)
            ->where('type_group_id', $type_group_id)
            ->where('contact_type_id', $contact_type_id)
            ->where('source_id', $source_id)
            ->where('age_group_id', $age_group_id)
            ->where('room_count', $room_count)
            ->first();
    }

By this I can create a query like the following and able to pass filter to a model relation.

query($coordinates:CoordinatesInput, $category_id:Int, $tenure_id:Int, $type_group_id:Int, $contact_type_id:Int, $source_id:Int, $age_group_id:Int, $room_count:Int) {
    locations (coordinates: $coordinates) {
        id
        parent_id
        name
        admin_level
        country
        filteredAnalytic (category_id: $category_id, tenure_id: $tenure_id, type_group_id: $type_group_id, contact_type_id:$contact_type_id, source_id:$source_id, age_group_id:$age_group_id, room_count:$room_count) {
            id
            location_id
            created_at
        }
    }
}

with filters,

{
   "coordinates" : {
      "lat": 41.671,
      "lng": 27.9714
  },
  "category_id": 30003,
  "tenure_id": 10001,
  "type_group_id": 310001,
  "contact_type_id": 110002,
  "source_id": 40002,
  "age_group_id": 300001,
  "room_count": null
}

How to retrieve a model with a filter on one of it's hasMany relationships?

2 Answers2