Jun 10, 2021

Level 54

Spatie Query Builder ignores my with

I'm using using Spaties LaravelQueryBuilder to allow for some additional filtering on my api.

I've got a model called 'AvailableIntegration' and this model has a relationship through to Category that is a belongsToMany;

Without QueryBuilder I can make the following query

      $availableIntegrations = QueryBuilder::for(AvailableIntegration::class)
    		->allowedIncludes(['categories'])
            ->with(['categories'])
            ->paginate();

and this returns

    "data": [
        {
            "id": "2b3bf55a-b09f-61eb-c86b-0f749868b2df",
            "name": "Product Name",
            "categories": [
                {
                    "id": "8b3bf55a-b09f-61eb-ca09-c2a671d29bbb",
                    "name": "Payments"
                }
            ]
        }
    ],

What I would like to do is to filter this by category.

So far, I've managed to filter by category_id by using ->join to attach the related table

eg:

/integrations?filter[categories]=8b3bf55a-b09f-61eb-ca09-c2a671d29bbb

using:

        $availableIntegrations = QueryBuilder::for(AvailableIntegration::class)
            ->join('available_integration_category',
                'available_integrations.id',
                'available_integration_category.available_integration_id')
            ->allowedFilters(
                AllowedFilter::exact('categories', 'available_integration_category.category_id', false)
            )
            ->allowedIncludes(['categories'])
            ->with(['categories'])
            ->paginate();

returns

    "data": [
        {
            "id": "8b3bf55a-b09f-61eb-ca09-c2a94268a229",
            "name": "Product Name",
            "categories": []
        }
    ],

Whilst this does apply the filter and get me the correct result filtered by 'available_integration_category.category_id', what is happening is that I'm loosing the results that were coming through:

    "categories": []

I've been reading https://spatie.be/docs/laravel-query-builder/v3/features/filtering but not able to spot what I need (apart from whether I should be adding scopes)

So my question is two fold.

1: can anyone advise on the correct way to filter on belongsToMany
2: does anyone know why my 'categories' aren't being returned when I add the join?

Thanks in advance.

jlrdw

4 years ago

Level 75

In spate, I wonder if allowedIncludes is used instead. Perhaps you can try that.

https://spatie.be/docs/laravel-query-builder/v2/features/including-relationships

@automica with your experience you could probably do everything you need just with eloquent. But just a thought on it.

Edit: I re read an example:

Quote:

$users = QueryBuilder::for(User::class)
    ->allowedIncludes(['posts'])
    ->get();

// $users will have all their their `posts()` related models loaded

unquote

Seems that with the line ->allowedIncludes(['posts']) it works without a with clause.

automica

4 years ago

Level 54

I’m already using allowedIncludes. If you use that on it’s own, you are required to add an argument to specify which relationships you want to load. Using with loads the categories relationship.

I probably don’t need to use both allowedIncludes and with but the point is that without the join I get my categories loaded, and with it I don’t.

I’m keen to use this package as it gives a lot of functionality to our FE without me having to reinvent the wheel.

automica

4 years ago

Level 54

seems the issue isn't specifically QueryBuilder, but the presence of the join which is breaking the relationships

     $availableIntegrations = QueryBuilder::for(AvailableIntegration::class)
            ->with(['categories'])
            ->paginate();

will return my category relationship

but:

     $availableIntegrations = QueryBuilder::for(AvailableIntegration::class)
          ->join('available_integration_category as a_i_c',
                'available_integrations.id',
                'a_i_c.available_integration_id')
            ->with(['categories''])
            ->paginate();

won't.

I'll try defining the join as a proper relationship and see if that fixes this.

MichalOravec

4 years ago

Level 75

Try to add select('table.*')

$availableIntegrations = QueryBuilder::for(AvailableIntegration::class)
    ->select('available_integration_category .*')
    ->join('available_integration_category', 'available_integrations.id', 'available_integration_category.available_integration_id')
    ->with(['categories''])
    ->paginate();

Note: I've never used spatie query builder.

1 like

automica

4 years ago

Level 54

@michaloravec thanks.

Adding the extra

->select(['available_integration_category.*','available_integrations.*'])

has brought back the relationships.

I'm going to have to RTFM to work out how to get these to play nicely with the QueryBuilder though.

I'll update the ticket when I work out how to do it.

Please or to participate in this conversation.