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

Krasner

Please describe your required data in detail. Last line is difficult to understand. – Anthroposophy

Answer 1

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.

Branch

Answer 2

You could try

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

Shieh

Answer 3

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

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

Insane

Answer 4

$search = $request->search;

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

       return $produtcSearch;

Nadler

While this code may solve the question, including an explanation 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. – Muskellunge