vitorf7's avatar

Paginating grouped results by title

Hi everyone,

I am slightly new with Laravel. I have been a member of Laracasts for a while and I have learned a lot here however the companies I worked for never wanted to do anything in Laravel. Thank God I finally started a new job where I can work with Laravel. I am however being faced with a really tricky situation.

The Project is using Laravel 4. The project is basically doing 2 sites, 1 is an API and the second a front end site that feeds off of the API.

We have a table called media_files which is being queried like:

$mediaFiles = $this->entity->filtered()->paginate($this->perPage);

return $this->buildCollection(
    $mediaFiles,
    $this->mediaFileTransformer
);

They need the results to be grouped by Title and somewhere else they have basically done a foreach loop which creates an array of objects from the collection with the title as its key, like so:

foreach ($mediaFiles['data'] as $file) {
    $groupedFiles['data'][$file['title']][] = $file;
}

However of course now the pagination results are all wrong because the original paginate method from the Query builder will be showing the original pagination before the grouping via the loop was done. This is because you might have 3-4 files which have all the same title but different IDs (this is because they will upload different file formats of the same file but want to keep the same title so they can group it. At least that was the idea of the original developer who did this).

My question is if there is a way that I can somehow query the database and get a result that is the same as the loop so that the pagination is correct?

Edit:

We have come up with an SQL query which solves the problem the developers were facing however it is proving to be difficult to get it working. In the DB we can use it and returns about 89K results but I am not being able to do it with Query Builder as it is a crazy query.

SELECT media_files.*, GROUP_CONCAT(media_files.zencoder_job_id SEPARATOR ',') AS children_ids
FROM media_files
GROUP BY media_files.zencoder_job_id,
CASE WHEN media_files.zencoder_job_id IS NULL
THEN media_files.id
ELSE 0
END

Thanks in advance

0 likes
9 replies
pmall's avatar

Select all the distinct title then paginate them. Find a way to eager load the related files.

By the way, if you really need this because you need a better data structure. It would be so much easier with a title entity which has many files.

vitorf7's avatar

@pmall That would be difficult since everything is in the same table isn't it? The original returned JSON is:

{
    "data": [
        {
            "id": 1,
            "file_url": {
                "original": "http://myuploads.com/my_video.flv",
                "thumbnail": null
            },
            "file_file_name": "my_video.flv",
            "file_content_type": "application/octet-stream",
            "media_type": "video",
            "title": "My Video Advert"
        }, 
        {
            "id": 2,
            "file_url": {
                "original": "http://myuploads.com/my_video.mp4",
                "thumbnail": null
            },
            "file_file_name": "my_video.mp4",
            "file_content_type": "video/mp4",
            "media_type": "video",
            "title": "My Video Advert"
        }, 
        {
            "id": 3,
            "file_url": {
                "original": "http://myuploads.com/my_video.mov",
                "thumbnail": null
            },
            "file_file_name": "my_video.mov",
            "file_content_type": "application/octet-stream",
            "title": "My Video Advert"
        }
    ]
}

But the way we need it to be returned is:

{
    "data": {
        "My Video Advert": [
            {
                "id": 1,
                "file_url": {
                    "original": "http://myuploads.com/my_video.flv",
                    "thumbnail": null
                },
                "file_file_name": "my_video.flv",
                "file_content_type": "application/octet-stream",
                "media_type": "video",
                "title": "My Video Advert"
            }, 
            {
                "id": 2,
                "file_url": {
                    "original": "http://myuploads.com/my_video.mp4",
                    "thumbnail": null
                },
                "file_file_name": "my_video.mp4",
                "file_content_type": "video/mp4",
                "media_type": "video",
                "title": "My Video Advert"
            }, 
            {
                "id": 3,
                "file_url": {
                    "original": "http://myuploads.com/my_video.mov",
                    "thumbnail": null
                },
                "file_file_name": "my_video.mov",
                "file_content_type": "application/octet-stream",
                "title": "My Video Advert"
            }
        ]
    }
}

So I am not sure how I would try go about doing it the way you are saying. Also unfortunately the project is so far ahead that now it will be too much of a headache to change that so that we have an Entity with the Title (which I agree it would make more sense).

pmall's avatar

So select the distinct titles and paginate them.

$titles = MyFile::select('title')->distinct()->paginate();

Select all the files related to these titles, and group them by title (with the group by method of the collection, not the sql one).

$file_ids = $titles->lists('id')->all();

$files_grouped_by_titles = MyFile::whereIn('id', $file_ids)->get()->groupBy('title');

So you have a list of title => list of files pairs.

So you can loop through all the titles of the page and display the related files.

vitorf7's avatar

@pmall I have just tried it but the lists is returning a list of empty values. Could this be because the ID field is in the $guarded array?

pmall's avatar

No it is totally unrelated. What I've showed above is kind of pseudo code just to show you how to proceed. Adapt it to your code/data structure.

Edit : it must be because titles is a paginator and not a collection. Do it like this :

$file_ids = $titles->getCollection()->lists('id')->all();
vitorf7's avatar

@pmall For some weird reason it still is empty. On the other hand another dev noticed that grouping by title is not the best of options :/ as some pictures might have the same name but be from different stories/clients etc. He and I found a good SQL query that runs fast on our DB but I am trying to find how to do it in Query Builder.


SELECT media_files.*, GROUP_CONCAT(media_files.zencoder_job_id SEPARATOR ',') AS children_ids
FROM media_files
GROUP BY media_files.zencoder_job_id,
CASE WHEN media_files.zencoder_job_id IS NULL
THEN media_files.id
ELSE 0
END

Sorry for being a headache

pmall's avatar

I cant help you. Your data structure is not suited to what you want to do with it, it will always be a headache. My advice would be, refactor your code so you have file groups with titles and related files.

vitorf7's avatar

@pmall Yes I know it is easier said than done. Especially when you pick up projects that have been developed by other developers (contractors) that no longer are here and the project needs to go live quickly.

Unfortunately as much as I agree with you I will have to find a way to execute that SQL query with Laravel as it seems to be doing what we need for now.

Thanks

vitorf7's avatar
vitorf7
OP
10 years ago
Best Answer
Level 9

@pmall Once again thank you so much for your help on this. In the end we looked at the DB structure we inherited from the legacy system and we noticed there was something else we had which could be better used for grouping. The media files had a zencoder_job_id field which was used mainly for the video files that get encoded by Zencoder and this is why initially the developer that started this project was grouping by title (not very good). In the end we went with a query like this:


     $orderBy = \DB::table('media_files')->orderBy('id', 'DESC');

    // Search Media and then group it by zencoder_job_id where it is not null
    $mediaFilesResults = \DB::table(DB::raw("({$orderBy->toSql()}) as sub"))
        ->whereIn('id', $whereInIds)
        ->groupBy(\DB::raw("COALESCE(zencoder_job_id, id)"))
        ->orderBy('id', 'DESC')
        ->paginate($perPage);

It ended up being that this worked perfectly.

Please or to participate in this conversation.