Replicate IFMISSINGORNULL in QueryBuilder

Mobile
1

How can I reproduce the ORDER BY clause of the following query using QueryBuilder in Coucbase Lite (c#)?

Also, is it possible to do a join with the double pipe (||) in QueryBuilder?

SELECT c.CardName AS CardName, h.CardName AS CardNameH
FROM RedbookCore AS g
LEFT JOIN RedbookCore AS c ON meta©.id = (‘UserCard~’ || g.CardGuid)
LEFT JOIN RedbookEBookH2 AS h ON meta(h).id = (‘Directory~’ || g.CardGuid)
WHERE meta(g).id LIKE ‘UserCardGrouping~%’
ORDER BY UPPER(IFMISSINGORNULL(c.CardName, h.CardName));

2

Unfortunately we don’t have the same functionality as IFMISSINGORNULL in the mobile query feature (I think you might be the first person to ask about it). I don’t think there is a great way to do to this, so you’ll probably have to resort to post-query ordering if that is an option. @jens Do you have a different suggestion?

I also don’t know what the double pipe operator does. Is it a concat?

3

Unfortunately, I am wanting to use the Limit functionality, so I guess I’ll have to forego that and grab everything from the database first for my sorting.

Is there any way to do conditional testing in a condition?

|| concats a literal and an object value

4

It’s in LiteCore; is it just not exposed in the public API?

There’s a CASE operator (unless that also isn’t exposed in the public API…)

|| concats a literal and an object value

We’ve had requests for CONCAT, but it isn’t implemented yet, sorry!

5

Tagging @pasin and @priya.rajagopal for the API requests above

6

CASE Is not yet in the public API (unless I’m missing something). Any idea when that might be exposed? Or is there a way to use it right now?

7

If CBL exposed the JSON query syntax as an option for creating a query (which I’m strongly in favor of) then you could drop down to that; but unfortunately it doesn’t. I don’t know of any workaround.