cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Juan Manuel Diaz Lara <jmdia...@yahoo.com.INVALID>
Subject Re: Problem using to-many relationship on where clause
Date Wed, 29 Nov 2017 06:17:36 GMT
I created a new project, with entities Products -toMany-> StockcurrentCorporativo, with
same results.
Tracing code, I arrived to DefaultSelectTranslator#isUnsupportedForDistinct, this is the cause
for cayenne not including a DISTINCT in generated SQL, because I have columns mapping to types
"not supported for distinct clause". But I found a note on method DefaultSelectTranslator#isSuppressingDistinct
that tell that cayenne should should do an in-memory  distinct operation. so this is not
working well (a bug ?).
At least I conclude that:
1. Cayenne is deciding with types of columns can be used for DISTINCT, but this is not generally
true, some db's ignore not sortable columns when used in DISTINCT queries. Maybe this decision
should be delegated to the specific db translator. 

2. There is a bug because cayenne must perform the in-memory distinct when suppressing DISTINCT
in generated SQL.
I removed my longvarbinary columns and the problem gone, a DISTINCT SQL was generated, confirming
the previous conclusions.
Well, this was a try, in reality, I can not simply remove my longvarbinary columns.
Does some body can report this as a bug ?.
Thanks.


Atte. Juan Manuel Díaz Lara 

    On Tuesday, November 28, 2017, 2:04:43 PM CST, Juan Manuel Diaz Lara <jmdiazlr@yahoo.com.INVALID>
wrote:  
 
 I am agree that this kind of query should generate a SQL DISTINCT, but the fact is that adding
a joint()brings only one product on result even the sql generates 16 rows, so Cayenne is doing
some kind of IN-MEMORY DISTINCT that is not done without a joint(), maybe a bug because there
are two different executions paths.  
I will create a new mapping with only this two tables to recheck is not a problem of model
or any other mistake. I'll report results.
thanks.

Atte. Juan Manuel Díaz Lara 

    On Tuesday, November 28, 2017, 11:42:14 AM CST, Lon Varscsak <lon.varscsak@gmail.com>
wrote:  
 
 I think the part that's missing is a "distinct".  I'm assuming there are 4
stockcurrent_corporativo rows for that one product, but if it had a
distinct it would be showing one.  I don't think that
ObjectSelect/FluentSelect have the ability to turn on/off distinct, but for
me, this always generates a "distinct" in the query.  I'm not sure why it
would be different for you.

-Lon

On Mon, Nov 27, 2017 at 8:45 PM, Juan Manuel Diaz Lara <
jmdiazlr@yahoo.com.invalid> wrote:

> More details, this code should return only one product entity, but
> produces a result list with 4 items, all items being the same product
> entity (the purpose is get products which have stock in at leas one
> location):
>
>
> ObjectSelect<Product> q = ObjectSelect.query(Product.class)
> .where(Product.EXISTENCIAS_CORPORATIVAS.dot(StockCurrentCorporativo.UNITS).gt(0.0))
> .and(Product.CODE.like("USBVGA");List<Product> results =
> q.select(context);
>
> (this is could be expressed in plain SQL as a correlated query inside an
> EXISTS operation over table StockCurrentCorporativo)
> Generates :
>
> SELECT <<all columns from t0 table>>
> FROM public.products t0 JOIN public.stockcurrent_corporativo t1 ON (t0.id
> = t1.product) WHERE (t1.units > ?) AND (t0.code LIKE ?)    ORDER BY
> t0.name [bind: 1->units:0.0, 2->code:'USBVGA']
>
> This produces 4 identical rows form database that end up on only one
> product entity but repeated 4 times on results list (results.size() = 4).
> Changig the query to
>
> ObjectSelect<Product> q = ObjectSelect.query(Product.class)
> .where(Product.EXISTENCIAS_CORPORATIVAS.dot(StockCurrentCorporativo.UNITS).gt(0.0))
> .and(Product.CODE.like("USBVGA");q.prefetch(Product.
> EXISTENCIAS_CORPORATIVAS.joint()); // <-- change
> List<Product> results = q.select(context);
>
>
> produces this SQL:
>
> SELECT <<all columns from t0 table>> FROM public.products t0 LEFT JOIN
> public.stockcurrent_corporativo t1 ON (t0.id = t1.product) JOIN
> public.stockcurrent_corporativo t2 ON (t0.id = t2.product) WHERE
> (t2.units > ?) AND (t0.code LIKE ?) ORDER BY t0.name [bind: 1->units:0.0,
> 2->code:'USBVGA']
>
>
> note that the two references to the relationship traslate to two joins.
> This SQL query produces 16 identical rows!!, that end up in only one
> product entity, but surprisingly, results list have only one item
> (product), and this is what is expected !!.
> All this is annoying, or maybe I am not understanding well the query rules
> of cayenne.
> Yes, I can make the query with SQLTemplate, but would like to use only
> object oriented queries. Could it be done ? In general. how to include
> conditions over the many size of relationships ?.
> Please help.
>
> Atte. Juan Manuel Díaz Lara
>
>    On Sunday, November 26, 2017, 8:57:50 PM CST, Juan Manuel Diaz Lara
> <jmdiazlr@yahoo.com.INVALID> wrote:
>
>  I am using 4.0.M5, previously this query worked as I expected:
>
>                  ObjectSelect<Product> q = ObjectSelect.query(Product.class)
>              .where(Product.EXISTENCIAS_CORPORATIVAS.dot(
> StockCurrentCorporativo.UNITS).gt(0.0))
> .and(Product.CODE.like("USBVGA");
>                List<Product> results = q.select(context);
>
> The objective is to get products for which we have stock in any location,
> StockCurrentCorporativo has the stock by location.
> I restricted the example query to just one specific product and expect to
> get only one product in results, but got results.size() > 1, with all items
> being the same product (the same object on my tests).
>
> The generated SQL from the log is:
>    SELECT <<all columns from t0 table>>
>    FROM public.products t0 JOIN public.stockcurrent_corporativo t1 ON (
> t0.id = t1.product) WHERE (t1.units > ?) AND (t0.code LIKE ?)    ORDER
> BY t0.name [bind: 1->units:0.0, 2->code:'USBVGA']
>
>
> EXISTENCIAS_CORPORATIVAS is a toMany relationship from Product and have
> type StockCurrentCorporativo, defined in _Product as :
>
>      public static final Property<List<StockCurrentCorporativo>>
> EXISTENCIAS_CORPORATIVAS = Property.create("existenciasCorporativas",
> List.class);
>
>
>  To be fair, I do not know when this started, the production app is
> working well, this is on a my development environment when I started to
> work for a new requirement.
> I reviewed the mapping and it looks ok, any help will be appreciated.
>
> Atte. Juan Manuel Díaz Lara
>    
Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message