CODEWITHSUNDEEP
phpmysqllaravel
Jazz
Jazz

Reputation: 175

Sorting column in subquery not working laravel

I try to sort column quality with $query->orderBy('numbers.quality', 'asc'); in subquery but it not working. Any workaround? Note: For some reason i don't want to use join.

$model = NumberUpload::query();

if (!empty($request['domain'])) {
    $model->whereIn('domain_id', $request['domain']);
}

$model->whereIn('phone', function($query) use ($request){
    $query->select('phone')->from('numbers')->whereNull('numbers.client_group_id')->whereNotIn('id', function($query) use ($request){
        $query->select('number_push_logs.number_id')->from('number_push_logs')->where('number_push_logs.client_group_id',$request['client_group']);
    });

    if (!empty($request['min_quality'])) {
        $query->where('numbers.quality', '>=', $request['min_quality']);
    }

    if (!empty($request['max_quality'])) {
        $query->where('numbers.quality', '<=', $request['max_quality']);
    }

    if (!empty($request['number_type'])) {
        $query->whereIn('numbers.number_type_id', $request['number_type']);
    }

    if (!empty($request['contact_status'])) {
        $query->where('numbers.contact_status_id', $request['contact_status']);
    }

    $query->orderBy('numbers.quality', 'asc');
})->limit(5);

This the sql query output:

MySQL query:

select * from `number_uploads` where `domain_id` in (?, ?, ?, ?, ?, ?) and `phone` in (
  select `phone` from `numbers` where `numbers`.`client_group_id` is null and `id` not in (
    select `number_push_logs`.`number_id` from `number_push_logs` where `number_push_logs`.`client_group_id` = ?
  ) order by `numbers`.`quality` asc
) limit 5

Current output:

phone quality
82187****** 5
82187****** 2
82187****** 3
82187****** 1
82187****** 4

Expected output:

phone quality
82187****** 1
82187****** 2
82187****** 3
82187****** 4
82187****** 5

Upvotes: 1

Views: 338

Answers (1)

nbk
nbk

Reputation: 49375

Your query must in sql looks like this, if you want to keep the order

select * from `number_uploads` 
INNER JOIN (
  select `phone`,`quality` from `numbers` where `numbers`.`client_group_id` is null and `id` not in (
    select `number_push_logs`.`number_id` from `number_push_logs` where `number_push_logs`.`client_group_id` = '?'
  )) numbers ON `number_uploads`.`phone` = numbers.`phone`
where `domain_id` in (?,?,?,?,?) 
order by `numbers`.`quality` 
limit 5

Upvotes: 2

Related Questions