mysql - How to refine SQL query results to accommodate a view

Question

I am attempting to build a messaging application in Laravel 8 with two tables: conversations and messages. On the front end, the left column lists each conversation, and when clicked the content of the conversation displays. Everything works with one nagging exception, I cannot figure out how to query for conversations only and yet have the message data I need to work with.

I join the two tables to get the data I need, however, the query returns an element for each message. If I change the query to return only conversations where the users ID is sender or receiver, I get what I want, but not the data to populate the template.

Here is the query and results to get the conversations a user has participated in as either sender or receiver:

    public function justConversations($id)
    {
        return DB::table('conversations')
        ->where('conversations.party_one', '=', $id)
        ->orWhere('conversations.party_two', '=', $id)
        ->get(); 
    }

The query with the data I need returns an element for each message, in this case two messages means the conversation displays two times versus what I want, which is once. Using distinct doesn't work because each message is different. Here's the query and the results I need to use:

    public function getAllMyConversations($id)
    {  
        return DB::table('conversations')
            ->join('messages', 'messages.conversation_id', '=', 'conversations.id')
            ->select(
                'conversations.id AS message_id',
                'conversations.party_one',
                'conversations.party_two',
                'conversations.party_one_name',
                'conversations.party_two_name',
                'conversations.created_at AS conversation_created_at',
                'messages.conversation_id',
                'messages.created_by',
                'messages.sent_to',
                'messages.created_by_name',
                'messages.sent_to_name',
                'messages.content',
                'messages.read_at_created_by',
                'messages.read_at_sent_to',
                'messages.created_at AS message_created_at',
            )
            ->where('conversations.party_one', '=', $id)
            ->orWhere('conversations.party_two', '=', $id)
            ->get(); 
    }

My approach might be incorrect. I'm at a dead-end. I hope this makes sense. I would appreciate any assistance in identifying my mistakes.

question from:https://stackoverflow.com/questions/65600681/how-to-refine-sql-query-results-to-accommodate-a-view

Answer 1

Waitting for answers