Expression for all lineitems that where picked today

[philk]

Hello,

I am struggling constructing my EL expression for the following:
"Select the first lineitem that was picked today"

final ExpressionBuilder eb = new ExpressionBuilder();
eb.get("pickedAt").greaterThanEqual(eb.addDate("day", 0)).and(eb.lessThan(eb.addDate("day", 1)))
                        .minimum();
ReadObjectQuery query = new ReadObjectQuery(LegacyOrderPosition.class);
query.setSelectionCriteria(eb);
final Object first = JpaHelper.createQuery(query, em).getSingleResult();

But this will not return me the expected entity, but rather some random entity that does not even have the "pickedAt" member set (its null).

How would I compose that query and how could I use the database provided DATE() method rather than the to give the date from the client using "new Date()".

Regards,
Phil

_______________________________________________
eclipselink-users mailing list
eclipselink-users@...
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

[James Sutherland]

The function addDate() takes a date as argument, not the expression builder.  minimum() is an aggregation function, so can only be used in the SELECT clause.  You must set the selectionCriteria to the resulting Expression not the expression builder.

Try something like,

final ExpressionBuilder eb = new ExpressionBuilder();
Expression expression = eb.get("pickedAt").greaterThanEqual(eb.today().addDate("day",
0)).and(eb.lessThan(eb.today().addDate("day", 1)));
ReadAllQuery query = new ReadAllQuery(LegacyOrderPosition.class);
query.setSelectionCriteria(expression);
query.addAscendingOrder("pickedAt");
query.setMaxRows(1);
final Object first = JpaHelper.createQuery(query, em).getResultList().get(0);

Or use a sub select,

ExpressionBuilder subBuilder = new ExpressionBuilder();
Expression subExpression = subBuilder.get("pickedAt").greaterThanEqual(subBuilder.today().addDate("day",
0)).and(subBuilder.lessThan(subBuilder.today().addDate("day", 1)));
ReportQuery subQuery = new ReportQuery(LegacyOrderPosition.class);
subQuery.setSelectionCriteria(expression);
subQuery.addMinimum("pickedAt");

ReadObjectQuery query = new ReadObjectQuery(LegacyOrderPosition.class);
ExpressionBuilder eb= new ExpressionBuilder();
query.setSelectionCriteria(eb.get("pickedAt").equal(subQuery));

Object first = JpaHelper.createQuery(query, em).getSingleResult();

philk wrote:

Hello,

I am struggling constructing my EL expression for the following:
"Select the first lineitem that was picked today"

final ExpressionBuilder eb = new ExpressionBuilder();
eb.get("pickedAt").greaterThanEqual(eb.addDate("day",
0)).and(eb.lessThan(eb.addDate("day", 1)))
                        .minimum();
ReadObjectQuery query = new ReadObjectQuery(LegacyOrderPosition.class);
query.setSelectionCriteria(eb);
final Object first = JpaHelper.createQuery(query, em).getSingleResult();

But this will not return me the expected entity, but rather some random
entity that does not even have the "pickedAt" member set (its null).

How would I compose that query and how could I use the database provided
DATE() method rather than the to give the date from the client using "new
Date()".

Regards,
Phil

_______________________________________________
eclipselink-users mailing list
[e-mail]
https://dev.eclipse.org/mailman/listinfo/eclipselink-users