vendredi 8 mai 2015

How to query a one-to-many table based on a condition on a date field?

I have Client table with ont-to-many with Comment table as the following:

Client table:

Id name status....

Comment table:

Id description created_date client_id

this is what i tried:

    @Entity
@Table(name = "client")
public class Client implements Serializable {
/** The comments. */
    private Set<Comment> comments = new HashSet<Comment>(0);
@OneToMany(fetch = FetchType.EAGER, mappedBy = "client", cascade = CascadeType.ALL)
    @OrderBy(clause = "createdDate")
    //@Fetch(FetchMode.SELECT)
    public Set<Comment> getComments() {
        return comments;
    }
}
@Entity
@Table(name = "comment")
public class Comment implements Serializable {

    /** The created date. */
    private Date createdDate;

@ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "CLIENT_ID", nullable = false)
    public Client getClient() {
        return client;
    }

    /**
     * Sets the client.
     *
     * @param client
     *            the new client
     */
    public void setClient(Client client) {
        this.client = client;
    }

}

    public List<Client> getLastEditedDateFromComment() {
        try {
            Calendar cal = Calendar.getInstance();
            //cal.add(Calendar.HOUR_OF_DAY, 2);
            cal.add(Calendar.DATE, -7);
            //.add(Restrictions.le("comments.editedDate", cal.getTime()))
            //System.out.println("ClientDAO.getLastEditedDateFromComment()"+cal.getTime());
            startOperation();
            Criteria criteria = session
                    .createCriteria(Client.class)
                    .createAlias("comments", "comments")
                    .add(Restrictions.eq("status", "Pending"))
                    .add(Restrictions.le("comments.createdDate", cal.getTime()))
                    .setProjection(
                            Projections.distinct(Projections.projectionList()
                                    .add(Projections.groupProperty("clientName"))
                                    .add(Projections.groupProperty("id"))
                                    .add(Projections.groupProperty("status"))
                                    .add(Projections.property("clientName"), "clientName")
                                    .add(Projections.property("id"), "id")
                                    .add(Projections.property("status"), "status")
                                    .add(Projections.max("comments.createdDate"))));
            // .setProjection(Projections.max("comments.editedDate"));
            // Integer maxAge = (Integer)criteria.uniqueResult();
            List<Client> clist = criteria.setResultTransformer(Transformers.aliasToBean(Client.class))
                    .list();
            tx.commit();
            return clist;
    }
    }

the above code generates:

select distinct this_.clientName as y0_, this_.ID as y1_, this_.status as y2_, this_.clientName as y3_, this_.ID as y4_, this_.status as y5_, max(comments1_.createdDate) as y6_ from zyhnavkp.client this_ inner join zyhnavkp.comment comments1_ on this_.ID=comments1_.CLIENT_ID where this_.status=? and comments1_.createdDate<=? group by this_.clientName, this_.ID, this_.status

I need a query to get all clients with the created_date passed 1 week ONLY from comment table where status ='Pending', I'm trying to do this hibernate, any sql query is acceptable :)

Thanks in advance :)

Aucun commentaire:

Enregistrer un commentaire