按公司不工作排序.请协助我

Question

这是我的数据库查询，当我使用排序公司时它不起作用，我总共有 48000 条记录，但它只显示 18000 条记录。按状态，联系人，城市排序都工作正常。任何帮助将不胜感激

public static function retreiveCandidates($status,$alpha=NULL,$dash=NULL,$statusid,$sortby=NULL){
    DB::enableQueryLog();
    $query = DB::table('candidates')->select('candidates.candidateid','candidates.firstname','candidates.lastname','candidates.imageext','candidates.middlename','candidates.personal_email','candidates.city','candidates.mobile','candidates.title_id','candidates.status','candidates.state','candidates.isverified','candidates.candidate_statusid','candidates.sendinvitation','candidates.ismerged','candidates.imagekey','candidates.candidatekey')->where('candidates.status',$status);
    if($alpha!=''){
        $query = $query->where('candidates.firstname', 'like', $alpha . '%');
    }
    if($statusid !=''){
        $query = $query->where('candidates.candidate_statusid',$statusid);
    }
    if($dash !=''){
        $result = $query->orderBy('candidates.created_at', 'desc')->limit(7)->get();
    }
    if($sortby == NULL){
        $query = $query->orderBy('candidates.firstname', 'asc') ;
    }elseif($sortby == 'company'){
        $query = $query->join('candidates_company','candidates_company.candidateid','=','candidates.candidateid')->join('company','company.companyid','=','candidates_company.companyid')->where('candidates_company.status','1')->groupBy('candidates.candidateid')->orderBy('company.company', 'asc')->orderBy('candidates.candidateid', 'asc')  ;
    }elseif($sortby == 'status'){
           $query = $query->join('candidate_status','candidate_status.statusid','=','candidates.candidate_statusid')->orderBy('candidate_status.statusorder', 'asc') ;  
    }elseif($sortby == 'contact'){
        $query = $query->orderBy('candidates.firstname', 'asc');            
    }elseif( $sortby == 'city'){            
        $query = $query->orderBy('candidates.city', 'asc') ;
    }elseif( $sortby == 'state'){           
        $query = $query->orderBy('candidates.state', 'asc') ;
    }
    $result = $query->paginate(PAGELIMIT);
    $d= DB::getQueryLog();
    return $result;     
}

这是我在打印数据库查询时得到的

    Array
(
    [0] => Array
        (
            [query] => select count(*) as aggregate from `rsi_candidates` inner join `rsi_candidates_company` on `rsi_candidates_company`.`candidateid` = `rsi_candidates`.`candidateid` inner join `rsi_company` on `rsi_company`.`companyid` = `rsi_candidates_company`.`companyid` where `rsi_candidates`.`status` = ? and `rsi_candidates_company`.`status` = ? group by `rsi_candidates`.`candidateid`
            [bindings] => Array
                (
                    [0] => 1
                    [1] => 1
                )
            [time] => 1036.83
        )
    [1] => Array
        (
            [query] => select `rsi_candidates`.`candidateid`, `rsi_candidates`.`firstname`, `rsi_candidates`.`lastname`, `rsi_candidates`.`imageext`, `rsi_candidates`.`middlename`, `rsi_candidates`.`personal_email`, `rsi_candidates`.`city`, `rsi_candidates`.`mobile`, `rsi_candidates`.`title_id`, `rsi_candidates`.`status`, `rsi_candidates`.`state`, `rsi_candidates`.`isverified`, `rsi_candidates`.`candidate_statusid`, `rsi_candidates`.`sendinvitation`, `rsi_candidates`.`ismerged`, `rsi_candidates`.`imagekey`, `rsi_candidates`.`candidatekey` from `rsi_candidates` inner join `rsi_candidates_company` on `rsi_candidates_company`.`candidateid` = `rsi_candidates`.`candidateid` inner join `rsi_company` on `rsi_company`.`companyid` = `rsi_candidates_company`.`companyid` where `rsi_candidates`.`status` = ? and `rsi_candidates_company`.`status` = ? group by `rsi_candidates`.`candidateid` order by `rsi_company`.`company` asc, `rsi_candidates`.`candidateid` asc limit 10 offset 0
            [bindings] => Array
                (
                    [0] => 1
                    [1] => 1
                )
            [time] => 454.11
        )
)

Answer 1

建议

将来引用，使用"==="而不是"=="，第二个在某些最尴尬的情况下会产生true。

进入问题，您的搜索只有

where('candidates_company.status'，'1'(

这可能会缩小结果的范围，而没有实际看到表格，我无法确定这是问题所在，但是除了减少检索到的结果量之外，我真的看不到任何其他内容。

Answer 2

创建候选人模型

php artisan make:model Candidate

这将在应用文件夹中创建一个模型文件候选项.php 。该文件看起来像

<?php
namespace App;
use IlluminateDatabaseEloquentModel;
class Candidate extends Model
{
 protected $table = 'candidate'; //give your table name here
}

现在，您可以在控制器中访问候选模型，例如

use AppCandidate;
class CandidateController extends Controller
{
 $candidates= AppCandidate::orderBy('company')->get(); //select by orderby
}

希望这有帮助