EXISTS and EXISTS use case

--Apple-Mail=_B53CF4E7-C128-4175-A9D7-92CFAD98696D
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8

Hello,=20

I have another use case.  For example, let=E2=80=99s 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:
=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=
=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=
=94=E2=80=94=E2=80=94

EOQualifier qual =3D =
Artist.PAINTINGS.exists(Painting.NAME.like(=E2=80=9CV*=E2=80=9D))
    .and(Artist.PAINTINGS.exists(Painting.NAME.like(=E2=80=9CT*=E2=80=9D))=
);

var fetchSpec =3D new ERXFetchSpecification<Artist>(=E2=80=9CArtist=E2=80=9D=
, qual, null);
NSArray<Artist> artists =3D fetchSpec.fetchObjects(ec);

The qualifier qual works in memory and when fetching.

In Cayenne, I=E2=80=99m doing it as follows:
=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=
=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=
=94=E2=80=94=E2=80=94

Expression exp1 =3D exp(=E2=80=9Cpaintings.name like 'V%=E2=80=99=E2=80=9D=
);
Expression exp2 =3D exp(=E2=80=9Cpaintings.name like =E2=80=99T%=E2=80=99=E2=
=80=9D);

List<Artist> artists =3D 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=20
      WHERE
          EXISTS (
              SELECT t1.ID FROM PAINTING t1=20
              WHERE t1.NAME LIKE ? AND ( t1.ARTIST_ID =3D t0.ID )
          )=20
          AND=20
          EXISTS (
              SELECT t2.ID FROM PAINTING t2=20
              WHERE t2.NAME LIKE ? AND ( t2.ARTIST_ID =3D t0.ID )
          )=20
      [bind: 1->NAME:'M%', 2->NAME:'V%']
INFO: =3D=3D=3D 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 =3D Artist.PAINTINGS.exists(Painting.NAME.like(=E2=80=9CM%=E2=
=80=9D))
	.andExp(Artist.PAINTINGS.exists(Painting.NAME.like(=E2=80=9CV%=E2=80=
=9D)));

List<Artist> artists =3D 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 =3D =
Artist.PAINTINGS.dot(Painting.NAME).like(=E2=80=9CM%=E2=80=9D).exists()
   .andExp(Artist.PAINTINGS.dot(Painting.NAME).like(=E2=80=9CV%=E2=80=9D).=
exists());

// Fetch from database
List<Artist> artists =3D ObjectSelect
    .query(Artist.class)
    .where(exp)
    .select(oc);

// Or in memory filtering
List<Artist> artists =3D exp.filterObjects(allArtists);

Thank you,
Ricardo Parada





--Apple-Mail=_B53CF4E7-C128-4175-A9D7-92CFAD98696D--