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 Tue, 28 Nov 2017 03:45:14 GMT
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