Level 104
The min function will complete a query so you are no longer working with a Builder instance. You will need the raw SQL:
DB::table('my_table')->selectRaw('some_col, min(date)')->groupBy('some_col'); // ->get()
1 like
Aug 29, 2022
21
Level 8
Is it possible to use the following query without raw sql?
I need to get distinct values by one column and also the oldest date. This is the raw sql:
SELECT some_col, min(date)
FROM table_name
GROUP BY some_col
Assuming table_name is also the same as the eloquent model name just capitalized, is it possible to make this query with either eloquent or query builder?
For example I tried:
DB::table('my_table')->select('some_col', 'date')->min('date')->groupBy('some_col');
But I get errors about using methods on string (returned by min())
Also tried:
MyTable::oldest('date')->groupBy('some_col');
but after inspecting the raw sql using ->toSql(), it's not the same
Level 8
@tykus The query itself does translate well, but I get error about other columsn not in either the aggregate function or the GROUP BY clause.
Sorry that in my example I forgot to add more columns.. I guess I get this error because MIN can give more than 1 result? So it means my initial SQL is wrong.
How can I then do it? to select distinct by some_col, and oldest date, and also get all columns?
Level 102
@Ligonsker show the actual sql query with all columns needed
And use group by instead of distinct
And a bit of reading to better understand group by https://sinnbeck.dev/posts/laravel-groupby-error
1 like
Level 104
@Ligonsker the error message relates the ONLY_FULL_GROUP_BY SQL mode, you can choose to disable that setting.
1 like
Level 8
@tykus @sinnbeck What if I can't disable it?
@sinnbeck I need all the columns except these two (*).
Is there a way to make it work, if I group also by a unique column, in case date is equal? so if there is more than 1 row with same date, the oldest would be the one with the lower id? will that make the error disappear if it "sees" a unique column?
Level 102
@Ligonsker all columns need to be aggregated (sum, count, min, max) or in the groupBy
Refer to the post i posted earlier if in doubt
Not sure what you mean by id as lower? Id isn't going to be in the select I assume?
1 like
Level 8
@Sinnbeck The problem is that I need to use it with paginate. so I need to get the correct data before, or so I think. the solutions in the article are good but I think I can't use it with paginate and my purpose.
I did find a raw SQL query that gets the correct data: (DB::select(DB::raw( . . .)), but then I also can't use paginate because it's an array and not an object.
What should I do then?
Level 102
@Ligonsker sorry I don't see any what you could paginate it. I also have never seen the need once in my career. Maybe explain what the purpose of the query is? There might be a better way (maybe each row you want relates to another table?)
I can also suggest forgetting all about laravel for a start, and try and find some query online that does what you want (remember the ONLY_FULL_GROUP_BY that tykus mentioned)
1 like
Level 8
@sinnbeck regarding the ONLY_FULL_GROUP_BY , I don't have control over the DB.
The data is only in 1 table:
id data special_number date
1 ab 22 2022-06-15
2 cd 33 2022-07-05
3 ef 44 2022-08-18
4 gh 22 2022-09-21
I need to get the oldest data but also unique by special_number column.
So if special_number has 2 rows like above, then get the oldest of the two (either by date, and now that I think about it, could be by lower id)
So the data returned should be:
1 ab 22 2022-06-15
2 cd 33 2022-07-05
3 ef 44 2022-08-18
Level 75
@Ligonsker also you can disable full Group by for one single one if needed.
https://laracasts.com/discuss/channels/guides/turn-off-only-full-group-by-for-just-one-query-guide
1 like
Level 102
@Ligonsker But special_number is not a foreign key that points to another table? If it was, that might make it a bit easier. Otherwise you can use as sort of a primary key as it will always be unique I assume?
SELECT special_number, min(date)
FROM table_name
GROUP BY special_number
ORDER BY special_number
LIMIT 100
OFFSET 0
Maybe something like this?
Level 8
@Sinnbeck the above query did not work :/ It is a foreign key to another table
Level 102
@Ligonsker Oh? What error did you get? I just tested it locally on similar data, and it worked just fine?
\DB::select('SELECT special_number, min(date)
FROM table_name
GROUP BY special_number
ORDER BY special_number
LIMIT 100
OFFSET 0');
1 like
Level 102
And laravel version
\DB::table('table_name')
->select(['special_number', \DB::raw('min(date(date))')])
->groupBY('special_number')
->orderBy('special_number')
->paginate(20);
//or
MyModel::query()
->select(['special_number', \DB::raw('min(date(date))')])
->groupBY('special_number')
->orderBy('special_number')
->paginate(20);
Level 102
If you want the data, then you need to either group by it, or find an aggregate of it. I dont assume its the same when the special_number is the same?
Level 8
@jlrdw how can I do it for Microsoft sql? :O
Level 102
@Ligonsker So this isnt mysql? Sql server?
1 like
Level 8
@Sinnbeck yes this is microsoft sql server
Level 73
@Ligonsker You can't. The only option is to write proper SQL statements, which in my opinion you should do regardless. Shitty SQL will break your application sooner or later.
1 like
Level 75
@Ligonsker it would have been good up front to know which database you were using.
Try a stored procedure.
1 like
Level 8
@jlrdw sorry, next time I will always mention that first :D
Please or to participate in this conversation.