Subquery using Java SDK's DSL

Java SDK
1

Hi there,

Is it possible to create statements that contain subqueries? I have not found anything about it in the docs or in the forums.

Let’s say I have this query:

SELECT COUNT(*)
FROM it d
WHERE d.type = 'SR_OFFER'
AND d.status = 'CREATED'
AND d.id IN (
                SELECT RAW lockedItemId
                FROM it
                WHERE type = 'LOCK'
              )

Using the Java SDK’s DSL, the outer query would look something like this:

Statement statement = select("count(*)")
        .from(bucketName)
        .where(
                x("type").eq(x("$type")
                .and(x("status").eq(x("$fromStatus"))))
        );

I’m to trying to figure out how to add the AND […] IN clause where the expression is the subquery.

For the moment I’ll try to workaround it by performing two queries. First, the subquery to get the collection of IDs; second the outer query with the result of the previous one in the IN clause as JSONArray.

Cheers.

2

I just found a way. Subquery:

Statement lockedDocumentsQuery =
        select("RAW l.lockedItemId")
                .from(bucketName).as("l")
                .where(x("l.type").eq(x("LOCK")));

And complete statement (outer + subquery):

Statement statement = select("count(*)")
        .from(bucketName)
        .where(
                x("type").eq(x("$type")
                .and(x("status").eq(x("$fromStatus"))))
                .and(x("id").notIn(sub(lockedDocumentsQuery))
        );

Any SDK engineer out there, if there is a better/more appropriate way please let it know :slight_smile:

Cheers.

1 Like