zwwcn

Just another WordPress.com site

Monthly Archives: December 2014

order by before group by in mysql

We have two entities: Activity 1:n Conversation. I need to get the latest conversation for each of the activity. The MySql doesn’t allow me to put order by before the group by.
I found a workaround from this :

So I get something like:
SELECT * FROM ( SELECT * FROM OP_TICKET_CONVERSATION WHERE ACTIVITY_ID IN (ticketsIds) ORDER BY OBJECT_ID DESC) AS tc GROUP BY tc.ACTIVITY_ID

This works but hibernate returns Object type, we need to specify the root entity:

this could be done by adding “addEntity”.

List conversationsList = em.createSQLQuery(sqlString).addEntity(TicketConversation.class).list();

Advertisements

fetch join collection break firstResult/MaxResult in hibernate

If a fetch join is used along with order by, hibernate will load all records into memory to do the sorting. It will ignore the firstResult/MaxResult which mean no real database pagination.

em.createQuery("SELECT a FROM TicketTracker tt
LEFT JOIN tt.ticket a
LEFT JOIN FETCH a.conversation tc
WHERE a.status!=400 GROUP BY tt.ticket ORDER BY a.dueDate", TicketActivity.class)

.setFirstResult(0).setMaxResults(10).list();

In this example, even we didn’t order records by the join entity, the join fetch still break the setFirstResult/SetMaxResult . I have to write a seperate query to fetch the conversations records.