use distinct with multiple columns in laravel

Question

my table structure is as below

date        seller  unit    price   total
05-06-17    abc     14      700     9800
05-06-17    pqr     12      600     7200
05-06-17    abc     10      520     5200
06-06-17    abc     10      600     6000
06-06-17    pqr     15      520     7800
06-06-17    pqr     16      520     8320

I need to fetch record like

1) 'date' = '05-06-2017', 'seller' = 'abc', 'total' = '15000'
2) 'date' = '05-06-2017', 'seller' = 'pqr', 'total' = '7200'
3) 'date' = '06-06-2017', 'seller' = 'abc', 'total' = '6000'
4) 'date' = '06-06-2017', 'seller' = 'pqr', 'total' = '16120'

I need data from database in date wise. seller abc has two entry in a table for date 05-06-2017 so I need total of that day for seller abc as well pqr same things for the second date for all seller

Please describe your required data in detail. Last line is difficult to understand. — Sagar Gautam, Jun 06 '17 at 06:04

score 8 · Answer 1 · edited Jan 07 '23 at 08:52

Whenever we have multiple rows with same data that needs to be merged together in final output, we use group by functionality of mySQL.

so in your case you can use laravel query builder to do it in this way.

 DB::table('orders')->select('date', 'seller', DB::raw('SUM(total) as total'))
    ->groupBy('seller')
    ->groupBy('date')
    ->get();

Explanation

DB::table('orders')->select('date', 'seller', DB::raw('SUM(total) as total')) will query our orders table in our database & then fetch our provided columns i.e. date, seller and sum of total column as total

->groupBy('seller')->groupBy('date') It will merge the multiple records of same seller in same date.

->get(); we are get getting our data from the query.

linktoahref · Answer 2 · 2017-06-06T06:22:04.840

3

You could try

DB::table('orders')
    ->select('date', 'seller', DB::raw('SUM(total)'))
    ->groupBy('date')
    ->groupBy('seller')
    ->get();

edited Jun 06 '17 at 06:22

answered Jun 06 '17 at 06:15

linktoahref

7,812
3
29
51

Hiren Wadhiya · Answer 3 · 2021-07-27T06:53:34.247

2

You can use distinct with multiple columns like this(checked it in Laravel 8).

Product::where('column_name','=', $searchTerm)
->distinct('column1','column2')
->get();

edited Jul 27 '21 at 06:53

answered Jul 27 '21 at 06:36

Hiren Wadhiya

167
1
8

score 0 · Answer 4 · answered Apr 13 '20 at 20:59

0

$search = $request->search;

    $productSearch = Product::where('title', 'LIKE', '%'.$search.'%')
                    ->orWhere('description', 'LIKE', '%'.$search.'%')
                    ->get();

       return $produtcSearch;

answered Apr 13 '20 at 20:59

AhmedSeaf

221
2
3

2

While this code may solve the question, [including an explanation](https://meta.stackexchange.com/q/114762) of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please [edit] your answer to add explanations and give an indication of what limitations and assumptions apply. – Brian61354270 Apr 14 '20 at 00:37

use distinct with multiple columns in laravel

4 Answers4

Linked