Nested subqueries using "in" clause


#1

Put simply, I am working on an Android app and trying to write a query that does the following (and actually is confirmed to work in the query workbench on the server end):

select a.* from data a where a.entityType=‘Foo’ and a.category=‘some-random-uuid’ and meta(a).id in (select raw foo from data where entityType=‘Bar’);

The id of a document of entityType Foo is potentially kept as a key “foo” on document(s) of entityType Bar. I’ve tried different combinations of things in the mobile app, but have been unsuccessful thus far.

LHS:
Query.select(SelectResult.expression(Expression.property(""))).from(DataSource.database(database)).where(Expression.property(“entityType”).equalTo(“Foo”).and(…other filters that work…))

RHS: Query.select(SelectResult.expression(Expression.property(“foo”))).from(DataSource.database(database)).where(Expression.property(“entityType”).equalTo(“Bar”))

Basically what I’ve tried has been different variations of LHS.and(Expression.meta().getId().in(RHS) and trying to run that but not getting any results, when I can confirm that there should absolutely be results from just eyeballing my dataset.

Longhand: Query.select(SelectResult.expression(Expression.property(""))).from(DataSource.database(database)).where(Expression.property(“entityType”).equalTo(“Foo”).and(…other filters that work…)).and(Expression.meta().getId().in(Query.select(SelectResult.expression(Expression.property(“foo”))).from(DataSource.database(database)).where(Expression.property(“entityType”).equalTo(“Bar”)));

I suspect that having that second Query.select() buried inside my overall query may be what is the issue here, but I’m not sure how to go about this otherwise. I am really trying to avoid having to just get the two ResultSets and then doing my own map-reduce on them. Any ideas are appreciated


#2

Subqueries are not supported yet in Lite. That IN is for array operations (I.e. the type of a property is an array).


#3

Is there any ETA on when subqueries will be supported? Unless I am missing something, I just did a spot check of the Expression API in 2.0.0-DB023 and it still appears as though the IN doesn’t work for anything other than other Expressions


#4

Subqueries are not supported in 2.0.0.

We don’t have an ETA on supporting this (well- it won’t be in 2.1 due out in June sometime). But as with other requests, it will be prioritized against other features.
(Also, you mentioned DB023 - hope you have moved to the GA release of 2.0.0 )