mysql - Eloquent hasToMany 关系中的 Order By 先于 Group By

Question

我想获得按 project_id 排序的独特帖子基本上，如果有重复的帖子，我想要其中 project_id 的帖子不为空。 有什么办法可以得到这个吗？

此代码返回唯一的帖子，但它们不是按 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']);
}

如果我删除 groupBy ，我收到了 project_id 订购的重复帖子.

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

下面我展示了我得到的结果和我想要的结果:

I removed the additional data for better reading

结果为groupBy和orderBy条款。独特的帖子，但未按 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
        }
    }
],

没有 groupBy 的结果条款。重复的帖子按 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
        }
    }
],

我想要的结果。由 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
        }
    }
],

感谢您花时间帮助我。

Answer 1

检索帖子时，将返回一个集合。

然后，您可以使用 ->sortByDesc() collection method 按降序对结果进行排序(使用自定义逻辑) :

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

此处，帖子按帖子的枢轴post_id降序排序(不确定是否通过访问) -> 语法或数组键，因为我还没有测试过)。

要删除任何重复项(检查 project_id 是否不为空)，您应该使用 -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']);
}