Laravel format query json result

Question

I have a query that looks like this:

$messages = Conversation::leftJoin('v_sendermessages', 'v_conversations.con_id', '=', 'v_sendermessages.sm_c_id')
                                   ->leftJoin('v_recipientmessages', 'v_conversations.con_id', '=', 'v_recipientmessages.rm_c_id')
                                   ->where('con_id', $conId)
                                   ->select('v_conversations.sender_id', 'v_conversations.recipient_id', 'v_sendermessages.sender_message', 'v_sendermessages.date_sent', 'v_recipientmessages.recipient_message', 'v_recipientmessages.date_answered')
                                   ->get();

and the output will be:

{
  "messages": [
    {
      "sender_id": 2,
      "recipient_id": 1,
      "sender_message": "Asdfasdfasdf",
      "date_sent": 1477664894,
      "recipient_message": "Sdfsdfsdf",
      "date_answered": 1477665242
    },
    {
      "sender_id": 2,
      "recipient_id": 1,
      "sender_message": "Asdfasdfasdf",
      "date_sent": 1477664894,
      "recipient_message": "Ju",
      "date_answered": 1477665442
    },
    {
      "sender_id": 2,
      "recipient_id": 1,
      "sender_message": "Asdfasdfasdf",
      "date_sent": 1477664894,
      "recipient_message": "Sdafasdf",
      "date_answered": 1477666240
    }
  ]
}

But how can I modify the above query to output json that looks like this:

{
  "senderMessages": [
    {
    *sender messages listed here*
}],
"recipientMessages": [
{
    *recipient messages listed here*
]}

I use this query to show a conversation, so it would be much easier to list all sender messages in one section and all recipient in another section

@AmitGupta v_conversations PK is: con_id. v_sendermessages.sm_c_id is a FK that points to that key, and v_recipientmessages.rm_c_id is a FK that points to that key as well — user2636197
– user2636197, Commented Oct 28, 2016 at 18:44

alex-greaves · Accepted Answer · 2016-10-28 20:12:30Z

I don't think you're going to be able to obtain the result you want as a direct result from one query. You'd be able to format the data you've already returned relatively easily. But if you were determined to not format the data you could simply run two queries, one to retrieve the sender and one to retrieve the recipient:

$messages['senderMessages'] = Sender::where('conversation_id', $conversationId)->get();
$messages['recipientMessages'] = Recipient::where('conversation_id', $conversationId)->get();

However I don't think there is any point in doing so, as long as you know the sender and the conversation, e.g. given a conversation by users A and B:

A: 'Hello'

B: 'Hi'

A: 'Fine weather we're having'

B: 'Yes it is'

In this case your current setup is registering 'Hi' as a response to 'Hello' and 'Yes it is' as a response to 'Fine weather we're having', almost like they're two separate conversations. This poses problems where if a user sends 2 messages before the other user responds e.g.

A: 'Hi'

A: 'How are you?'

B: 'Hello'

Does it register user B's response as a response to the first or second message? Could that cause problems?

As long as you know the timestamp a message was sent, who sent it and what conversation it was a part of (this can be found with the conversation_id you're using or just based off of the recipient_id), there is no need for a recipient message. You can delete the recipient message table entirely and rename 'v_sendermessages' to 'messages' then find messages with the conversation id specified.

As you have a sender_id you can handle the users differently, I'm not sure exactly what result you want but if it's so you can put user A on the left and B on the right that's completely doable as you can see their ids are different. Also this could change your query to a simple:

$messages = Message::where('conversation_id', $conversationId)->get();

Collectives™ on Stack Overflow

Laravel format query json result

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related