I am trying to find all the records for which a certain field matches the beginning of a string literal provided as a fixed parameter. The needle is a field from a table.
Example: Given a parameter 'John Doe', I would like to get all the records of the table user, for which the first_name field is a sub-string of the beginning of that 'John Doe' string (e.g. matching records would be those for which the first_name is 'John', or 'Jo', or 'Joh', etc.
It seems I can achieve this with the following SQL:
SELECT * FROM user WHERE 'John Doe' LIKE first_name || '%'
Now how to translate that to peewee? It's strange that I am able to actually obtain the correct SQL for this but for some reason peewee is returning an empty result while the same query is working when I enter it directly via SQLite command line.
User.select().where(SQL("'John Doe'").startswith(User.first_name))
When I check the sql() of this, it shows:
('SELECT "t1"."id", "t1"."first_name" FROM "user" AS "t1" WHERE ("John Doe" LIKE ("t1"."first_name" || ?))', ['%'])
But the result is empty. Same SQL in the command line returns the right record... What am I doing wrong?
This is how I would write it:
User.select().where(Value('John Doe') ** (User.first_name + '%'))
The exponentiation overload translates to case-insensitive LIKE.