Filter Relations: Ambiguous column reference error

[nhatkha1407]

First of all, thank you to the author and contributors for creating this useful package!

I ran into the following error when using the package:

Endpoint:

/posts?filters[collections][id][$eq]=1

Post.php

public function collections(): BelongsToMany
{
    return $this->belongsToMany(
        PostCollection::class,
        'post_collections_ref_posts'
    )->withPivot(['position'])->withTimestamps();
}

Error:

Illuminate\Database\QueryException: SQLSTATE[23000]: Integrity constraint violation: 1052 Column `id` in where clause is ambiguous (Connection: mysql, SQL: select count(*) as aggregate from `posts` where exists (select * from `post_collections` inner join `post_collections_ref_posts` on `post_collections`.`id` = `post_collections_ref_posts`.`post_collection_id` where `posts`.`id` = `post_collections_ref_posts`.`post_id` and `id` = 1) and `posts`.`deleted_at` is null) in file /vendor/laravel/framework/src/Illuminate/Database/Connection.php on line 822

The query needs to disambiguate the ambiguous id column reference.

-- Current
...`post_collections_ref_posts`.`post_id` and `id` = 1

-- Expected: Specify the table alias for relation columns
...`post_collections_ref_posts`.`post_id` and `post_collections`.`id` = 1

I'm happy to provide any other details that would be helpful.

Again, thanks for your work on this package!

[abbasudo]

hello, glad that purity helped you. purity at it core uses laravels whereHas (laravel docs) function to filter by relations. try whereHas function outside purity and see if it works. you probably need to redefine the belongsToMany relation.

[nhatkha1407]

What do you think if we manual add table name inside whereHas

https://github.com/abbasudo/laravel-purity/blob/342d882acd42eeadd02e26056e40e79fb69b133a/src/Filters/Resolve.php#L188-L190

I tried implementing this in the EqualFilter.php file and it seems to work well. Adding the table name provides more clarity in the query and ensures we are filtering on the correct table.

Current: https://github.com/abbasudo/laravel-purity/blob/342d882acd42eeadd02e26056e40e79fb69b133a/src/Filters/Strategies/EqualFilter.php#L25-L27

Modified:

    foreach ($this->values as $value) {
        $query->where($query->from . '.' . $this->column, $value);
    }

Let me know if you have any other thoughts!