phpmysqllaravel

get images list according to there message and thread id from database


i have two table one is messages and other is images, message is save in messages table and that message images save in another table with foreign key msg_id and im working on thred like one thread have multiple message and images. let me show you the pictures.

Here is Messages Table

and here is my images table

here is my images table

you see one message has 3 images and second message with same thread has 3 images also, now i want to generate a json that has two object and every object has images list array with these images,

i have include json here as an example

{
  "status": "Success",
  "code": 200,
  "ConversationList": [
    {
      "id": 1,
      "category": "Budget",
      "cat_id": 1,
      "message": "Hi how are you",
      "dateTime": "12-12-2019 10:20:20 AM",
      "user_type": "1",
      "user_id": 1,
      "ImageList": [
        {
          "image": "askdaksdjaskdjkasjdaksd"
        },
        {
          "image": "askdaksdjaskdjkasjdaksd"
        }
      ]
    },{
      "id": 1,
      "category": "Budget",
      "cat_id": 1,
      "message": "Hi how are you",
      "dateTime": "12-12-2019 10:20:20 AM",
      "user_type": "1",
      "user_id": 1,
      "ImageList": [
        {
          "image": "askdaksdjaskdjkasjdaksd"
        },
        {
          "image": "askdaksdjaskdjkasjdaksd"
        }
      ]
    }
  ]
}

what i tried is

$msg = Message::where('msg_dept_id',$request->msg_dept_id)

  ->join('images AS c', function($join){
      $join->on('messages.thread_id', '=', 'c.thread_id')
           ->on('messages.msg_id', '=', 'c.msg_id');
  })
  ->where('msg_dept_name',$request->msg_dept_name)
  ->where('messages.thread_id',$request->thread_id)
  ->orderBy('messages.msg_id', 'DESC')
  ->get();

  foreach($msg as $m)
  {
    $imagelist[] = Image::select('image')
    ->where('images.thread_id',$m->thead_id)
    ->where('images.msg_id',$m->msg_id)
    ->get();
  }

  // $data[] = $msg + $imagelist;
  // $data = json_encode($data);
  return response()->json([
    'status' => 'Success',
    'code' => 200,
    'messages' => $msg,
  ]);
}

Solution

  • this is how its done, thanks Who help me or try to Help me, and thanks(which i am really not) who discourage me by down voting

    Here is the answer

    $data['msg'] = Message::select('messages.*','u.cz_name As From_name','u1.cz_name As To_name')
            ->join('users AS u','u.id','messages.msg_from_id')
            ->join('users AS u1','u1.id','messages.msg_to_id')
            ->groupBy('thread_id')
            ->get();
            // ->toSql();
    
            foreach($data['msg'] as $key => $m)
            {
                $imagelist = Image::select('id','image','msg_image_approve')
                ->where('images.msg_id',$m->msg_id)
                ->get();
                $data['msg'][$key]->image = $imagelist;
            }