找到你要的答案

Q:Eloquent - Group By Month, Year and Paginate

Q:功能组的月、年和Paginate

I am trying to list entries in a table by Month, Year:

  • May, 2015
    • Item 1
    • Item 2
    • Item 3
  • June, 2015
    • Item 1
    • etc..

I have achieved this with the following code but I would also like to paginate the results. I have tried many different things but none of them seem to work, I am using Laravel 5.

$events = App\Events->orderBy('start', 'asc')->get()->groupBy(function($date) {
            return $date->start->format('F, Y');
        });

Here is the output for the above query:

{
    "April, 2015": [
        {
            "id": "10",
            "event_type_id": "1",
            "user_id": "1",
            "title": "Testing",
            "slug": "testing",
            "start": "2015-04-23 17:00:00",
            "end": "2015-04-23 17:40:00",
            "description": "<h1>MEETING!</h1><p>Let's try this in HTML!<br></p>",
            "created_at": "2015-04-19 14:18:33",
            "updated_at": "2015-04-21 22:07:41",
            "type": {
                "id": "1",
                "name": "General",
                "slug": "general",
                "created_at": "2015-04-18 11:24:00",
                "updated_at": "2015-04-18 11:24:04"
            }
        }
    ],
    "May, 2015": [
        {
            "id": "12",
            "event_type_id": "1",
            "user_id": "1",
            "title": "Test Event",
            "slug": "test-event",
            "start": "2015-05-15 18:00:00",
            "end": null,
            "description": "<p>This is a test event with just a start time</p>",
            "created_at": "2015-04-21 14:59:56",
            "updated_at": "2015-05-02 18:37:53",
            "type": {
                "id": "1",
                "name": "General",
                "slug": "general",
                "created_at": "2015-04-18 11:24:00",
                "updated_at": "2015-04-18 11:24:04"
            }
        },
        {
            "id": "9",
            "event_type_id": "1",
            "user_id": "1",
            "title": "Monthly Meeting",
            "slug": "monthly-meeting",
            "start": "2015-05-23 14:00:00",
            "end": "2015-04-16 20:00:00",
            "description": "<p>It's a long monthly meeting!</p>",
            "created_at": "2015-04-19 13:13:45",
            "updated_at": "2015-05-03 08:45:56",
            "type": {
                "id": "1",
                "name": "General",
                "slug": "general",
                "created_at": "2015-04-18 11:24:00",
                "updated_at": "2015-04-18 11:24:04"
            }
        }
    ],
    "June, 2015": [
        {
            "id": "11",
            "event_type_id": "1",
            "user_id": "1",
            "title": "Another Meeting Saved",
            "slug": "another-meeting-saved",
            "start": "2015-06-19 18:00:00",
            "end": null,
            "description": "<p>It's another meeting afterall</p>",
            "created_at": "2015-04-20 15:03:30",
            "updated_at": "2015-05-03 08:46:19",
            "type": {
                "id": "1",
                "name": "General",
                "slug": "general",
                "created_at": "2015-04-18 11:24:00",
                "updated_at": "2015-04-18 11:24:04"
            }
        }
    ]
}

With LengthAwarePaginator -

$paginator = new LengthAwarePaginator($events, count($events), 1);
    return $paginator;

This returns the paginator but the data is the same - meaning the same result set as without the paginator, when I'd expect only one record to be returned per page:

    [{
    "total": 3,
    "per_page": 1,
    "current_page": 1,
    "last_page": 3,
    "next_page_url": "/?page=2",
    "prev_page_url": null,
    "from": 1,
    "to": 3,
    "data": {
        "data" : ".. same as above"
    }
}]

我试着按月份、月份列出表中的条目:

  • May, 2015
    • Item 1
    • Item 2
    • Item 3
  • June, 2015
    • Item 1
    • etc..

我已经实现了这个用下面的代码但我想分页的结果。我已经尝试了许多不同的事情,但似乎没有人上班,我使用laravel 5。

$events = App\Events->orderBy('start', 'asc')->get()->groupBy(function($date) {
            return $date->start->format('F, Y');
        });

以下是上述查询的输出:

{
    "April, 2015": [
        {
            "id": "10",
            "event_type_id": "1",
            "user_id": "1",
            "title": "Testing",
            "slug": "testing",
            "start": "2015-04-23 17:00:00",
            "end": "2015-04-23 17:40:00",
            "description": "<h1>MEETING!</h1><p>Let's try this in HTML!<br></p>",
            "created_at": "2015-04-19 14:18:33",
            "updated_at": "2015-04-21 22:07:41",
            "type": {
                "id": "1",
                "name": "General",
                "slug": "general",
                "created_at": "2015-04-18 11:24:00",
                "updated_at": "2015-04-18 11:24:04"
            }
        }
    ],
    "May, 2015": [
        {
            "id": "12",
            "event_type_id": "1",
            "user_id": "1",
            "title": "Test Event",
            "slug": "test-event",
            "start": "2015-05-15 18:00:00",
            "end": null,
            "description": "<p>This is a test event with just a start time</p>",
            "created_at": "2015-04-21 14:59:56",
            "updated_at": "2015-05-02 18:37:53",
            "type": {
                "id": "1",
                "name": "General",
                "slug": "general",
                "created_at": "2015-04-18 11:24:00",
                "updated_at": "2015-04-18 11:24:04"
            }
        },
        {
            "id": "9",
            "event_type_id": "1",
            "user_id": "1",
            "title": "Monthly Meeting",
            "slug": "monthly-meeting",
            "start": "2015-05-23 14:00:00",
            "end": "2015-04-16 20:00:00",
            "description": "<p>It's a long monthly meeting!</p>",
            "created_at": "2015-04-19 13:13:45",
            "updated_at": "2015-05-03 08:45:56",
            "type": {
                "id": "1",
                "name": "General",
                "slug": "general",
                "created_at": "2015-04-18 11:24:00",
                "updated_at": "2015-04-18 11:24:04"
            }
        }
    ],
    "June, 2015": [
        {
            "id": "11",
            "event_type_id": "1",
            "user_id": "1",
            "title": "Another Meeting Saved",
            "slug": "another-meeting-saved",
            "start": "2015-06-19 18:00:00",
            "end": null,
            "description": "<p>It's another meeting afterall</p>",
            "created_at": "2015-04-20 15:03:30",
            "updated_at": "2015-05-03 08:46:19",
            "type": {
                "id": "1",
                "name": "General",
                "slug": "general",
                "created_at": "2015-04-18 11:24:00",
                "updated_at": "2015-04-18 11:24:04"
            }
        }
    ]
}

与lengthawarepaginator—

$paginator = new LengthAwarePaginator($events, count($events), 1);
    return $paginator;

这将返回页面但数据是相同的含义没有分页程序相同的结果,当我希望只有一个记录被返回的网页:

    [{
    "total": 3,
    "per_page": 1,
    "current_page": 1,
    "last_page": 3,
    "next_page_url": "/?page=2",
    "prev_page_url": null,
    "from": 1,
    "to": 3,
    "data": {
        "data" : ".. same as above"
    }
}]
answer1: 回答1:

With aggregates you need to implement your own custom paginator, as stated by docs:

Note: Currently, pagination operations that use a groupBy statement cannot be executed efficiently by Laravel. If you need to use a groupBy with a paginated result set, it is recommended that you query the database and create a paginator manually.

See this posts to manually implement pagination:

Laravel 5 - Manual pagination

Manually Creating a Paginator (Laravel 5)

你需要实现自己的自定义分页程序的集合体,正如医生:

Note: Currently, pagination operations that use a groupBy statement cannot be executed efficiently by Laravel. If you need to use a groupBy with a paginated result set, it is recommended that you query the database and create a paginator manually.

看到这个帖子手工实现分页:

5手动分页laravel

手动创建一个页面(laravel,5)

answer2: 回答2:

As stated in the Laravel docs...Note: Currently, pagination operations that use a groupBy statement cannot be executed efficiently by Laravel. If you need to use a groupBy with a paginated result set, it is recommended that you query the database and create a paginator manually. Docs

在laravel规定单据…注:目前,分页操作,使用GROUPBY语句不能被Laravel的有效执行。如果你需要使用GROUPBY和分页的结果集,建议您查询数据库并创建一个页面手动。文档

answer3: 回答3:

Many people have pointed me to a widely mentioned paragraph in the Laravel documentation,

Note: Currently, pagination operations that use a groupBy statement cannot be executed efficiently by Laravel. If you need to use a groupBy with a paginated result set, it is recommended that you query the database and create a paginator manually.

Not terribly helpful, since I cannot find any example in the documentation as to exactly how to create a manual paginator using the results of an Eloquent query. So, here is what I was able to come up with. Note that you must use ->take() and ->offset() in the query, otherwise you will end up with the same results on every page (this is where I was getting stuck).

<?php
// routes.php
use Illuminate\Pagination\LengthAwarePaginator as Paginator;
    get('test', function(Request $request) {
        $page = $request->has('page') ? $request->input('page') : 1; // Use ?page=x if given, otherwise start at 1
        $numPerPage = 2; // Number of results per page
        $eventType = EventType::find(1); // Not relevant to pagination
        $count = $eventType->memberEvents()->count(); // Get the total number of entries you'll be paging through
        // Get the actual items
        $events = $eventType->memberEvents()->orderBy('start', 'asc')
            ->take($numPerPage)->offset(($page-1)*$numPerPage)->get()->groupBy(function($date) {
            return $date->start->format('F, Y');
        });
        // Create the paginator with Illuminate\Pagination\LengthAwarePaginator as Paginator
        // Pass in the variables supplied above, including the path for pagination links
        $paginator = new Paginator($events, $count, $numPerPage, $page, ['path' => $request->url(), 'query' => $request->query()]);
        return $paginator;
    });

许多人向我指出一个被广泛提及的段落在laravel文档,

Note: Currently, pagination operations that use a groupBy statement cannot be executed efficiently by Laravel. If you need to use a groupBy with a paginated result set, it is recommended that you query the database and create a paginator manually.

没有太大帮助,因为我无法在文档中找到的例子是如何使用一个雄辩的查询结果创建一个手动分页程序。所以,这里是我能想出的。请注意,您必须使用>;take()和>;在查询offset(),否则你最终会在每一页上的结果是一样的(这就是我被卡住)。

<?php
// routes.php
use Illuminate\Pagination\LengthAwarePaginator as Paginator;
    get('test', function(Request $request) {
        $page = $request->has('page') ? $request->input('page') : 1; // Use ?page=x if given, otherwise start at 1
        $numPerPage = 2; // Number of results per page
        $eventType = EventType::find(1); // Not relevant to pagination
        $count = $eventType->memberEvents()->count(); // Get the total number of entries you'll be paging through
        // Get the actual items
        $events = $eventType->memberEvents()->orderBy('start', 'asc')
            ->take($numPerPage)->offset(($page-1)*$numPerPage)->get()->groupBy(function($date) {
            return $date->start->format('F, Y');
        });
        // Create the paginator with Illuminate\Pagination\LengthAwarePaginator as Paginator
        // Pass in the variables supplied above, including the path for pagination links
        $paginator = new Paginator($events, $count, $numPerPage, $page, ['path' => $request->url(), 'query' => $request->query()]);
        return $paginator;
    });
php  json  laravel  eloquent  laravel-5