Sorting messages for different users from database

Question!

I am working in a Spring-MVC application with Hibernate and Postgres. In the application I am adding chat functionality. I am using a very simple database schema for saving chats. In the schema, for the table Messages, there are 2 foreign keys, referred to table User as sender and receiver. I am currently able to send and receive messages, but the problem is I want to club the messages into conversations between users. I have timestamp to sort the messages by time, but I don't know how to group them by the other user(reciever name) and display them in the front end(HTML)

I am posting some of the code and schema, kindly have a look :

SQL code :

CREATE TABLE public.userinfo (
                id INTEGER NOT NULL,
                email VARCHAR,
                username VARCHAR,
                displayname VARCHAR,
                password VARCHAR,
                CONSTRAINT id PRIMARY KEY (id)
);


CREATE TABLE public.conversation (
                conversation_id NUMERIC NOT NULL,
                messagetext VARCHAR,
                time DATE,
                id INTEGER NOT NULL,
                userinfo_id INTEGER NOT NULL,
                CONSTRAINT conversation_id PRIMARY KEY (conversation_id)
);



ALTER TABLE public.conversation ADD CONSTRAINT userinfo_conversation_fk
FOREIGN KEY (id)
REFERENCES public.userinfo (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE;

ALTER TABLE public.conversation ADD CONSTRAINT userinfo_conversation_fk1
FOREIGN KEY (userinfo_id)
REFERENCES public.userinfo (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE;

MessagesDAOImple :

@Override
    public boolean addMessage(User sender, User receiver, Messages messages) {
        if( session == null) {
            session = this.sessionFactory.openSession();
        }else {
            session = this.sessionFactory.getCurrentSession();
        }
        int id = sender.getId();
        User sender1 = (User) session.get(User.class,id);
        sender.getMessages1().add(messages);
        receiver.getMessages2().add(messages);
        session.saveOrUpdate(messages);
        session.flush();
        return true;
    }

@Override
    public List<Messages> listMessagesForUser(User user) {
        if(session == null){
            session = this.sessionFactory.openSession();
        } else{
            session = this.sessionFactory.getCurrentSession();
        }
        int id = user.getId();
        Query query = session.createQuery("from Messages as m where m.user2.id=:id");
        query.setParameter("id",id);
        List<Messages> messagesList= query.list();
        return messagesList;

    }

I am still writing the html code, so I don't have it right now, but if there is a possibility of using something easy like c:forEach and displaying the messages as list for testing grouped by conversations is what I am looking for. Any pointers or suggestions would be nice. Thank you.



Answers

I'm not sure what you're looking for.

Here is some SQL to return a conversation between two users (123



This video can help you solving your question :)
By: admin