'Laravel working with relationships and conditions is not working correctly

I have the following code:

$sensor = Sensor::with(['allStates' , 'todayStates', 'weekStates'])->where(['client_uuid' => $user->uuid, 'uuid' => $sensor_uuid])->first();

My Sensor model (relationships section):

    public function allStates()
    {
        return $this->hasMany(SensorData::class, 'sensor_uuid' , 'uuid')->orderBy('created_at' , 'DESC');
    }

    public function todayStates()
    {
        return $this->hasMany(SensorData::class , 'sensor_uuid' , 'uuid')->whereDate('created_at' , '=' , Carbon::today())->orderBy('created_at' , 'ASC');
    }

    public function weekStates()
    {
        return $this->hasMany(SensorData::class , 'sensor_uuid' , 'uuid')->whereBetween('created_at' , [Carbon::now()->startOfWeek(), Carbon::now()->endOfWeek()])->orderBy('created_at' , 'ASC')->groupBy('created_at');
    }

The thing here is that on the weekStates relationship, it gets all the data between the given 2 dates (start of week - end of week), but the problem is, all of the data from each day of the week will also be returned, and this will cause some problems on my client-side charts.

So what i need to do is select all 7 days of the week's worth of data, but uniquely, in the sense that i need to fetch ONE SensorData row PER day.

I have tried to use groupBy(created_at) on my relationship, but it didnt work, it gives the following error:

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'my_db.sensor_data.uuid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with...

I would appreciate any kind of help i could get.

Thank you very much!



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source