Laravel Eloquent How to Get the Minimum Value That Is Not Null

Laravel eloquent how to get the minimum value that is not NULL?

I found filter, and it works now.

public function getLowestAttribute ()
{
    $prices = $this->prices->filter(function ($item) {
        return !is_null($item->price);
    });

    return $prices->min('price');
}

Get all the row using where min/max laravel

You could try this:

$tmp = rawData::whereDate('acktime','=', $thisDay)->orderBy('temperature', 'asc')->first();

How to get min value of specific column from belongsToMany relationship in eloquent

I have found my answer after many try. Hope this can help others as well.

addSelect method helped me about this and my eloquent code is more effective now. This creates only one query and no detailed (unnecessary) information about devices as I wanted. It gives only the lowest battery level for each projects.

Eloquent code for this:

$projects = Project::select('projects.id', 'projects.name')
    ->addSelect([
        'lowest_battery_level' => Device::select('battery_level')
        ->leftJoin('device_project', 'device_project.device_id', '=', 'devices.id')
        ->whereColumn('device_project.project_id', 'projects.id')
        ->orderBy('battery_level', 'asc') // no need asc if you wanna get lowest value first
        ->limit(1)
    ])
->get();

// can use like this
foreach ($projects as $project) {
    $values[] = $project->lowest_battery_level
}

This creates sql query like this:

Creates only 1 query and get only projects results without other devices' details.

select
    `projects`.`id`, 
    `projects`.`name`,
    (
        select
            `battery_level`
        from `devices`
        inner join `device_project` on `devices`.`id` = `device_project`.`device_id`
        where `projects`.`id` = `device_project`.`project_id`
        order by `battery_level`
        limit 1
    ) as `lowest_battery_level`
from `projects`

Performance Comparison with Laravel Debugbar

There are 100 projects and 1000 devices in database. And every project have relationship randomly with 0-50 of devices. Also different projects can have relationship with same devices (many-to-many relationship).

With previous eloquent code:

$projects = Project::with('devices:battery_level')->get();

foreach ($projects as $project) {
    $values[] = $project->devices->min('battery_level')
}

As it can be seen below, it uses 18 MB RAM and took 539 ms.

Creates 2783 Device objects and 100 Project objects

With new eloquent code: (which I showed above)

As it can be seen below, it uses 10 MB RAM and took 432 ms.

Creates no Device objects and only 100 Project objects

How Can I get Min or Max value from Union result with Laravel Query Builder?

You can get min from the get() function result.
Simply added a get() function to your query.

$JobPhase = JobPhase::where('ScheduledDate', '<>', null)
    ->select('ScheduledDate AS PromisedDate');

$minDate = JobWorkOrder::where('PromisedDate', '<>', null)
    ->select('PromisedDate')
    ->union($JobPhase)
    ->get()
    ->min('PromisedDate');

Then you will get a MIN value.

Laravel - Calculate minimum value from multiple fields in controller

I solved this by @Jonas's solution. And I make this my answer.

Controller

public function store(Request $request, $id){
$shop = Shop::findorfail($id);
$item = Item::where('shop_id', $shop->id)->get(); //working fine
$i=0;
$price = request('price')

foreach( $items as $item)
$item->price = request('price')[$i];
$i++;

//This works
$min_price = min(request('price'));

$item->save();

}

Eloquent is saving no values in not null fields

Well, they're empty but they aren't null - as expected. To avoid empty entries you must use validation rules to enforce that:

In a controller:

public function store(Request $request)
{
    $this->validate($request, [
        'name' => 'required|min:4',
        'email' => 'required|email',
        'password' => 'required|min:8',
    ]);

    $user = User::create($request->only('name', 'email', 'password'));

    return redirect()->route('users.show', $user->id);
}

How to show the minimum and maximum price?

I would do it this way (assuming that Event is in fact your Conference class) :

class Conference extends Model
{
    protected $appends = ['price_range'];

    public function registrationTypes(){
        return $this->hasMany('App\RegistrationType', 'conference_id');
    }

    public function getPriceRangeAttribute() {
        return $this->registrationTypes()->get()->min('price') . ' - ' . $this->registrationTypes()->get()->max('price');
        /* The first answer :
         * return $this->registrationTypes()->min('price') . ' - ' . $this->registrationTypes()->max('price');
         * should also do it, and could even be better, because no 
         * other results than the actual min and max are returned,
         * no collection fetched ! */
    }
}

Here you tell Laravel to appends the price_range attribute to every instance of Conference. The price_range attribute is returned by the getPriceRangeAttribute accessor who check for the minimum and maximum found in the price column for every registrationType linked (returned by the hasMany relation) to your Conference.

How to validate an input field if value is not null in Laravel

try using nullable as a rule

'password' => 'nullable|min:6|confirmed',

see https://laravel.com/docs/5.8/validation#a-note-on-optional-fields

---

Related Topics