Sort by relationship first in laravel

Question

I have two tables: admins and log_doctor_infos. admins table has relationship hasOne with log_doctor_infos throught doctor_id like this.

In model Admin:

public function logDoctorInfo() {
    return $this->hasOne(LogDoctorInfo::class, 'doctor_id', 'id');
    // Model LogDoctorInfo is log_doctor_infos table
}

And in Model LogDoctorInfo:

public function doctor(){
    return $this->belongsTo(Admin::class, 'doctor_id', 'id');
    // Model Admin is admins table
}

I get all data form admins table and i want to sort record has relationship with log_doctor_infos to top.

Yellow record, which has relationship with log_doctor_infos and i want to sort it in top.

Edit: i use paginate in this query and i really want to get quantity of Yellow record.

Thanks for reading!

In my controller, i have custom filter and paginate. Help me.

public function index(Request $request) {
    $fullname = $request->query('fullname', NULL);
    $phone = $request->query('phone', NULL);
    $status = $request->query('status', NULL);

    $doctors = (new Doctor)->newQuery();
    if ($fullname != NULL) {
        $doctors = $doctors->where('fullname', 'LIKE', '%'.$fullname.'%');
    }
    if ($phone != NULL) {
        $doctors = $doctors->where('phone', 'LIKE', '%'.$phone.'%');
    }
    if ($status != NULL) {
        $doctors = $doctors->where('status', $status);
    }
    $doctors = $doctors
    // ->with(array('logDoctorInfo' => function($query) {
    //     $query->orderBy('updated_at', 'ASC');
    // }))
    ->latest()
    ->paginate()
    ->appends([
        'fullname' => $fullname,
        'phone' => $phone,
        'status' => $status
    ]);
    // dd($doctors);
    return view('admin.doctors.index', compact('doctors'));
}

N69S · Accepted Answer · 2019-01-25T08:06:03.743

3

you can use the withCount method.

Admin::withCount('logDoctorInfo')
       ->orderBy('log_doctor_info_count', 'desc')
       ->paginate(5);

Your controller will look like this

public function index(Request $request) {
    $fullname = $request->input('fullname', NULL);
    $phone = $request->input('phone', NULL);
    $status = $request->input('status', NULL);

    $doctorQuery = Doctor::query();
    if ($fullname) {
        $doctorQuery->where('fullname', 'LIKE', '%'.$fullname.'%');
    }
    if ($phone) {
        $doctorQuery->where('phone', 'LIKE', '%'.$phone.'%');
    }
    if ($status) {
        $doctorQuery->where('status', $status);
    }
    $doctorQuery->withCount('logDoctorInfo')
        ->orderBy('log_doctor_info_count');

    $doctors = $doctorQuery->paginate()
        ->appends([
            'fullname' => $fullname,
            'phone' => $phone,
            'status' => $status
        ]);
    // dd($doctors);
    return view('admin.doctors.index', compact('doctors'));
}

edited Jan 25 '19 at 08:06

answered Jan 24 '19 at 09:24

N69S

16,110
3
22
36

Its worked, but i don't know how to use your code with paginate and custom filter like my controller. Can you help me? – Ngoc Tran Jan 24 '19 at 09:56
1

you have to replace get() method to paginate(5). – Jinal Somaiya Jan 24 '19 at 09:57
It's not really work. I dont have logDoctorInfo_count like in your code ->orderBy('logDoctorInfo_count', 'desc'). – Ngoc Tran Jan 24 '19 at 10:26
1

Eloquent creates that field `relation_name_count` when you use `withCount('relationName')` method. As Jinal said, just replace `get()` with `paginate()`. i updated my answer with an example – N69S Jan 24 '19 at 14:03
Thank you so much. One more question: in view, how can i count quantity of yellow row? – Ngoc Tran Jan 24 '19 at 16:31
1

you do `$doctors->where('relation_name_count', '>', 0)->count()`. `where()` and `count()` are valid method on Laravel Collection Objects. but that will only give you the count in the current page. if you want the count of "yellow" elements in the DataBase, you need to run a count query – N69S Jan 25 '19 at 08:03

score 2 · Answer 2 · answered Jan 24 '19 at 09:23

2

Doctor::with('logDoctorInfo')->get()->sortByDesc('logDoctorInfo.id');

answered Jan 24 '19 at 09:23

EXayer

145
1
9

Its worked. But when i use paginate, its not work. Its say: "Method paginate does not exist." Help me? – Ngoc Tran Jan 24 '19 at 09:34
Try - `Doctor::with('logDoctorInfo')->paginate(10)->sortByDesc('logDoctorInfo.id'); ` – EXayer Jan 25 '19 at 11:42
Its worked. But paginate can't work. It say `Method links does not exist.` in line `{{ $doctors->links('admin.paginations.pagination_sm') }}` – Ngoc Tran Jan 26 '19 at 02:12

Sort by relationship first in laravel

2 Answers2