Hello,
I have another use case. For example, let’s say Artist "Leonardo da Vinci" has
these paintings:
* Mona Lisa
* The Last Supper
* Vitruvian Man
I need to build a qualifier / expression to obtain the artists with a painting
that starts with the letter V *and* another painting that starts with the
letter T. Artist Leonardo da Vinci would meet such criteria.
In EOF, I can do it as follows:
———————————————————
EOQualifier qual = Artist.PAINTINGS.exists(Painting.NAME.like(“V*”))
.and(Artist.PAINTINGS.exists(Painting.NAME.like(“T*”)));
var fetchSpec = new ERXFetchSpecification<Artist>(“Artist”, qual, null);
NSArray<Artist> artists = fetchSpec.fetchObjects(ec);
The qualifier qual works in memory and when fetching.
In Cayenne, I’m doing it as follows:
———————————————————
Expression exp1 = exp(“paintings.name like 'V%’”);
Expression exp2 = exp(“paintings.name like ’T%’”);
List<Artist> artists = ObjectSelect
.query(Artist.class)
.where(exp1.exists().andExp(exp2.exists()))
.select(oc);
which generates the following SQL (formatted by me for readability):
INFO: --- transaction started.
INFO: SELECT t0.DATE_OF_BIRTH, t0.NAME, t0.ID FROM ARTIST t0
WHERE
EXISTS (
SELECT t1.ID FROM PAINTING t1
WHERE t1.NAME LIKE ? AND ( t1.ARTIST_ID = t0.ID )
)
AND
EXISTS (
SELECT t2.ID FROM PAINTING t2
WHERE t2.NAME LIKE ? AND ( t2.ARTIST_ID = t0.ID )
)
[bind: 1->NAME:'M%', 2->NAME:'V%']
INFO: === returned 1 row. - took 360 ms.
INFO: +++ transaction committed.
It would be nice if cayenne would allow building an Expression that uses exists
when fetching and have the same expression work for filtering objects in memory.
Has that been considered? For example, the API would be nicer if you could do
this:
Expression exp = Artist.PAINTINGS.exists(Painting.NAME.like(“M%”))
.andExp(Artist.PAINTINGS.exists(Painting.NAME.like(“V%”)));
List<Artist> artists = ObjectSelect
.query(Artist.class)
.where(exp)
.select(oc);
If that does not fit the current design then how about EXISTS expressions
working for filtering objects in memory?
For example:
Expression exp = Artist.PAINTINGS.dot(Painting.NAME).like(“M%”).exists()
.andExp(Artist.PAINTINGS.dot(Painting.NAME).like(“V%”).exists());
// Fetch from database
List<Artist> artists = ObjectSelect
.query(Artist.class)
.where(exp)
.select(oc);
// Or in memory filtering
List<Artist> artists = exp.filterObjects(allArtists);
Thank you,
Ricardo Parada
