Laravel Multiple orderBy Group Views by Day

Question

I'm using OctoberCMS based on Laravel and Twig.

I'm trying to sort a list of images from the database using orderBy.

I need to display the images grouped by date (desc), then ordered by views (desc) within that date.

I read here to use multiple orderBy passes.

Database Records

name     | created_at          | views
---------------------------------------
image 01 | 2017-03-27 07:08:31 | 3
image 02 | 2017-03-27 01:19:44 | 25
image 03 | 2017-03-25 01:43:06 | 9
image 04 | 2017-03-25 04:19:43 | 1
image 05 | 2017-03-25 15:19:43 | 12
image 06 | 2017-03-22 03:07:06 | 4
image 07 | 2017-03-22 08:10:07 | 16
image 08 | 2017-03-21 09:04:29 | 39
image 09 | 2017-03-20 07:38:31 | 5
image 10 | 2017-03-20 05:32:24 | 28

Laravel

orderBy

Successfully orders by Date created_at, but ignores ordering by views. May be because of time.

$this->page["scopeGallery"] = Gallery::orderBy('created_at', 'desc')
                                      ->orderBy('views', 'desc')
                                      ->get();

groupBy

Returns null.

$this->page["scopeGallery"] = Gallery::select(\DB::raw('DATE(created_at) as day'))
                                      ->groupBy('day')
                                      ->orderBy('views', 'desc')
                                      ->get();

Twig

{% for image in this.page.scopeGallery %}
    image.name
{% endfor %}

Result I'm looking for

name     | created_at | views
------------------------------
image 02 | 2017-03-27 | 25
image 01 | 2017-03-27 | 3

image 05 | 2017-03-25 | 12
image 03 | 2017-03-25 | 9
image 04 | 2017-03-25 | 1

image 07 | 2017-03-22 | 16
image 06 | 2017-03-22 | 4

image 08 | 2017-03-21 | 39

image 10 | 2017-03-20 | 28
image 09 | 2017-03-20 | 5

score 2 · Answer 1 · answered Mar 31 '17 at 04:24

2

I presume the created_at field is datetime. Your first query will order by datetime and therefore the secondary orderby wont do much as the it will be applied if there are serveral created_at values that match.

This should work:

$this->page["scopeGallery"] = Gallery::orderByRaw(\DB::raw('DATE(created_at) desc'))
                                  ->orderBy('views', 'desc')
                                  ->get();

That will create the following query:

select * from `your_table` order by DATE(created_at) desc, `views` desc

answered Mar 31 '17 at 04:24

dragontree

1,709
11
14

I posted an answer. I had to use `select` and order by day without time. But do you think '(created_at) as day' will mess up on the next month and group two month's matching days together? – Matt McManis Apr 03 '17 at 11:43
1

Probably yes, as the it will sort by day number. So January 2nd will be after February 1st. – dragontree Apr 03 '17 at 11:47
How could I combine month and day so it makes seperate groups? – Matt McManis Apr 03 '17 at 11:57
What are you trying to achieve? – dragontree Apr 03 '17 at 11:58
I need the Gallery structured so that the images are grouped by day, then ordered by views within that day. – Matt McManis Apr 03 '17 at 12:24

score 0 · Answer 2 · answered Apr 03 '17 at 11:39

I had to do it like this, and include any other columns I need in the select.

$this->page["scopeGallery"] = Gallery::select([
                                        'name',
                                        \DB::raw('DATE(created_at) as day')
                                      ])
                                      ->orderBy('day', 'desc')
                                      ->orderBy('views', 'desc')
                                      ->get();
}

Laravel Multiple orderBy Group Views by Day

Database Records

Laravel

Twig

Result I'm looking for

2 Answers2