laravel 5 - paginate total() of a query with distinct

Question

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);

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.

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.

How could I get the right total() value?

score 2 · Answer 1 · answered Mar 13 '17 at 18:13

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();

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

score 0 · Answer 2 · answered Apr 26 '15 at 08:03

Laravel has issue in paginate of complex queries. so you should handle them manually . In laravel 5 I did it in 2 steps :

Step 1: repository method :

  public function getByPage($page = 1, $limit = 10 , $provinceId ,    $cityId  , $expertiseId)
{
    $array = ['users.deleted' => false];
    $array["users.approved"] = true;
    $array["users.is_confirmed"] = true;
    if($provinceId)
        $array["users.province_FK"] = $provinceId;
    if($cityId)
        $array["users.city_FK"] = $cityId;
    if($expertiseId)
        $array["ONJNCT_USERS_EXPERTISE.expertise_FK"] = $expertiseId;

    $results = new \stdClass();
    $results->page = $page;
    $results->limit = $limit;
    $results->totalItems = 0;
    $results->items = array();
    $users=  DB::table('users')
        ->distinct()
        ->select('users.*','ONDEGREES.name as drgree_name')
        ->join('ONJNCT_USERS_EXPERTISE', 'users.id', '=', 'ONJNCT_USERS_EXPERTISE.user_FK')
        ->join('ONDEGREES', 'users.degree_FK', '=', 'ONDEGREES.id')
        ->where($array)
        ->skip($limit * ($page - 1))->take($limit)->get();
    //$users = $usersQuery>skip($limit * ($page - 1))->take($limit)->get();
    $usersCount=  DB::table('users')
        ->select('users.*','ONDEGREES.name as drgree_name')
        ->join('ONJNCT_USERS_EXPERTISE', 'users.id', '=', 'ONJNCT_USERS_EXPERTISE.user_FK')
        ->join('ONDEGREES', 'users.degree_FK', '=', 'ONDEGREES.id')
        ->where($array)
        ->count(DB::raw('DISTINCT users.id'));
    $results->totalItems = $usersCount;
    $results->items = $users;
    return $results;
}

Step 2: In my Search Controller :

  function search($provinceId , $cityId , $expertiseId){
     $page = Input::get('page', 1);
     $data = $this->userService->getByPage($page, 1 , $provinceId ,$cityId , $expertiseId);
    $users =  new LengthAwarePaginator($data->items, $data->totalItems, 1 , Paginator::resolveCurrentPage(),['path' => Paginator::resolveCurrentPath()]);
    return View::make('guest.search.searchResult')->with('users' ,$users);
}

It worked for me well!

laravel 5 - paginate total() of a query with distinct

2 Answers2

Linked