(Messages) Use HQL for message conversation query

Registered by Lars Helge Øverland on 2016-04-05

Rewrite the query for message conversations in HibernateMessageConversationStore.getMessageConversations to use HQL.

Add a new persisted property int messageCount on MessageConversation, which must store the current number of messages. The purpose of this improved performance in the query / having to calculate on the fly.

Using HQL will allow for field filtering, caching and easier searching.

Blueprint information

Status:
Complete
Approver:
Lars Helge Øverland
Priority:
Medium
Drafter:
Lars Helge Øverland
Direction:
Approved
Assignee:
Stian Sandvold
Definition:
New
Series goal:
Accepted for trunk
Implementation:
Implemented
Milestone target:
milestone icon 2.24
Started by
Stian Sandvold on 2016-05-02
Completed by
Stian Sandvold on 2016-05-24

Related branches

Sprints

Whiteboard

Remember to write SQL for counting messages

update count when object is updated, or when addMessage/removeMessage is called.

SQL run at startup: Checks for any null values (which should be non-existant?) and if there are more than 0, we do the counting of messages;

    private void updateMessageConversationMessageCount() {

        Integer nullCounts = statementManager.getHolder().queryForInteger( "SELECT count(*) from messageconversation WHERE messagecount IS NULL" );

        if(nullCounts > 0)
        {
            // Count messages in messageConversations
            executeSql(
                "update messageconversation MC SET messagecount = (SELECT count(MCM.messageconversationid) FROM messageconversation_messages MCM WHERE messageconversationid=MC.messageconversationid) " );
        }
    }

Replaced SQL with HQL, results appear to be identical in both types of query. Also now dont subquery for message count.
added counter for messageCount
added messageCount, removed it as a transient variable.

Tested getting messageConversations and adding messages, both the list of message conversations and the counter for messages seems to work as intended.

Test out how to better fetch the data:
we only need 1 usermasse, how can we avoid loading more later? (eager loading)
Projection(ref: http://stackoverflow.com/questions/25536868/criteria-distinct-root-entity-vs-projections-distinct)
Filtering data (ref: https://docs.jboss.org/hibernate/orm/3.6/reference/en-US/html/filters.html)
Add parameter injection to query!

*** UPDATES ***
Added filtering to the hql, can't use the User class directly for filtering, as it seems the filtering uses SQL to apply the condition, or has some other limitation that makes this impossible. Instead, the filter takes a user's id (user.getId()) and compares to the userid column in usermessages.

Managed to fetch the objects required without any special post-mapping and stuff like that like we had to do when I discussed with Lars. Because of how the query now works, I believe Projection and Eager loading is no longer relevant, as it appears we naturally get unique results from the join, and we get all the usermessages as hibernate objects, which leads me to believe they are also cached by hibernate.

Add unit test!

(?)

Work Items

This blueprint contains Public information 
Everyone can see this information.

Subscribers

No subscribers.