How to get SQLite error in PHP/Laravel when quoted column does not exist

Question

Related to my previous question, I found out that due to an error I made, Laravel generates a wrong SQL query:

select * from "companies" where "companies"."id" = '9c54986f-8284-4da9-b826-c7a723de279b' and "companies"."deleted_at" is null and "company_id" = '9c54986f-8284-4da9-b826-c7a723de279b'

The problem here is that company_id does not exist in companies; however, the query does not generate an error when run, it just returns no result.

I suppose the problem here is that "company_id" is treated as a literal instead of a column reference; if I remove the quotes I get a proper error:

Error: in prepare, no such column: company_id (1)

I also get a proper error if I add the table prefix to the column name:

sqlite> select * from "companies" where "companies"."id" = '9c54986f-8284-4da9-b826-c7a723de279b' and "companies"."deleted_at" is null and "companies"."compa
ny_id" = '9c54986f-8284-4da9-b826-c7a723de279b';
Error: in prepare, no such column: companies.company_id (1)

Is there a way to solve this problem by acting on Laravel's or SQLite's configuration? I cannot alter how the queries are generated, as they are generated by the framework itself.

Also, I am NOT asking why this specific query behave as it does, that was already clear to me.

The fragment and "company_id" = '9c54986f-8284-4da9-b826-c7a723de279b' is generated by a global scope implemented like this:

abstract readonly class UnlessAuthorizedScope implements Scope {
    public function __construct(
        private   string  $modelField,
        protected ?string $authorizingPermission,
        private   string  $userField,
    ) {}

    public function apply(Builder $builder, Model $model): void {
        if (Auth::hasUser()) {
            $user = Auth::user();

            if (
                !$this->authorizingPermission
                || !$user?->can($this->authorizingPermission)
            ) {
                $builder->where(
                    $this->modelField,
                    $user?->{$this->userField}
                );
            }
        }
    }
}

which is then implemented in:

readonly class CurrentCompanyScope extends UnlessAuthorizedScope {
    public function __construct(?string $authorizingPermission = null, ?string $modelField = null) {
        parent::__construct(
            $modelField ?? "company_id",
            $authorizingPermission,
            "company_id"
        );
    }
}

and finally used as:

class Company extends Model {
    protected static function booted(): void {
        parent::booted();
        static::addGlobalScope(new CurrentCompanyScope(
            CompanyPermission::ViewAll->value,
            // the error was here, instead of specifying "id", I kept the default "company_id" value
        ));
    }
}

Answer 1

I have been able to fix this problem by modifying the implementation of my base global scope:

abstract readonly class UnlessAuthorizedScope implements Scope {
    public function __construct(
        private   string  $modelField,
        protected ?string $authorizingPermission,
        private   string  $userField,
    ) {}

    public function apply(Builder $builder, Model $model): void {
        if (Auth::hasUser()) {
            $user = Auth::user();

            if (
                !$this->authorizingPermission
                || !$user?->can($this->authorizingPermission)
            ) {
                $builder->where(
                    $model->getTable().".".$this->modelField, // changed here
                    $user?->{$this->userField}
                );
            }
        }
    }
}

by adding an explicit table prefix to the field name, it was kept in the query generated by the framework, which then became:

select * from "companies" where "companies"."id" = '9c54986f-8284-4da9-b826-c7a723de279b' and "companies"."deleted_at" is null and "companies"."compa
ny_id" = '9c54986f-8284-4da9-b826-c7a723de279b'

and this new query generated a proper exception, which was caught by the framework and displayed on the page.