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.varsc...@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 <
jmdia...@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
> <jmdia...@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
>
