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.
What I think is happening here is that "company_id" is being interpreted as a string literal, rather than a column. Keep in mind that SQLite accepts string literals as either singly or doubly quoted. Apparently, SQLite’s heuristic for interpreting doubly quoted strings is:
- First check if the string can be mapped to some database identifier (such as a column)
- That failing, then treat the string as a string literal
Here is your query again, formatted:
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';
As the company_id column does not exist, this query appears to be getting interpreted as:
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';
Of course, the string comparison in the last term of the WHERE clause will never be true, hence no records are being returned.
There is no need for your columns to be in double quotes, so don’t use them, to avoid this sort of problem.