SQLInterpolation could not work with QueryInterfaceRequest

[ryohji]

What did you do?

I wrote the query (with GRDB 4.8.0), to select the color value from inner joined table:

try String.fetchOne(db, SQLRequest<String>("""
WITH
  Id(id) AS (SELECT id FROM TaggedItem WHERE itemId=\(itemId))
  Color(id,value) AS (SELECT id,value FROM TagMeta WHERE name="color")
SELECT value FROM Id
INNER JOIN Color USING(id)
INNER JOIN Tag USING(id)
ORDER BY Tag.priority
"""))

What did you expect to happen?

In the query above, there are two subqueries. I have TaggedItem and TagMeta struct, which conforms to both of PersistableRecord and FetchableRecord.
So, it looks pretty to replace the subquery such as TaggedItem.filter(Column("itemId") == itemId).

But SQLInterpolation does not accept QueryInterfaceRequest (such as TaggedItem.filter(...)), so I can not write the code such as:

try String.fetchOne(db, SQLRequest<String>("""
WITH
  Id(id) AS \(TaggedItem.filter(Column("itemId") == itemId))
...

What happened instead?

Xcode shows the error: "Instance method 'appendInterpolation' requires that 'QueryInterfaceRequest' conform to 'SQLExpressible'".

I know, your document said there is no support for QueryInterfaceRequest.

If there is more neat way to code, please let me know.
Thank you!

Environment

GRDB flavor(s): GRDB
GRDB version: 4.8.0
Installation method: CocoaPods
Xcode version: 11.2
Swift version: 5
Platform(s) running GRDB: iOS
macOS version running Xcode: 10.15.3

[groue]

Hello @ryohji

But SQLInterpolation does not accept QueryInterfaceRequest

This is true. You can't embed a query interface request in an SQL literal.

I hope to lift this limitation one day. #690 is actually one step in this direction.

The work that needs to be achieved is to turn SQLLiteral into a promise for SQL, just like query interface requests are. A promise for SQL is a value that can't emit any SQL until a database connection is available. For example, Player.filter(key: 1) can't emit any SQL until we can access the database, introspect the primary key of the players' table, and eventually produce SELECT * FROM player WHERE id = 1.

Currently SQLLiteral is not a promise. It can give you SQL without any database connection. And that's why it can't embed query interface requests. You have to pick another technique.

[ryohji]

@groue I got it! thank you!

[groue]

You can embed query interface requests in SQL literals with GRDB 5.0.0-beta