How to get latest data with groupBy in Laravel 8

SajithMadhubashana
February 17, 2023
88 views
0 votes
2 Answers

I want to make a chat app. So I can get a list of patient but the top last message doesn’t have the patient.
How to solve my problem.

PHP :

$appointment = Appointment::with([
    'upcoming.consultant' => function($query) use ($user) {
        $query->where('consultant_id', $user->consultant_id);
    },
    'patient',
    'conversation' => function($query) {
        $query->latest();
    }
])
->groupBy('patient_id')
->get('patient_id');

Result Json Data :

[
    {
        "appointment_id": 16,
        "upcoming_id": 72,
        "patient_id": 5,
        "payment_id": 22,
        "child_id": null,
        "time_slot": "2023-02-08 00:47:00",
        "is_child": 0,
        "is_presented": 0,
        "is_rejected": 0,
        "meet_at": null,
        "diseases": null,
        "comments": null,
        "created_at": "2023-02-08 22:07:01",
        "updated_at": "2023-02-08 22:07:01",
        "deleted_at": null,
        "upcoming": {
            "upcoming_id": 72,
            "consultant_id": 1,
            "package_id": 1,
            "price": "10",
            "title": "abcd",
            "start": "2023-02-08 00:47:00",
            "end": "2023-02-08 01:47:00",
            "total_appointment": 2,
            "spend_time": 10,
            "who_inserted": "3",
            "created_at": "2023-02-08 18:48:21",
            "updated_at": "2023-02-08 18:48:21",
            "deleted_at": null,
            "consultant": null
        },
        "patient": {
            "patient_id": 5,
            "salutation": "MR",
            "first_name": "Apis Technologies",
            "last_name": "#NutriInfo",
            "email": "[email protected]",
            "email_verified_at": "2023-02-08 14:28:33",
            "dob": "2023-02-08 00:00:00",
            "image": null,
            "tp": "+1 (935) 591-6866",
            "tp_verified_at": null,
            "gender": "Male",
            "country": "Afghanistan",
            "language": "si",
            "address": "Exercitation volupta",
            "city": "Matale",
            "state": null,
            "zipcode": null,
            "created_at": "2023-02-08 14:28:33",
            "updated_at": "2023-02-10 00:56:02",
            "deleted_at": null
        },
        "conversation": {
            "id": 2,
            "appointment_id": 16,
            "sender": 5,
            "receiver": 1,
            "massege": "a",
            "is_read": 0,
            "is_document": 0,
            "who_inserted": "patient",
            "document_link": null,
            "created_at": "2023-02-08 22:07:55",
            "updated_at": "2023-02-08 22:07:55",
            "deleted_at": null
        }
    }
]

With groupBy
(Actually I want this way with last message)

PHP :

$appointment = Appointment::with([
    'upcoming.consultant' => function($query) use ($user) {
        $query->where('consultant_id', $user->consultant_id);
    },
    'patient',
    'conversation' => function($query) {
        $query->latest();
    }
])->get();

Result Json Data :

[
    {
        "patient_id": 4,
        "upcoming": null,
        "patient": {},
        "conversation": null
    },
    {
        "patient_id": 5,
        "upcoming": null,
        "patient": {},
        "conversation": null
    }
]

Thank You

Answers

Chosen as BEST ANSWER

Thank you all I solved it my way..

PHP

$user = $request->user();

        $appointment = Appointment::with(
        [
            'upcoming.consultant' => function($query) use ($user){
                $query->where('consultant_id', $user->consultant_id);
            },
            'patient','conversation'
        ])
        ->has('conversation')
        ->get();

        $patientList = Appointment::with(
        [
            'upcoming.consultant' => function($query) use ($user){
            $query->where('consultant_id', $user->consultant_id);
            },
            'patient','conversation' => function($query){
                $query->latest();
            }
        ])
        ->groupBy('patient_id')
        ->get('patient_id');
        
        $list = array();
        foreach ($patientList as $key1 => $patient) {

            
            foreach ($appointment as $key2 => $row) {
                
                if($patient->patient_id == $row->patient_id){

                    $keyList = $this->searchForId($row->patient_id,$list);
                    
                    if(is_null($keyList)){
                        array_push($list,[
                            'patient'=>$row->patient,
                            'conversation' => $row->conversation
                        ]);
                    }else{
                        $currentDate = Carbon::parse($list[$keyList]['conversation']['created_at']);
                        $newDate = Carbon::parse($row->conversation->created_at);
                        // dd($newDate->gt($currentDate));
                        if ($newDate->gt($currentDate)) {
                            $list[$keyList] = [
                                'patient'=>$row->patient,
                                'conversation' => $row->conversation
                            ];
                        }
                        
                    }

                }

            }
            
        }

        foreach ($list as $key => $part) {
            $sort[$key] = strtotime($part['conversation']['created_at']);
        }
        array_multisort($sort, SORT_DESC, $list);

        return view('consultant.chat', [
            'patients' => $list
        ]);

JSON

[
  {
    "patient": {
      "patient_id": 5,
      "salutation": "MR",
      "first_name": "Apis Technologies",
      "last_name": "#NutriInfo",
      "email": "[email protected]",
      "email_verified_at": "2023-02-08 14:28:33",
      "dob": "2023-02-08 00:00:00",
      "image": null,
      "tp": "+1 (935) 591-6866",
      "tp_verified_at": null,
      "gender": "Male",
      "country": "Afghanistan",
      "language": "si",
      "address": "Exercitation volupta",
      "city": "Matale",
      "state": null,
      "zipcode": null,
      "created_at": "2023-02-08 14:28:33",
      "updated_at": "2023-02-10 00:56:02",
      "deleted_at": null
    },
    "conversation": {
      "id": 153,
      "appointment_id": 16,
      "sender": 5,
      "receiver": 1,
      "massege": "a",
      "is_read": 0,
      "is_document": 0,
      "who_inserted": "patient",
      "document_link": null,
      "created_at": "2023-02-18 16:07:55",
      "updated_at": "2023-02-18 16:07:55",
      "deleted_at": null
    }
  },
  {
    "patient": {
      "patient_id": 4,
      "salutation": "MR",
      "first_name": "Alexa",
      "last_name": "PAKAYA",
      "email": "[email protected]",
      "email_verified_at": null,
      "dob": "2013-07-24 00:00:00",
      "image": null,
      "tp": "+94772193832",
      "tp_verified_at": null,
      "gender": "Male",
      "country": "Nigeria",
      "language": "si",
      "address": "Sunt corporis offic",
      "city": null,
      "state": null,
      "zipcode": null,
      "created_at": "2023-01-04 01:13:07",
      "updated_at": "2023-02-16 09:24:37",
      "deleted_at": null
    },
    "conversation": {
      "id": 152,
      "appointment_id": 24,
      "sender": 3,
      "receiver": 4,
      "massege": "asd",
      "is_read": 0,
      "is_document": 0,
      "who_inserted": "consultant",
      "document_link": null,
      "created_at": "2023-02-18 15:18:20",
      "updated_at": "2023-02-18 15:18:20",
      "deleted_at": null
    }
  }
]

(Edit)

- Harvir
- February 17, 2023 at 10:03 am
- 0 votes
0
to retrieve a list of appointments grouped by patient ID, with the last conversation message for each patient. However, the resulting JSON data only contains patient ID, upcoming appointment details, patient details, and conversation details, but not the patient name.

To include the patient name in the resulting JSON data, you can modify your code to include the first_name and last_name fields from the patient table in the select statement
```
$appointment = Appointment::with([
        'upcoming.consultant' => function($query) use ($user){
            $query->where('consultant_id', $user->consultant_id);
        },
        'patient' => function($query) {
            $query->select('patient_id', 'salutation', 'first_name', 'last_name');
        },
        'conversation' => function($query) {
            $query->latest();
        }
    ])
    ->groupBy('patient_id')
    ->get(['patient_id']);
```
This will select only the patient_id, salutation, first_name, and last_name fields from the patient table, and include them in the resulting JSON data. You can adjust the select statement to include more fields from the patient table if needed.

Note that the resulting JSON data will only include the fields that are selected in the get method. If you need to include more fields in the JSON data, you should add them to the get method. This will include the upcoming, patient, and conversation fields in the resulting JSON data.
```
$appointment = Appointment::with([
        'upcoming.consultant' => function($query) use ($user){
            $query->where('consultant_id', $user->consultant_id);
        },
        'patient' => function($query) {
            $query->select('patient_id', 'salutation', 'first_name', 'last_name');
        },
        'conversation' => function($query) {
            $query->latest();
        }
    ])
    ->groupBy('patient_id')
    ->get(['patient_id', 'upcoming', 'patient', 'conversation']);
```
Login or Signup to reply.

Please signup or login to give your own answer.

Click here to cancel reply.