laravel 5-具有不同查询的分页total() [英] laravel 5 - paginate total() of a query with distinct

问题描述

我有一个查询，可根据数据透视表中的值获取照片，该数据存储图片"和标签"之间的关系:

I have a query to get photos according to values in a pivot table, that stores the relation of "pics" and "tags":

#photos
$q = PicTag::select(DB::raw('distinct(pics.id)'),
                            'pics.url',
                            'pics.titel',
                            'pics.hits',
                            'pics.created_at',
                            'users.username',
                            'users.displayname')
                    ->leftJoin('pics', 'pics.id', '=', 'pic_tag.pic_id')
                    ->leftJoin('users','users.id','=','pics.user_id')
                    ->whereIn('pic_tag.tag_id', $tagids);

    if($cat)
        $q->where('typ',$cat);

    if($year)
        $q->where('jahrprod',$year);

$pics = $q->orderBy('pics.id','desc')
            ->paginate(30);

问题是，当为一张照片存储多个(相同)标签时，例如标签"，标签"和"tAG".然后，同一张照片将在我的画廊中显示3次.这就是为什么我在查询中使用distinct.

The problem is, when for a certain photo multiple (same) tags are stored like "Tag", "tag" and "tAG". Then the same photo would be shown 3 times in my gallery. That is why I use the distinct in the query.

然后图库可以，但是$pics->total()不会显示"87张照片"，而是显示"90张照片"，因为在分页中未使用distinct.在laravel 4中，我使用了groupBy('pics.id')，但这似乎不是最快的查询，而在laravel 5中，它给出了1的total()计数结果.

Then the gallery is ok, but $pics->total() does not show "87 photos" but for example "90 photos", because the distinct is not used in the pagination. In laravel 4, I used groupBy('pics.id'), but this did not seem to be the fastest query and with laravel 5 it gives me a total() count result of 1.

如何获得正确的total()值?

推荐答案

我知道这是一个古老的主题，但是它可以帮助其他人.

I know it's an old subject but it could help some other people.

我遇到了同样的问题，发现的唯一好的解决方案(低内存成本)是两次执行请求:

I faced the same problem and the only good solution (low memory cost) I found was to do the request in two times:

$ids = DB::table('foo')
    ->selectRaw('foo.id')
    ->distinct()
    ->pluck('foo.id');

$results = $query = DB::table('foo')
    ->selectRaw('foo.id')
    ->whereIn('foo.id', $ids)
    ->paginate();

我尝试了10万次结果，完全没有问题.

I tried this with 100k results, and had no problem at all.

这篇关于laravel 5-具有不同查询的分页total()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！