Re: SQLSelect and Prefetching (cayenne 4.1)
> 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 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 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 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
>> 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 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 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.
>>
>
Re: SQLSelect and Prefetching (cayenne 4.1)
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 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
> 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 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 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.
>
SQLSelect and Prefetching (cayenne 4.1)
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 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 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.
