Problem about subquery


#1

I know that CBL2.0 doesn’t support subquery. So I tried to mimic the query with two distinct queries (Main and Sub). The basic idea is: Sub query will return an ID array for Main to use in Where statement.

However, it seems there’s no way to directly use the array (Code lang is C#, and the code is just to demonstrate the idea):
1

// Doesn’t work. Because Expression.Value() not accept String Array
ArrayExpression.Any(ArrayExpression.Variable(“x”))
.In(Expression.Value(IDArray)) // IDArray is a C# String Array
.Satisfies(
ArrayExpression.Variable(“x”).EqualTo(Meta.ID)
)
2

// Doesn’t work. Because Parameters.setValue() not accept String Array
ArrayExpression.Any(ArrayExpression.Variable(“x”))
.In(Expression.Parameter(“array_to_be_set_later”))
.Satisfies(
ArrayExpression.Variable(“x”).EqualTo(Meta.ID)
)
3

// Doesn’t work. Same reason as previous.
ArrayFunction.Contains(Expression.Value(IDArray), Meta.ID) // IDArray is a C# String Array
There seems only two viable approaches:
1

// Convert the String Array to IExpression Array, then use In
Meta.ID.In(
IDArray.Select(id => Expression.String(id))
)
2

// Convert the String Array to RegularExpression, then use Regex
var IDRegex = “^(” + id1 + “|” + id2 + “)$”
Meta.ID.Regex(Expression.String(IDRegex))
My question is, is there a better way to do subquery?
And if it’s not, which way is the best (I think the In should be better than Regex)?

Also, I’m intended to use the Main query as live query. So it would be nice if the Main can accept Parameters. Otherwise, I need to reconstruct the Main query whenever the Sub changes.


#2

The best way is the way that works best for you. I don’t think we can make any recommendations at this low level as to which is “better.” But I can’t think of any other way to do it other than what you have come up with (and the “in” case is actually something I wouldn’t have thought to try, so nice if that works!)