Is unsafeSqlExpr unobservable?

[mhyassin]

I noticed I'm getting an Illegal clause sql from the encodeMatcher when the observable is triggered, I can see that the unsafeSqlQuery can be observed from the docs, but nothing is mentioned regarding unsafeSqlExpr

[radex]

Good question, I'd say it's a bug. As long as you're not doing anything unusual in the unsafeSqlExpr, the observation should change to the "refetch on change" strategy, but it tries to move forward with encoding a matcher, which won't work. As a workaround, if you add a Q.on() or one of the other clauses that would trigger this condition https://github.com/Nozbe/WatermelonDB/blob/master/src/observation/encodeMatcher/canEncode.js - it should work

[mhyassin]

Nothing unusual in the unsafeSqlExpr no.
I've just tried adding a Q.sortBy and it definitely fixed it, thanks a lot for the workaround and the fast answer, should I open an issue to keep track of the bug, can I help with fixing it somehow?

[radex]

Yes and yes :)

[KrisLau]

This seems to be the closes to the issue I'm having I think? I'm trying to use unsafeSqlExpr to make .sortBy case insensitive but when I add it to my query it seems to just return no results:

 withObservables([...], ({database, ...}) => ({
    teams: database
      .get('team')
      .query(
        Q.on(...),
        Q.sortBy('name', Q.asc),
        Q.unsafeSqlExpr('LOWER(team.name)'),
      )

I also tried Q.unsafeSqlExpr('ORDER BY LOWER(team.name) ASC'), which gave me an error:
[Diagnostic error: Error: Failed to prepare query statement - sqlite error 1 (near "ORDER": syntax error)]

Using a a fully Q.unsafeSqlQuery works but preferably I would prefer to use watermelondb's inbuilt querying