Example of creating a Query manually?

[mikezliu]

I'm trying to create and execute a Query manually (the query isn't defined in the .sq file) but not sure what the right way of doing it is. Is there an example?

Query(
            111111111,
            copyOnWriteList(),
            dbManager.driver(),
            "Player.sq",
            "customQuery",
    rawQuery) { cursor ->
        ...
    }

[JakeWharton]

This isn't exactly fully supported, unfortunately. You might be able to accomplish this, but the APIs exposed really only exist to serve the generated code, not manually written code.

Can you tell us about your use case more?

[mikezliu]

TLDR at bottom

I have an app that requires lots of custom sorting and other filtering on columns. There's about 10 types of sorts with 3-4 different true/false options for each, all sorting on one table, kinda looks like this:

|SELECT *
    |FROM players
    |  WHERE $customWhereSelector
    | ORDER BY $customOrderByStatement

Before SQLDelight I'd manually craft the SQL query with all the different sort options combined together. However since SQLDelight doesn't allow parameterizing the column names in the .sq files, I have to write out each separate sort query. So I tried doing that... comes out to several hundred queries but I finished it all 😓. I need to map all the custom query objects to my base classes which, all together the file is almost 10k lines.

Then second problem - kotlin native seems to choke when compiling (my old compile time was 2 minutes, now it's approaching 20 minutes). Seems the massive files with my custom sql mappers is causing an issue, and it's rather unmaintainable.

So I decided to delete it and start over and attempt the custom query method... it's sort of compiling. Basically I have a template query in the .sq file that generates some of the stubs for me, but then I manually execute the query myself (by creating a custom class that extends Query), then I map it to the class generated by the .sq file. But Since the DatabaseImpl class is private, I need to copy/paste the mapping part.

I think this is good enough for me for now, though if the DatabaseImpl class wasn't private, i'd be able to get rid of all my duplicate code since I'd be able to use the generated mapper from my template query. I need to handle notification of changes but I was planning to do that anyways since I need more granular notifications for perf reasons.

TLDR: I need to write the custom query myself because my app uses lots of sorting on custom columns, sqldelight .sq files does not allow parameterizing columns, and there's lots of duplicate code if I go down the route of writing out all the queries in the .sq files

[AlecKazakova]

this is the route we're taking to support more dynamic queries. Hopefully it can simplify things a bit? If not it'd be useful to have new issues with specific cases that doing conditionals in sql can't support and then we can go from there

[mikezliu]

I managed to do this by overriding Query and in execute() creating my own raw query, then mapping it to object containers. This is the only realistic way I see of supporting my scenario, since I have many custom sorting operators on different columns, if I were to have to write out all the queries in .sq it would create hundreds of different SQL statements and be unmaintainable in code.

[AlecKazakova]

does something like this not work?

ORDER BY CASE
    WHEN :sortField = 'name' THEN name
    WHEN :sortField = 'age' THEN age
    ELSE name END DESC

[mikezliu]

Ah I thought from the linked issue it wasn't supported. I'll give that a try later today

[AlecKazakova]

if thats not supported we should definitely do that

[raduz108]

@mikezliu how did you manage to get it done? I have similar issue: in the native code, a complex raw query built on the go, based on scenarios, filters, definitions, events, etc, absolutely NOT doable by the approach mentioned above. I really like type safety of the lib but ALSO we need to be able to just write a real life complex sqls.

[mikezliu]

@raduz108 I created a custom class that implements Query(mapper)

class YourCustomQueryClass<out T : Any>(
    ...
    mapper: (SqlCursor) -> T
) : Query<T>(mapper) {

    
    override fun addListener(listener: Listener) {
        driver.addListener(...)
    }

    override fun removeListener(listener: Listener) {
        driver.removeListener(...)
    }

    override fun <R> execute(mapper: (SqlCursor) -> QueryResult<R>): QueryResult<R> {
        return driver.executeQuery(...)
    }

    override fun toString(): String = "YourCustomQueryClass.sq:get"
}

Though this bit of code I haven't changed in ~4 years so I don't know if there's a better way to do it now with the latest release.

[raduz108]

@mikezliu so you create such query class each time you need custom query? Could you please show how do you actually use that? I somehow can't see the benefit.