how to use MIN and MAX sql query in Laravel Eloquent

mysql sql laravel laravel-5 eloquent

11,564

Solution 1

Appointmentsetting::where('Day','=',1)
->whereIn('PersonID', function ($query) {
    $query->select('p.id')
        ->from('users as p')
        ->join('appointmentsettings as aps', 'aps.PersonID', '=', 'p.id')
        ->where('p.active', '=', 1)
        ->where('aps.CompanyID', '=', 1)
        ->orWhereIn('aps.PersonID', function ($query2) {
            $query2->select('cps.user_id')
                    ->from('companypersonstructs as cps')
                    ->where('cps.CompanyID', '=', 1);
        })
        ->groupBy('aps.PersonID');
})
->where('active', '=', 1)
->select(\DB::raw("MIN(StartFrom) AS StartFrom, MAX(EndTo) AS EndTo"));
->get();

Solution 2

->selectRaw(" MIN(StartFrom) AS StartFrom, MAX(EndTo) AS EndTo");

please put above query and try. the main problem in your query is you are using select two time there is no need for write select inside select or selectRaw for more about raw query click here

11,564

Jazuly

Updated on June 04, 2022

Comments

Jazuly almost 2 years
i want to use min and max laravel elaquent at the same line, is that posible?
```
Appointmentsetting::where('Day','=',1)
->whereIn('PersonID', function ($query) {
    $query->select('p.id')
        ->from('users as p')
        ->join('appointmentsettings as aps', 'aps.PersonID', '=', 'p.id')
        ->where('p.active', '=', 1)
        ->where('aps.CompanyID', '=', 1)
        ->orWhereIn('aps.PersonID', function ($query2) {
            $query2->select('cps.user_id')
                    ->from('companypersonstructs as cps')
                    ->where('cps.CompanyID', '=', 1);
        })
        ->groupBy('aps.PersonID');
})
->where('active', '=', 1)
->select(\DB::raw("SELECT MIN(StartFrom) AS StartFrom, MAX(EndTo) AS EndTo"));
->get();
```
i got error

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT MIN(StartFrom) AS StartFrom, MAX(EndTo) AS EndTo from `appointmentsetting' at line 1
- Lim Kean Phang about 5 years
  
  min('col_name')->max('col_name') ?
- Lim Kean Phang about 5 years
  
  ->select(DB::raw("MIN(StartFrom) AS StartFrom, MAX(EndTo) AS EndTo")) Try This
- Jazuly about 5 years
  
  @LimKeanPhang yeah i already try it but the system give me error Call to a member function max() on string
- Lim Kean Phang about 5 years
  
  Updated comments. Try DB::raw
- Gaurav Gupta about 5 years
  
  just remove SELECT form inside
- Jazuly about 5 years
  
  @GauravGupta how is it?
- Gaurav Gupta about 5 years
  
  laravel.com/docs/5.8/queries#raw-expressions more detail , when you use selectRaw() so why need extra select
- Jazuly about 5 years
  
  @LimKeanPhang it send me builder result Builder {#590 ▼ #query: Builder {#591 ▶} #model: Appointmentsetting {#592 ▶} #eagerLoad: [] #localMacros: [] #onDelete: null #passthru: array:13 [▶] #scopes: [] #removedScopes: [] }
- Gaurav Gupta about 5 years
  
  please update little more code also
- Jazuly about 5 years
  
  @GauravGupta done, please take a look
- Jazuly about 5 years
  
  sorry, i still get same error as above when i change to DB::RAW
- Lim Kean Phang about 5 years
  
  ->select(\DB::raw("MIN(StartFrom) AS StartFrom, MAX(EndTo) AS EndTo")); //Remove the additional select inside @Jazuly
- Gaurav Gupta about 5 years
  
  ->selectRaw(" MIN(StartFrom) AS StartFrom, MAX(EndTo) AS EndTo"); put this please and try
- Jazuly about 5 years
  
  @LimKeanPhang its work, thanks, please write u answer.
- Jazuly about 5 years
  
  btw, my first code its works too just need remove select inside the code.
- Lim Kean Phang about 5 years
  
  Alright~Done. Cheers~!
Gaurav Gupta about 5 years

i was writing information both time in comment as well as in answer that why i didn't see it comment and answer is already there