Why ORDER BY Fails When Using DATE_FORMAT in Laravel Eloquent
Sorting database results by a formatted date string often feels like a reasonable first step, but it introduces significant logical pitfalls that can lead to unexpected ordering behavior. The specific issue you are facing stems from how SQL handles type coercion when mixing numeric-looking strings with chronological sorting logic. In your current Laravel 5 query, you are transforming the created_at timestamp into a string formatted as 'DD-MM-YYYY', grouping by that string, and then attempting to order by that same string.
While this approach works perfectly fine for simple display lists, it breaks down when you need true chronological sorting. The root cause is that DATE_FORMAT returns a string (e.g., "15-03-2024"), not a timestamp or a date object. When the database engine evaluates ORDER BY '15-03-2024' ASC, it treats these values as lexicographical strings rather than dates. Lexicographical sorting compares character by character. This means "10-01-2024" would indeed come before "2-01-2024" because the character '1' is less than '2'. However, if you have entries from different years or months where the day differs, string sorting can produce results that defy logical time progression. More critically, once you apply GROUP BY on a formatted date, your dataset contains only one row per unique formatted date string. If multiple records share the same formatted date but differ in year (which shouldn't happen with standard formatting unless years are omitted), or if the sorting logic relies on hidden metadata within the string representation, the order becomes unreliable compared to sorting directly on the underlying timestamp.
The most robust solution is to defer the formatting until after the query has executed and sorted the data correctly in the database layer. Instead of grouping by the formatted string, you should group by the actual date part of the created_at column, or simply omit the GROUP BY clause if you intend to aggregate rows into a single result per day without losing individual record context. If your goal is to display unique dates sorted chronologically, you can use Laravel's date helpers on the collection returned from the query, but sorting must happen inside the database for performance reasons when dealing with large datasets.
Here is an alternative approach that groups by the date truncated to the day while maintaining correct chronological order:
$b = DB::table('entry')
->selectRaw('DATE(created_at) as tanggal')
->groupBy('tanggal')
->orderBy('tanggal', 'asc')
->get();
By using DATE(created_at) instead of DATE_FORMAT, you ensure the database operates on a date type. This guarantees that ORDER BY respects chronological logic rather than string comparison. If you specifically need the formatted string for your view, apply DATE_FORMAT in the Blade template or use Laravel's format() helper after retrieving the collection.
There is a trade-off here regarding data integrity. Using GROUP BY collapses multiple records with the same date into a single row. If your application logic relies on processing individual entries within that day, this aggregation destroys that granularity. In such cases, you should remove the groupBy clause entirely and let Laravel handle the sorting of all records by their original created_at timestamps.
If you must persist the formatted date string for indexing purposes, consider altering the column type or using a proper Carbon/Date column in newer migrations rather than relying on string manipulation in the query builder. This ensures that future queries can leverage database indexes for efficient sorting without falling back to slow string comparisons.
For more complex date handling scenarios, including time zones and specific formatting requirements, you might find additional resources at https://laravelcompany.com, though the fundamental principle remains: let the database sort on dates, not strings.