如何在laravel 4.2中使用mysql中的计数值查询

Question

I wish to check the sum value and count value in the where condition of laravel 4.2 query

Here's the code that i have

    $aColumns = array('driver_details.DriverId', DB::raw('CONCAT(driver_details.Firstname, " ", driver_details.Lastname, " / ", driver_details.TaxiPlateNo) AS TaxiDriver'), DB::raw('count(taxi_trip.AutoId) AS TotalTrip'), DB::raw('sum(taxi_trip.TripDistance) AS TotalTripDistance'), DB::raw('sum(taxi_trip.TotalFare) AS TotalTripFare'));       
    $aColumnsSearch = array('driver_details.DriverId', 'driver_details.Firstname', 'driver_details.Lastname','driver_details.TaxiPlateNo', 
    DB::raw('count(taxi_trip.AutoId)'));

$Search='';
if(Input::get('Search')!='')
    $Search = Input::get('Search');     
$DriverId='';
if(Input::get('DriverId')!='')
    $DriverId = Input::get('DriverId');

$IsCancel='';
if(Input::get('IsCancel')!='')
    $IsCancel = Input::get('IsCancel');

$FromDate='';
if(Input::get('FromDate')!='')
    $FromDate = Input::get('FromDate');

    $ToDate='';
    if(Input::get('ToDate')!='')
        $ToDate = Input::get('ToDate');

$tempqry = DB::connection()->table('driver_details')    
    ->select($aColumns)
    ->leftJoin('taxi_trip', function($join)
          {
             $join->on('taxi_trip.DriverId', '=', 'driver_details.DriverId');
             $join->on('taxi_trip.PickupLAT', '!=', DB::raw(0));
             $join->on('taxi_trip.DropLAT', '!=', DB::raw(0));
             $join->on('taxi_trip.TotalFare', '!=', DB::raw(0));
           })
        ->where(function($query) use ($FromDate) {
       if($FromDate!='')
        {
            $query->where(DB::raw("date_format(taxi_trip.RequestDate,'%Y-%m-%d')"), '>=', date("Y-m-d",strtotime($FromDate)));
        }
    })
    ->where(function($query) use ($ToDate) {
       if($ToDate!='')
        {
            $query->where(DB::raw("date_format(taxi_trip.RequestDate,'%Y-%m-%d')"), '<=', date("Y-m-d",strtotime($ToDate)));
        }
    })
    ->where(function($query) use ($Search, $aColumnsSearch) 
            {
                for ( $i=0 ; $i<count($aColumnsSearch) ; $i++ )
                {
                    //$query->orWhere($aColumnsSearch[$i].' LIKE %'.$Search.'%');   

                    $query->orWhere($aColumnsSearch[$i],'LIKE', '%'.$Search.'%');
                }
            })
        ->where(function($query) use ($DriverId) {
       if($DriverId!='')
        {
            $query->where('taxi_trip.DriverId', '=', $DriverId);
        }
    })

    ->groupby('driver_details.DriverId');

    $temp = $tempqry;
    $tempqry  = $temp->get();

    return $tempqry;

I wish to use the count() and sum() values getting from the query against the search value which is i am giving

Answer 1

I don't know about the table structure that you follow, But here's the Working Logic what you expect.

Step 1 :

Get the user's input and get the list of records that matches your count which would look something like this.

$inputCount = '100'; #Assign the user input here
$result = DB::table('users')
                    ->orderBy('name', 'desc')
                    ->groupBy('count')
                    ->having('count', '>', $inputCount)
                    ->get();

Now, You will be having the list of the user's which has total count that matches user's input.

Note : column count is different than total count

Step 2 :

Just iterate using foreach

@foreach ($result as $data)
    <p>This is user {{ $data->id }}</p>
@endforeach

Note : If you use datatables then you should iterate with your table structure.

Additional Point :

If you need to retrieve the user's information which is inside the iteration then.

You shall get the iterated entities details inside the iteration

i.e., plucking the user's data

$name = DB::table('users')->where('id', $result->id)->pluck('name');

So, You shall have

@foreach ($result as $data)
<p>This is user {{ $data->id }}</p>
$name = DB::table('users')->where('id', $data->id)->pluck('name');
@endforeach

Recommendation :

I would recommend you to use eloquent