Professional way to use it

[vitalijalbu]

Hi guys, anyone could share a professional way to use it? Now i’m doing query at controller level, but want to split it and have more flexibility and avoid DRY.
Is out there any open source project using spatie query? So maybe inspecting the code will help.

[Robert-Hansen]

Hi @vitalijalbu

Here's an example of how you can split out filters, sorts, and includes with the Spatie Query Builder to keep your code clean, flexible, and avoid repetition. This is just a conceptual example, and I haven't tested the provided code, but it should give you an idea of how to approach things.

In this example, we use custom filters, sorts, and includes, all managed in separate classes, rather than putting everything in the controller.

Query

<?php

namespace App\Api\ShoppingLists\Queries;

class ShoppingListIndexQuery extends QueryBuilder
{
    public function __construct(Request $request)
    {
        // Initialize the base query with the ShoppingList model
        $query = ShoppingList::query();

        parent::__construct($query, $request);

		// Apply allowed includes, filters, and sorts
        $this
            ->defaultSort('-created_at')
            ->allowedIncludes([
                AllowedInclude::relationship('items'),
                AllowedInclude::relationship('user'),
            ])
            ->allowedFilters([
                AllowedFilter::custom('item_count', new ItemCountFilter),
            ])
            ->allowedSorts(
				AllowedSort::custom('item_count', new ItemCountSort())->defaultDirection('desc'),
				AllowedSort::custom('latest_activity', new LatestActivitySort()),
			);
    }

    /**
     * Filter shopping lists by a specific user ID.
     * 
     * @param int $userId
     * @return self
     */
    public function forUser(int $userId): self
    {
        $this->where('user_id', $userId);
        
        return $this;
    }

    /**
     * Eager load the 'items' relationship for ShoppingList
     * 
     * @return self
     */
    public function withItems(): self
    {
        return $this->with('items');
    }
}

Custom Sort:

<?php

namespace App\Api\ShoppingLists\Sorts;

class ItemCountSort implements Sort
{
    /**
     * Apply sorting to the query based on the number of items in the shopping list.
     * 
     * @param Builder $query
     * @param bool $descending
     * @param string $property
     */
    public function __invoke(Builder $query, bool $descending = true, string $property = 'item_count')
    {
        $direction = $descending ? 'DESC' : 'ASC';

        $query
            ->select('shopping_lists.*')
            ->leftJoin('shopping_list_items', 'shopping_list_items.shopping_list_id', '=', 'shopping_lists.id')
            ->groupBy('shopping_lists.id')
            ->orderByRaw("COUNT(shopping_list_items.id) {$direction}");
    }
}

<?php

namespace App\Api\ShoppingLists\Sorts;

class LatestActivitySort implements Sort
{
    /**
     * Apply sorting by latest activity
     * 
     * @param Builder $query
     * @param bool $descending
     * @param string $property
     */
    public function __invoke(Builder $query, bool $descending = true, string $property = 'latest_activity')
    {
        $direction = $descending ? 'DESC' : 'ASC';

        $query
            ->select('shopping_lists.*')
            ->selectRaw('
                (
                    SELECT GREATEST(
                        COALESCE(MAX(items.updated_at), shopping_lists.updated_at),
                        COALESCE(MAX(items.created_at), shopping_lists.created_at),
                        shopping_lists.updated_at,
                        shopping_lists.created_at
                    )
                    FROM shopping_list_items items 
                    WHERE items.shopping_list_id = shopping_lists.id
                ) as latest_activity
            ')
            ->orderByRaw("latest_activity {$direction}");
    }
}

Custom Filter:

namespace App\Api\ShoppingLists\Filters;

class ItemCountFilter implements Filter
{
    /**
     * Apply filtering by minimum item count to the query.
     * 
     * @param Builder $query
     * @param mixed $value
     * @param string $property
     */
    public function __invoke(Builder $query, $value, string $property)
    {
        $query
            ->select('shopping_lists.*')
            ->leftJoin('shopping_list_items', 'shopping_list_items.shopping_list_id', '=', 'shopping_lists.id')
            ->groupBy('shopping_lists.id')
            ->havingRaw('COUNT(shopping_list_items.id) > ?', [(int)$value]);
    }
}

Controller

<?php

namespace App\Api\ShoppingList\Controllers;

class ShoppingListController
{
    public function index(Request $request, ShoppingListIndexQuery $query)
    {
    	$user = $request->user();
    
    	$shoppingLists= $query
        	->forUser($user->id)  // Filter shopping lists by user
	        ->withItems() // Eager load the items relationship
	        ->paginate();
    
        return ShoppingListResource::collection($shoppingLists);
    }
}

[GET] /api/shopping-lists?filter[item_count]=5&sort=item_count&include=items,user

This structure allows you to manage complex queries in a clean, DRY manner while keeping your controller simple. It also offers the flexibility to extend with more filters, sorts, or includes without cluttering your controllers.

While I haven’t tested the exact example provided, it should work as inspiration for structuring your own queries with Spatie's Query Builder in a more modular and flexible way.

Hope this helps!

[vitalijalbu]

thanks, could you give me please and advice on my repository (uncompleted) but just to make an idea?
Basically I extended the Query and want only have separated file query for every model, so that way i won't touch controllers to add sorters or filters
https://github.com/vitalijalbu/laravel-api/blob/main/app/Query/ProductQuery.php