Support one whereHas for several filters.

[IndexZer0]

This PR adds AllowedRelationshipFilter class to be used when wanting to group multiple AllowedFilter's into the same exists query.

Problem:

When using multiple AllowedFilter's for nested relations - the package adds mutliple exist clauses to the query.

->allowedFilters([
    AllowedFilter::exact('relatedModels.id'),
    AllowedFilter::exact('relatedModels.name'),
    AllowedFilter::exact('relatedModels.nestedRelatedModels.id'),
    AllowedFilter::exact('relatedModels.nestedRelatedModels.name'),
])

• Generates 6 EXISTS queries.

SELECT
    *
FROM
    `test_models`
WHERE
    EXISTS (
        SELECT
            *
        FROM
            `related_models`
        WHERE
            `test_models`.`id` = `related_models`.`test_model_id`
            AND `related_models`.`id` = 1)
        AND EXISTS (
            SELECT
                *
            FROM
                `related_models`
            WHERE
                `test_models`.`id` = `related_models`.`test_model_id`
                AND `related_models`.`name` = 'asdf')
            AND EXISTS (
                SELECT
                    *
                FROM
                    `related_models`
                WHERE
                    `test_models`.`id` = `related_models`.`test_model_id`
                    AND EXISTS (
                        SELECT
                            *
                        FROM
                            `nested_related_models`
                        WHERE
                            `related_models`.`id` = `nested_related_models`.`related_model_id`
                            AND `nested_related_models`.`id` = 1))
                    AND EXISTS (
                        SELECT
                            *
                        FROM
                            `related_models`
                        WHERE
                            `test_models`.`id` = `related_models`.`test_model_id`
                            AND EXISTS (
                                SELECT
                                    *
                                FROM
                                    `nested_related_models`
                                WHERE
                                    `related_models`.`id` = `nested_related_models`.`related_model_id`
                                    AND `nested_related_models`.`name` = 'ghjk'))

Desired Query

• 2 EXISTS queries.

SELECT
    *
FROM
    `test_models`
WHERE
    EXISTS (
        SELECT
            *
        FROM
            `related_models`
        WHERE
            `test_models`.`id` = `related_models`.`test_model_id`
            AND `related_models`.`id` = 1
            AND `related_models`.`name` = 'asdf'
            AND EXISTS (
                SELECT
                    *
                FROM
                    `nested_related_models`
                WHERE
                    `related_models`.`id` = `nested_related_models`.`related_model_id`
                    AND `nested_related_models`.`id` = 1
                    AND `nested_related_models`.`name` = 'ghjk'))

Usage

->allowedFilters([
    AllowedRelationshipFilter::group('relatedModels', ...[
        AllowedFilter::exact('relatedModels.id', 'id'),
        AllowedFilter::exact('relatedModels.name', 'name'),
        AllowedRelationshipFilter::group('nestedRelatedModels', ...[
            AllowedFilter::exact('relatedModels.nestedRelatedModels.id', 'id'),
            AllowedFilter::exact('relatedModels.nestedRelatedModels.name', 'name'),
        ]),
    ]),
]);

---

I have seen various requests for this in the past, for example

https://github.com/spatie/laravel-query-builder/discussions/634

https://github.com/spatie/laravel-query-builder/discussions/663

---

• Tests have been added to demonstrate useage.
• Documentation not updated.
  • Will update once preliminary go ahead for this feature has been confirmed.

---

At this point just looking for your thoughts and feedback on this.

To me, this feels like quite an important missing feature of this package and if the core maintainers could give some feedback, insight and pointers on this then I'm happy to make any changes and then update documentation to match.