[CAY-2816] (NOT) EXIST usability - provide simple expression syntax

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Fix Version/s: 5.0-M1
• Assignee: Nikita Timofeev
• Reporter: Andrus Adamchik
• Created: 19/Sep/23 13:38
• Updated: 02/Feb/24 09:40

Description

Background

Currently, the syntax for (NOT) EXISTS requires two queries and an explicit correlation expression:

https://cayenne.apache.org/docs/4.2/cayenne-guide/#subqueries
https://twitter.com/ApacheCayenne/status/1562702875327082496

This is great for a general case, but often the "enclosing" expression is simply a relationship from top query root to subquery root. So the syntax can be drastically simplifed, replacing sub ObjectSelect with a simple expression. So this:

ObjectSelect<Painting> subQuery = ObjectSelect.query(Painting.class)
        .where(Painting.ARTIST.eq(Artist.ARTIST_ID_PK_PROPERTY.enclosing()));

long count = ObjectSelect.query(Artist.class)
        .where(ExpressionFactory.notExists(subQuery))
        .selectCount(context);

becomes this:

long count = ObjectSelect.query(Artist.class)
        .where(ExpressionFactory.notExists(Artist.PAINTINGS))
        .selectCount(context);

or even better, this:

long count = ObjectSelect.query(Artist.class)
        .where(Artist.PAINTINGS.notExists())
        .selectCount(context);

Task Scope

(TODO: break to subtasks?)

• Implement "ExpressionFactory.exists(Exp)" and "ExpressionFactory.notExists(Exp)". The trick would be to check all paths within the Exp and build the corresponding correlation expressions for the subquery. It may potentially resolve to multiple subqueries (if multiple relationships are specified), or no subqueries (for conditions based on the root query attributes: 'not exists name = "A"' would become 'not (name = "A")' in SQL)
• Implement parseable exp syntax "exists <exp>" and "not exists <exp>"
• Implement "exp.exists()" and "exp.notExists()" for fluent syntax