Filament•15mo ago

Is it a normal behavior of the Select component??

I use the \Filament\Forms\Components\Select to describe a BelongsTo relation. On the screenshots a component declaration and database query. When I modify a query with a join clause and declare the Select::searchable() it produce a SQL exception. It add a Model's key field without a table. Without the Select::searchable() it works correctly. Full stack trace here: https://flareapp.io/share/VmekRDz7

Flare

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in where clause is ambiguous (Connection: mysql, SQL: select users.* from users left join user_statuses on users.user_status_name = user_statuses.name left join users mentors on mentors.id = users.mentor_id where id = 18 and (user_statuses.`is_operational...

Solution:

If you need your code working before then, you could probably rewrite that leftJoin/whereNot/where query to use whereHas() on a relationship with a closure query rather than manually leftJoin'ing them, which should work around it (as it would then be wrapped in a dependent subquery). Which in general is probably better practice when working in Eloquent.

Jump to solution

7 Replies

cheesegrits•15mo ago

First, as per #✅┊rules can you paste code, don't send screenshots of code. Second, can you paste the code of those methods you are calling in your relationship() method. It's almost certainly because you are adding something to the query without fully qualifying the field name.

GHosTOP•15mo ago

I don't post a code I post the screenshot. My methods has no relation to this but I attach it below.

    public static function conditionPossibleMentor(Builder $builder, Model|self $user): Builder {
        return $builder
            ->leftJoin($builder->raw('`users` mentors'), 'mentors.id', 'users.mentor_id')
            ->whereNot(
                $user->getTable() . '.' . $user->getKeyName(),
                $user->getKey()
            )
            ->where(fn(Builder $query) => $query
                ->orWhereNull('users.mentor_id')
                ->orWhere('users.mentor_id', $user->mentor_id)
                ->orWhere(fn(Builder $query) => $query
                    ->whereNot('users.mentor_id', $user->getKey())
                    ->whereNot('mentors.mentor_id', $user->getKey())
                )
            )
        ;
    }

    public static function statusOperational(Builder $builder): Builder {
        return static::builderJoinStatus($builder)
            ->where(function (Builder $query) {
                $query
                    ->orWhere('user_statuses.is_operational', true)
                    ->orWhereNull('user_statuses.is_operational');
            })
        ;
    }

    public static function builderJoinStatus(Builder $builder, string $join_type = 'left'): Builder {
        static::builderSelectUsersAll($builder);

        $join = $join_type . 'Join';

        if (!array_reduce(
            $builder->toBase()->joins ?? [],
            fn(bool $carry, JoinClause $clause) => $carry |= $clause->table == 'user_statuses',
            false
        )) {
            $builder->{$join}('user_statuses', 'users.user_status_name', 'user_statuses.name');
        }

        return $builder;
    }

    public static function conditionPossibleMentor(Builder $builder, Model|self $user): Builder {
        return $builder
            ->leftJoin($builder->raw('`users` mentors'), 'mentors.id', 'users.mentor_id')
            ->whereNot(
                $user->getTable() . '.' . $user->getKeyName(),
                $user->getKey()
            )
            ->where(fn(Builder $query) => $query
                ->orWhereNull('users.mentor_id')
                ->orWhere('users.mentor_id', $user->mentor_id)
                ->orWhere(fn(Builder $query) => $query
                    ->whereNot('users.mentor_id', $user->getKey())
                    ->whereNot('mentors.mentor_id', $user->getKey())
                )
            )
        ;
    }

    public static function statusOperational(Builder $builder): Builder {
        return static::builderJoinStatus($builder)
            ->where(function (Builder $query) {
                $query
                    ->orWhere('user_statuses.is_operational', true)
                    ->orWhereNull('user_statuses.is_operational');
            })
        ;
    }

    public static function builderJoinStatus(Builder $builder, string $join_type = 'left'): Builder {
        static::builderSelectUsersAll($builder);

        $join = $join_type . 'Join';

        if (!array_reduce(
            $builder->toBase()->joins ?? [],
            fn(bool $carry, JoinClause $clause) => $carry |= $clause->table == 'user_statuses',
            false
        )) {
            $builder->{$join}('user_statuses', 'users.user_status_name', 'user_statuses.name');
        }

        return $builder;
    }

    public static function builderSelectUsersAll(Builder $builder): Builder {
        if (!array_reduce(
            $builder->toBase()->columns ?? [],
            fn(bool $carry, Expression $exp) => $carry |= preg_replace('/[\'"`]/', '', $exp->getValue(app(Grammar::class))) == 'users.*',
            false
        )) {
            $builder->selectRaw('users.*');
        }

        return $builder;
    }

    public static function builderSelectUsersAll(Builder $builder): Builder {
        if (!array_reduce(
            $builder->toBase()->columns ?? [],
            fn(bool $carry, Expression $exp) => $carry |= preg_replace('/[\'"`]/', '', $exp->getValue(app(Grammar::class))) == 'users.*',
            false
        )) {
            $builder->selectRaw('users.*');
        }

        return $builder;
    }

As I say above without call the Select::searchable() method it works correctly. It looks like a bug.

cheesegrits•15mo ago

Yup, I wanted to check what those methods were doing to the query so I could confirm my suspicion. And it is indeed manually adding joins (rather than using relationships) which tickled this error. I've submitted a PR on Filament which should fix this. Can't guarantee when / if Dan will merge it, but I suspect he will and it won't take too long. He's just kinda slam dunked busy atm. I submitted a similar PR a few months ago which fully qualified the key in a couple of other places, I guess I just missed this one at the time. https://github.com/filamentphp/filament/pull/8768

GitHub

Qualify key name in getSelectedRecordUsing() by cheesegrits · Pull ...

Changes have been thoroughly tested to not break existing functionality. Need to qualify this key in case the user has joined other tables in their modifyQueryUsing, to avoid 'column name is a...

Solution

cheesegrits•15mo ago

@GHosT the PR got merged, so next release will include the fix.

GHosTOP•15mo ago

Thanks a lot for your answer, it confirms my assumption. I'll try your recommendations, but for me this functionality is not critical. It a very good news!! I'll be waiting a release👍 Thanks!!

cheesegrits•15mo ago

n/p

Gaming

Programming

Is it a normal behavior of the Select component??