issue when model contains "date" column (conflicts with date_format alias)

[wivaku]

I have a model for transactions that includes these two fields:

• date: when did the transaction take place
• created_at: when was the record created

This results in an error message:

Illuminate\Database\QueryException  SQLSTATE[42000]: 
Syntax error or access violation: 1055 'myapp.transactions.created_at' isn't in GROUP BY

Using e.g.

Flowframe\Trend\Trend::model(Transaction::class)
->between(start: now()
->subMonths(1), end: now())
->perMonth()
->count()

which "expands" to:

select 
  date_format(created_at, '%Y-%m') as date, -- this alias conflicts with the table's column `date`
  count(*) as aggregate
from `transactions` 
where `created_at` between '2022-10-09 18:48:11' and '2022-11-09 18:48:11'
group by `date` 
order by `date` asc

It seems the date_format(...) as date conflicts with the table column date.

The above works fine for a model without a date column.

Related: can the field to group by be customized? I don't want to group by created_at but group by the table's column date instead.

[stephen-morlex]

Have you figure out how to fix this issue?

[wivaku]

no

[MACscr]

No solution to this? I have the same problem. Always frustrating when an issue gets closed by the author with no response.

[Larsklopstra]

No solution to this? I have the same problem. Always frustrating when an issue gets closed by the author with no response.

https://github.com/Flowframe/laravel-trend/blob/bc43bf7840ff60aca39e856ad96f5e990fac83d7/src/Trend.php#L80

This should do it?

[danswiser]

I had a very similar issue where my data was stored in a date column. Using ->dateAlias() fixed the issue for me. This is what should work:

Flowframe\Trend\Trend::model(Transaction::class)
->dateColumn('date') // The column with timestamp data
->dateAlias('alias') // Set an alias that doesn't conflict with any existing columns
->between(start: now()
->subMonths(1), end: now())
->perMonth()
->count()