Using SELECT DISTINCT in HQL

Question

I have been looking into private messaging database schemas and currently I am trying to build a basic db schema to store and retrieve messages - nothing too complex. This is the Message entity:

@Entity
@Table(name="messages")
class Message {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="id", unique=true, nullable = false)
    private int id;

    @ManyToOne(cascade = CascadeType.MERGE)
    @JoinColumn(name = "thread_id")
    private Thread thread_id;

    @Column(name="sent_date")
    private Date sent_date;

    @Column(name="message_body")
    private String message_body;

    @JoinColumn(name = "sender_id")
    private User sender;

    @JoinColumn(name = "receiver_id")
    private User receiver;


    // getters and setters
}

and the Thread entity is just an id :

@Entity
@Table(name="threads")
class Thread {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="id", unique=true, nullable = false)
    private int id;

    // getters and setters
}

this is the method that calls my query:

public List<Message> getDistinctMessagesForUser(int id) {
    return em.createQuery("SELECT m FROM Message m WHERE m.thread_id IN (SELECT DISTINCT m.thread_id FROM Message m WHERE (m.sender.id =:id OR m.receiver.id =:id)) ORDER BY m.sent_date DESC", Message.class)
            .setParameter("id", id)
            .getResultList();
}

I am using HQL to query the db. I am trying to get the most recent messages for each thread using a SELECT DISTINCT but I can't seem to make it work.

I tried using this :

SELECT m FROM Message m WHERE (m.sender.id =:id OR m.receiver.id =:id) GROUP BY m.thread_id ORDER BY m.sent_date DESC)

but I kept getting this error:

ERROR: column "message0_.id" must appear in the GROUP BY clause or be used in an aggregate function

I also tried the method shown in this link but the error was as a result of the fact that the query had e and m.

Every other query I tried returned either all the messages (instead of distinct) or threw the error I described above. I do not want to use CriteriaAPI as I read that it is a slower than HQL, and the other code I have is written in HQL and I plan to stick to one method. What am I missing?

Try SELECT m, m.thread_Id FROM Message m WHERE (m.sender.id =:id OR m.receiver.id =:id) GROUP BY m.thread_id ORDER BY m.sent_date DESC) — fg78nc
It gave me this error: java.lang.IllegalArgumentException: Cannot create TypedQuery for query with more than one return using requested result type [Message] — Aria
What kind of error do you get on DISTINCT? Please post generated SQLs. — fg78nc
@fg78nc I already put the error I got using DISTINCT in the question — Aria
I don't see generated SQL in your question and query with DISTINCT — fg78nc

fg78nc fg78nc · Accepted Answer · 2017-07-26T19:43:56

DISTINCT works in this case works at it should work. The problem is in your logic, as you are retrieving distinct thread entities in subquery, and then your outer query retrieves message based on result set of the subquery, but you are not getting unique 'message' entities this way, because each message entity can be associated with several even unique threads.

I suggest to change query for

List<MyMessage> myMes = em.createQuery(
                "SELECT m from MyMessage m where m.id "
                + "in (select min(m2.id) from MyMessage m2 group by m2.thread_id) "
                + "and (m.sender.id =:id OR m.receiver.id =:id)", MyMessage.class)
               .setParameter("id", 1L)

Here we retrieve first (and only first) message entity associated with each unique thread by getting the earliest message (by PK(or id) of message) in via min().

Using SELECT DISTINCT in HQL

1 Answers