Mark Centoni
Back to all posts
Tech

Dynamic filtering by custom fields in Laravel Query Builder

By Mark CentoniApril 26, 2024

Don’t know which fields or how many might be in the filter params? Need AND filtering between fields and OR filtering in possible field values? No problem.

coffee filters

I don’t write PHP or work will Laravel often and have been working mostly on the frontend for the past year, but, you know, sometimes the backend PHP engineer gets sick and you gotta dust off SQL and step in.

The problem

  • The API needs to support filtering a list of entities based on custom fields that are different for every customer
  • Each field applies with an AND condition (between fields)
  • Each possible value within a field applies with an OR condition (within fields)

Setup

The frontend is ready to send an array of fields and values where values could be an array of strings or a single string like:

1const customFields = [
2  {
3    id: 'someUUID',
4    values: [ 'someValue', 'my dog' ],
5  },
6  {
7    id: 'someOtherUUID',
8    values: ['1', '2' ],
9  },
10{
11    id: 'anotherUUID',
12    values: 'random string',
13  },
14]
15
16// which could also look like this, but case converters on UUID object keys
17// can be tricky
18
19const objectCustomFields = {
20  'someUUID': [ 'someValue', 'my dog' ],
21  'someOtherUUID': ['1', '2' ],
22  'anotherUUID': 'random string',
23}

The entity already has an HTTP controller with an index method that looks something like:

1public function index(Request $request): JsonResponse {
2  $entities = QueryBuilder::for(MyEntity::class)
3    ->where('parent_id', $parentId)
4    ->allowedFilters([ // each applied filter is an AND condition
5        AllowedFilter::exact('source', 'source'),
6        AllowedFilter::exact('status', 'status'),
7        // ...etc.
8    ])->jsonPaginate()->appends(request()->query());
9  
10  return (new MyEntityResourceCollection($entities))->response();
11}

Solution 1

We don’t know which fields are coming in or how many, so we’re going to generate a subquery for each field dynamically for a list of entity IDs that fit the conditions and use WHERE IN to see if the current entity is in the list.

STEP1: Write some SQL that would work:

1SELECT * FROM `my_entities` 
2
3WHERE my_entities.parent_id='9b9ed930-5c94-4356-87b9-8c0ec2af7353' 
4AND my_entities.source IN ('web', 'mobile')
5AND my_entities.status IN ('waiting', 'notified')
6AND my_entities.id IN (
7    SELECT custom_field_entity.entity_id FROM custom_field_entity
8    WHERE custom_field_entity.custom_field_id ='9b9ed930-7404-48b7-9d0f-46b034f65f1c' 
9    AND (
10      JSON_CONTAINS(custom_field_entity.custom_field_value, '"someValue"') 
11      OR JSON_CONTAINS(custom_field_entity.custom_field_value, '"my dog"')
12    )
13        
14) 
15AND parties.id IN (
16    SELECT custom_field_party.party_id FROM custom_field_party
17    WHERE custom_field_party.custom_field_id='9b9ed930-7f6f-446e-b7c1-c1d4c830dc54'
18    AND (
19      JSON_CONTAINS(custom_field_party.custom_field_value, '"1"') 
20      OR JSON_CONTAINS(custom_field_party.custom_field_value, '"2"')
21    )
22);

STEP 2: Translate into PHP logic with Laravel Query Builder methods

Laravel Query Builder provides a set of query methods that look somewhat like SQL. We’ll use the AllowedFilter::callback method to create a custom filter query along with function enclosures as parameters where() methods to do custom where logic. The result looks like:

1AllowedFilter::callback('custom_fields', function(\Illuminate\Database\Eloquent\Builder $query, $value) {
2                    
3  $query->where(function($q) use ($value) {
4    // level 1: each field
5    foreach ($value as $k => $v) {
6      $q->whereIn('my_entities.id', function($q1) use ($v) {
7        $values_arr = is_array($v['values']) ? $v['values'] : explode(',', $v['values']);
8        $q1->select('entity_id')->from('custom_field_entity')
9          ->where('custom_field_entity.custom_field_id', '=', $v['id'])
10          ->where(function($q2) use ($values_arr) {
11            // level 2: each value within a field
12            foreach ($values_arr as $str) {
13              $q2->orWhereJsonContains('custom_field_entity.custom_field_value', "$str");
14            }
15          });           
16        });
17    }
18  });
19}),

The parameters for our callback will be:

  • The filter field name from the url parameter filter[custom_fields]
  • A function that takes the query object which an instance of the Query Builder and the value of the filter parameter

1AllowedFilter::callback(
2  'custom_fields', 
3  function(\Illuminate\Database\Eloquent\Builder $query, $value) {}
4);

The first where clause adds an AND to the query instance passed in. We’ll pass it a function enclosure to hold our logic. That enclosure gets its own instance of the query and we’ll pass the value as an additional argument with use ($value).

1$query->where(function($q) use ($value) {

At level one, we’ll loop through each field in the $value array and open the whereIn() for each one.

Like SQL, the whereIn() takes the needle (entity id) and the haystack (possible entity ids), so we’ll create another function enclosure to generate our haystack, give its own query instance and also the custom field data which is the value of the current array key.

1foreach ($value as $k => $v) {
2      $q->whereIn('my_entities.id', function($q1) use ($v) {

We’ll make sure the field value we’re working with is an array if it’s not already and start our subquery, selecting the entity id from the join table where the custom field matches our current field id in the loop.

1$values_arr = is_array($v['values']) ? $v['values'] : explode(',', $v['values']);
2$q1->select('entity_id')->from('custom_field_entity')
3  ->where('custom_field_entity.custom_field_id', '=', $v['id'])

Finally, we’ll open our last where method to add parentheses around our OR conditions for values, loop through each of field values and create an OR condition where the SQL method JSON_CONTAINS would match the value.

1->where(function($q2) use ($values_arr) {
2  // level 2: each value within a field
3  foreach ($values_arr as $str) {
4    $q2->orWhereJsonContains('custom_field_entity.custom_field_value', "$str");
5  }
6});

This is nice and clean, but it’s not very performant because we have one subquery for every custom field and there could be any number of custom fields in the filter.

Solution 2: only 1 subquery

This time we’ll make use of WHERE EXISTS, grouping and aggregates to get N subqueries down to 1.

  • Get the entity id for any custom field that matches our field criteria — the OR condition for values within fields
  • Create a count field to count how many fields match for each entity id
  • Group by entity id to return only one record for each entity id
  • Only return entity records where the count matches the number of fields in our filter — that means there was a match for every field which satisfies our AND condition between fields

1SELECT * FROM `my_entities` 
2
3WHERE my_entities.parent_id='9b9ed930-5c94-4356-87b9-8c0ec2af7353' 
4AND my_entities.source IN ('web', 'mobile')
5AND my_entities.status IN ('waiting', 'notified')
6AND EXISTS (
7  SELECT 
8    COUNT(custom_field_value) as value_count, 
9    `party_id` 
10  FROM `custom_field_entity` 
11  WHERE `custom_field_entity`.`entity_id` = `my_entities`.`id` 
12  AND ((`custom_field_id` = '9b9ed930-7404-48b7-9d0f-46b034f65f1c' AND (JSON_CONTAINS(`custom_field_value`, '\\\"Booth Seating\\\"')) 
13  OR `custom_field_id` = '9b9ed930-7f6f-446e-b7c1-c1d4c830dc54' AND (JSON_CONTAINS(`custom_field_value`, '\\\"1\\\"')))) 
14  GROUP BY `party_id` 
15  HAVING `value_count` = 2
16)

The final PHP:

1AllowedFilter::callback('custom_fields', function(\Illuminate\Database\Eloquent\Builder $query, $value) {
2  $query->whereExists(function($q) use ($value) {
3    $q->selectRaw('count(custom_field_value) as value_count')
4      ->addSelect('entity_id')
5      ->from('custom_field_entity')
6      ->whereColumn('custom_field_entity.entity_id', 'my_entities.id')
7      ->where(function($q) use ($value) {
8        $q->where(function($q1) use ($value) {
9          foreach ($value as $k => $v) {
10            $q1->orWhere('custom_field_id', '=', $v['id'])
11              ->where(function($q2) use ($v) {
12                 $values_arr = is_array($v['values']) ? $v['values'] : explode(',', $v['values']);
13                 foreach ($values_arr as $str) {
14                   $q2->orWhereJsonContains('custom_field_value', "$str");
15                 }
16              });
17          }
18        });
19   })
20   ->groupBy('entity_id')
21   ->having('value_count', '=', count($value));
22  });
23}),

This time our first and only where clause in the main query is whereExists() to which we’ll pass the query instance and our filter value.

Then open the subquery with a selectRaw() which seemed to be the only way to create a count aggregate and give it an alias, though I’d love to learn of another way. Select the entity as well, from the join table using whereColumn() to match the current entity id with the entity id in the join table.

1$query->whereExists(function($q) use ($value) {
2    $q->selectRaw('count(custom_field_value) as value_count')
3      ->addSelect('entity_id')
4      ->from('custom_field_entity')
5      ->whereColumn('custom_field_entity.entity_id', 'my_entities.id')

Now we’re ready to open the logic where any of our fields match the conditions. The output of this would be an OR between fields, but we’ll transform that to an AND by grouping in the last step. The additional where() method creates parentheses for the OR conditions that follow.

1->where(function($q) use ($value) {
2 $q->where(function($q1) use ($value) {

The heart of the query is almost the same as solution 1 except we use an orWhere() in the outer loop to to create the OR between fields.

1foreach ($value as $k => $v) {
2  $q1->orWhere('custom_field_id', '=', $v['id'])
3    ->where(function($q2) use ($v) {
4      $values_arr = is_array($v['values']) ? $v['values'] : explode(',', $v['values']);
5      foreach ($values_arr as $str) {
6        $q2->orWhereJsonContains('custom_field_value', "$str");
7      }
8  });
9}

The secret sauce at the end of the subquery is GROUP BY with HAVING. HAVING is like a WHERE clause applied after the grouping and count aggregation, so it allows us to filter on the aggregated count. We group by entity id so we have one record per entity id and use a having clause to make sure we only return records where the count of fields that match our field conditions is the same as the number of fields in our initial value — entities that matched conditions for every field in our filters, which is the AND condition between fields.

1->groupBy('entity_id')
2->having('value_count', '=', count($value));

This approach would work with other types of aggregation like concatenation, so there are alternative methods to using COUNT or even using JSON_CONTAINS that might allow for some further optimization.

That’s it. This was my first brush with Laravel Query Builder and I’m certainly no SQL expert, so please add comments if you have better ways to achieve these results.