Hi,
We may need to make it more obvious in the API, but SQLSelect only supports
JOINT prefetching [1]. It can't support disjoint, as Cayenne has no way of
building the right WHERE clause for prefetch queries. It can theoretically
support DISJOINT_BY_ID, but IIRC it does not.
So let's focus on JOINT... Unlike ObjectSelect that does all the prefetch SQL
building for you transparently, SQLSelect can't, so it is your responsibility
to include all the columns from the main and related entities in your result
set, and also (and this is important) to label all these columns properly, so
that Cayenne could figure out which objects they belong to. Here is an example:
List<Artist> objects = SQLSelect.query(Artist.class, "SELECT "
+ "#result('ESTIMATED_PRICE' 'BigDecimal' ''
'paintings.ESTIMATED_PRICE'), "
+ "#result('PAINTING_TITLE' 'String' '' 'paintings.PAINTING_TITLE'), "
+ "#result('GALLERY_ID' 'int' '' 'paintings.GALLERY_ID'), "
+ "#result('PAINTING_ID' 'int' '' 'paintings.PAINTING_ID'), "
+ "#result('ARTIST_NAME' 'String'), "
+ "#result('DATE_OF_BIRTH' 'java.util.Date'), "
+ "#result('t0.ARTIST_ID' 'int' '' 'ARTIST_ID') "
+ "FROM ARTIST t0, PAINTING t1 "
+ "WHERE t0.ARTIST_ID = t1.ARTIST_ID")
.addPrefetch(Artist.PAINTINGS.joint())
.select(context);
Here the result includes all the Artist columns (root entity), and all the
Painting columns (prefetched entity). Painting columns are labeled in the
format "[dbrelationship].[columnName]" (e.g. "paintings.ESTIMATED_PRICE").
HTH,
Andrus
[1] https://cayenne.apache.org/docs/4.1/cayenne-guide/#prefetching-semantics
> On Jul 22, 2020, at 11:28 PM, Jorge Gonçalves
> <jorge.fern.goncal...@gmail.com> wrote:
>
> Hello,
>
> I have to do some complex queries to data base and to do that on I'm writing
> them in SQL.
>
> It works great with SQLSelect but when I need to prefetch something ( to
> avoid hundreds of queries when showing data in a tableView) it does not work,
>
> from javaDoc it seems that prefetching with SQLSelect is possible but I have
> not found any info or example.
>
> for example if i do:
>
> SelectQuery<Servico> query = new SelectQuery<>(Servico.class);
>
> query.addPrefetch("table1");
> query.addPrefetch("table3");
> query.addPrefetch("table4");
>
> getContext().performQuery(query);
>
> cayenne makes 4 queries and all data is displayed on table.
>
> but if I run:
>
> SQLSelect<Servico> query = new SQLSelect<>(Servico.class, "select * from
> servico;");
>
> query.addPrefetch("table1", PrefetchTreeNode.UNDEFINED_SEMANTICS);
> query.addPrefetch("table3", PrefetchTreeNode.UNDEFINED_SEMANTICS);
> query.addPrefetch("table4", PrefetchTreeNode.UNDEFINED_SEMANTICS);
>
> getContext().performQuery(query);
>
> it makes hundreds of queries when trying to show data on table (like if no
> prefetch was made), I've tried to make a join but does not work either.
>
