问题描述

如何使用相关表格对结果进行排序?

How can I sort my result with related tables?

我有这个表:Clients 和 Managers(用户表)

I have this tables: Clients and Managers (users table)

Client.php

<?php

class Client extends Eloquent {
    protected $table = 'clients';

    public function Manager(){
        return $this->belongsTo('User','manager','id');
    }
    public function Transaction(){
        return $this->hasMany('ClientTransaction','client','id');
    }
}

Users.php(默认 Laravel 的模型)

Users.php (default Laravel's model)

我的问题是如何查询要按经理姓名排序的表客户端.

My question is how can I query table clients to be sorted by Manager's name.

到目前为止，这是我的代码:

Here is my code so far:

public function getClients() {
    // Sorting preparations
    $allowed    = array('name','contact','money');
    $sort       = in_array(Input::get('sort'), $allowed) ? Input::get('sort') : 'name';
    $order      = Input::get('order') === 'desc' ? 'desc' : 'asc';
    // Get Clients from DB
    if($sort == 'contact'){
        $clients    = Client::with(array('Manager' => function($query) use ($order) {
            $query->orderBy('name', $order);
        }));
    } else {
        $clients    = Client::orderBy($sort, $order)->with('Manager');
    }
    // Filters
    $cname      = null;
    if(Input::has('cname')){
        $clients    = $clients->where('name', Input::get('cname'));
        $cname      = '$cname='.Input::get('cname');
    }
    // Pagination
    $clients    = $clients->paginate(25);
    // Return View
    return View::make('pages.platform.clients')
        ->with('clients', $clients);
}

如果我尝试按姓名排序，它会按客户姓名排序，但如果我尝试按联系人排序，则需要按用户(经理)姓名排序.

If I try to sort by Name it sorts OK by Clients name but if I try to sort by contact it needs to sort by User's (Manager's) name.

推荐答案

试试这个.

$clients = Client::join('users as manager', 'manager.id','=','clients.manager')
            ->orderBy('manager.name', $order)
            ->select('clients.*') // avoid fetching anything from joined table
            ->with('Manager');  // if you need managers data anyway

把它放到你的 if 语句中 if($sort == 'contact'){/***