CODEWITHSUNDEEP
mysqlsqllaravellaravel-5eloquent
Jazuly
Jazuly

Reputation: 1424

how to use MIN and MAX sql query in Laravel Eloquent

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

Upvotes: 2

Views: 18806

Answers (2)

Gaurav Gupta
Gaurav Gupta

Reputation: 1698

->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

Upvotes: 7

Lim Kean Phang
Lim Kean Phang

Reputation: 501

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();

Upvotes: 2

Related Questions