> It can theoretically support DISJOINT_BY_ID, but IIRC it does not.
Actually I was wrong about this one. DISJOINT_BY_ID works just fine with
SQLSelect, and will save you a lot of trouble mapping SQL:
List<Artist> result = SQLSelect.query(Artist.class, "SELECT "
+ "#result('ARTIST_NAME' 'String'), "
+ "#result('DATE_OF_BIRTH' 'java.util.Date'), "
+ "#result('t0.ARTIST_ID' 'int' '' 'ARTIST_ID') "
+ "FROM ARTIST t0")
.addPrefetch(Artist.PAINTINGS.disjointById())
.select(context);
I'll make sure we update the docs.
Andrus
> On Jul 30, 2020, at 9:11 AM, Andrus Adamchik <[email protected]> wrote:
>
> 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
>> <[email protected]> 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.
>>
>
