Does SqlDelight support `WITH` clause? Trying to do something like this: `WITH existingUser AS (SELE...

# squarelibraries

[Elka]

Does SqlDelight support WITH clause?
Trying to do something like this:
WITH existingUser AS (SELECT * FROM user WHERE isRemoved = 0);

[Elka]

@alec Starting new with SqlDelight and Sqlite in general. I have a couple of questions:
• Do we have something documented on where we can find some advanced usages of the APIs?
• I am trying to see if WITH would allow me to not repeat myself in other queries. I tried to write this statement

WITH existingUser AS (SELECT * FROM user WHERE isRemoved = 0);

So I can later create a query like this:

selectByName:
SELECT * FROM existingUser WHERE name = ?;

The query selectByName would return me all the users that were not removed and verifying the condition name = ?

[alec]

it needs to be part of the query itself: id recommend checking out the examples here https://www.sqlite.org/lang_with.html

[Elka]

ahh now i understand WITH ... This won't work in my case... Is there a way to reuse queries? Otherwise, I will always have to put isRemoved = 0 in all my queries..

[Elka]

Thanks for your help!
One more thing is there a way to rename the interface generated by this query while keeping the function generated named selectUserWithDepartment

selectUserWithDepartment:
SELECT *
FROM user
INNER JOIN department
ON user.departmentId = department.id;

[alec]

no, you probably want to use kotlin typealias for that

[alec]

you can reuse queries using sqlite views

[Elka]

Thanks! I thought about views didn't know they you can create Temporary views...