CODEWITHSUNDEEP
phplaravellaravel-5laravel-5.1
Rohitashv Singhal
Rohitashv Singhal

Reputation: 4557

Distinct Values in Laravel

Hello Friends I am using the following query :

$cur_date = date('Y-m-d');
   $clientTemp = DB::table('clients')->where('quotations.exp_date','<',$cur_date)
     ->join('quotations','quotations.customer_id','=','clients.id')
      ->get()
       ->map(function ($clientTemp) {
        return [
            'id' => $clientTemp->id,
            'hash' => $clientTemp->hash,
            'name' => $clientTemp->first_name.' '.$clientTemp->last_name,
            'email' => $clientTemp->email,
            'mobile' => $clientTemp->mobile
        ];
    });

I am getting this data from two tables :

1. Qutoations and 2. Clients.

In quotations table if the exp_date is less than current date then the details will be fetched from client table.

But there is possibility then there are more than 1 rows in quotation table but I want to fetch only one table from that for which customer_id is unique. How can I fetch unique row with same customer_id from quotations table

Upvotes: 0

Views: 149

Answers (2)

Devon Bessemer
Devon Bessemer

Reputation: 35337

You'd need to use a GROUP BY clause but due to MySQL's default ONLY_FULL_GROUP_BY mode, you must aggregate any column that has more than one value.

You don't seem to be actually using any values from quotations, so you could just add:

DB::table('clients')->select('clients.*')->groupBy('clients.id')...

Otherwise, you'd need to tell MySQL how to aggregate any rows that have multiple values, like:

DB::table('clients')->selectRaw('clients.*, MIN(quotations.id)')->groupBy('clients.id')...

Upvotes: 1

user8824269
user8824269

Reputation:

You should use groupby

$cur_date = date('Y-m-d');    $clientTemp = DB::table('clients')->where('quotations.exp_date','<',$cur_date)
     ->join('quotations','quotations.customer_id','=','clients.id')
     ->->groupBy('quotations.customer_id')
      ->get()
       ->map(function ($clientTemp) {
        return [
            'id' => $clientTemp->id,
            'hash' => $clientTemp->hash,
            'name' => $clientTemp->first_name.' '.$clientTemp->last_name,
            'email' => $clientTemp->email,
            'mobile' => $clientTemp->mobile
        ];
    });

Upvotes: 1

Related Questions