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
<[email protected]> 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
