Order By before Group By an Eloquent belongsToMany relationship

Question

I want to get unique posts ordering by project_id, basically, if there are duplicated posts I want the one in which project_id is not null. Is there any way to get this?

This code is returning unique posts, but they are not ordered by project_id.

/**
 * Get the posts record associated with the service.
 */
public function posts()
{
    return $this->belongsToMany('App\Post')
        ->withPivot('id', 'unity', 'coefficient', 'project_id')
        ->orderBy('project_id', 'DESC')
        ->groupBy(['post_id']);
}

If I remove the groupBy, I got duplicated posts ordered by project_id.

The output of dd($service->posts()->toSql());:

select * from `posts` 
inner join `post_service` on `posts`.`id` = `post_service`.`post_id` 
where `post_service`.`service_id` = ? 
group by `post_id` 
order by `project_id` desc

Below I show the results that I got and the result I desire:

I removed the additional data for better reading

Result with groupBy and orderBy clauses. Unique posts but not ordered by project_id:

"posts": [
    {
        "id": 733,
        "pivot": {
            "service_id": 20177,
            "post_id": 733,
            "id": 2575,
            "project_id": null
        }
    },
    {
        "id": 725,
        "pivot": {
            "service_id": 20177,
            "post_id": 725,
            "id": 2576,
            "project_id": null
        }
    }
],

Result without groupBy clause. Duplicated posts ordered by project_id:

"posts": [
    {
        "id": 733,
        "pivot": {
            "service_id": 20177,
            "post_id": 733,
            "id": 9723,
            "project_id": 4
        }
    },
    {
        "id": 733,
        "pivot": {
            "service_id": 20177,
            "post_id": 733,
            "id": 2575,
            "project_id": null
        }
    },
    {
        "id": 725,
        "pivot": {
            "service_id": 20177,
            "post_id": 725,
            "id": 2576,
            "project_id": null
        }
    }
],

Result I desire. Unique posts ordered by project_id:

"posts": [
    {
        "id": 733,
        "pivot": {
            "service_id": 20177,
            "post_id": 733,
            "id": 9723,
            "project_id": 4
        }
    },
    {
        "id": 725,
        "pivot": {
            "service_id": 20177,
            "post_id": 725,
            "id": 2576,
            "project_id": null
        }
    }
],

I thank you for taking the time to help me.

It won't be in order if you are also grouping by `post_id`. Did you mean you want it ordered within each group? — thisiskelvin, Apr 11 '19 at 13:45
@thisiskelvin I updated the question with result examples, please, check it out. — Rayann Nayran, Apr 11 '19 at 14:14

thisiskelvin · Answer 1 · 2019-04-11T14:58:43.950

0

When retrieving the posts a collection will be returned.

You can then order the results (using custom logic) in descending order by using the ->sortByDesc() collection method:

$posts = $service->posts
    ->sortByDesc(function ($post, $key) {
        return $post['pivot']['project_id'];
    })
    ->values()
    ->all();

Here the posts are sorted in decending order by the pivot's post_id of the post (not sure if this is accessed via -> syntax or array key as I haven't tested).

To remove any duplicates (checking if project_id is not null) you should use -wherePivot():

public function posts()
{
    return $this->belongsToMany('App\Post')
        ->withPivot('id', 'unity', 'coefficient', 'project_id')
        ->wherePivot('project_id', '=!', null)
        ->orderBy('project_id', 'DESC')
        ->groupBy(['post_id']);
}

edited Apr 11 '19 at 14:58

answered Apr 11 '19 at 14:26

thisiskelvin

4,136
1
10
17

Thank you for your answer. Your code is ordering, but it keeps duplicated posts. ps.: The order should be by project_id. – Rayann Nayran Apr 11 '19 at 14:33
Going to update my answer. What determines the post as unique, `project_id`? – thisiskelvin Apr 11 '19 at 14:52
The wherePivot condition will exclude posts with project id null, I Do not want it. – Rayann Nayran Apr 11 '19 at 16:15
I want to get unique posts ordering by project_id, basically, if there are duplicated posts I want keep the one wich project_id is not null. – Rayann Nayran Apr 11 '19 at 16:15
@RayannNayran Does this mean that if 2 posts both have `product_id`, both should be included in the returned results? – thisiskelvin Apr 11 '19 at 16:28
I Did not understand, check my result examples in the question. In the first ex. I got the post with id 733 but it pivot project_id is null. In the second I got two posts with id 733 in the correct order, but I want exclude the duplicated one with pivot project_id null. The third ex. I got two unique posts and the post with id 733 is the correct one with pivot project_id filled. – Rayann Nayran Apr 11 '19 at 16:45
The third result example is what I want to get. – Rayann Nayran Apr 11 '19 at 16:46

Order By before Group By an Eloquent belongsToMany relationship

1 Answers1