I have 2 tables, Partner
and Visit
. A partner can have many visits, also various in same day.
I need to create a Criteria
restriction to know how many days the Partner
has visits since a given date. So, 2 or more visits in same date must be only one.
Can this be done only by Criteria
and Restrictions
??
I can get all visits from a date with a criteria like:
Criteria criteria = buildCriteria();
criteria.add(Restrictions.eq(DBConstants.VISIT_COL_VISITOR, partnerVisitor));
criteria.add(Restrictions.ge(DBConstants.VISIT_COL_DATE, startDate));
But now, to filter repeated days, I need something like:
criteria.add(Restrictions.unique(DBConstants.VISIT_COL_DATE));
Any idea?
EDIT: @user23123412 @netik
Visit.java
private Integer id;
private Date date;
private Partner visitor;
// getters + setters
Visit
table rows related to partner 1:
ID | VISITOR | DATE |
---|---|---|
1 | 1 | 10/10/2014 16:20 |
20 | 1 | 10/10/2014 18:00 |
45 | 1 | 12/10/2014 16:20 |
71 | 1 | 12/10/2014 19:40 |
89 | 1 | 16/10/2014 11:20 |
The answer I need after the query is a Visit
count in different days since a given date
.
I.E.: If i launch a query with visitor = 1
and startDate = 12/10/2014
the result MUST be 2, cause row id = 45
and id = 71
have different visits in a same day, so it's only ONE day.
You have three options:
1) If you are not pinned to Critera api, I recommend to use HQL instead of Criteria API
2) If you want to use Criteria API, it's possible to apply sqlRestriction. Unfortunately you will lock to specific database. This example works on HSQLDB
3) It's also possible to use pure criteria API, but date restriction must be a little bit hacked (using between restriction)