Using Distinct in Laravel Fluent

Question

I have this join:

Return DB::table('volunteer')
            ->join('volunteer_volunteer_category', 'volunteer_volunteer_category.volunteer_id', '=', 'volunteer.id')
            ->select(array('*','volunteer.id AS link_id'))
            ->where('is_published', '=', 1)

But it unsurprisingly returns duplicate records, so I try to use distinct():

Return DB::table('volunteer')
            ->join('volunteer_volunteer_category', 'volunteer_volunteer_category.volunteer_id', '=', 'volunteer.id')
            ->select(array('*','volunteer.id AS link_id'))
                        ->distinct()
            ->where('is_published', '=', 1)

but I want to use distinct() on a specific single field which I'd easily be able to do in SQL. It seems distinct() does not take parameters, i.e. I can't say distinct('volunteer.id').

Can anyone point me to how can I remove my duplicate records? I bet this is another forehead slapper for me.

Phellem

Of course I'm being completely stupid here and need to add ->group_by('volunteer.id') as well as ->distinct(). – Phellem

But including ->group_by('volunteer.id') makes my pagination links disappear!!??? – Phellem

I believe that there's been a bug with pagination and group_by for a while now in Laravel 3; I don't know whether it's been resolved in Laravel 4. – Socialize

Answer 1

In my project I tried distinct() and groupby() too and both of them worked:

//Distinct version.
Company_Customer_Product::where('Company_id', '=', $companyid)->distinct()->get(array('Customer_id'));
//Goup by version.
Company_Customer_Product::where('Company_id', '=', $companyid)->groupby('Customer_id')->get(array('Customer_id'));

According to this, distinct() should work in your case too, just use it with get():

Return DB::table('volunteer')
   ->join('volunteer_volunteer_category', 'volunteer_volunteer_category.volunteer_id', '=', 'volunteer.id')
   ->select(array('*','volunteer.id AS link_id'))
   ->distinct()
   ->where('is_published', '=', 1)
   ->get(array('volunteer.id'));

Otherwise you don't need distinct() when you use groupby() so you could just use:

Return DB::table('volunteer')
   ->join('volunteer_volunteer_category', 'volunteer_volunteer_category.volunteer_id', '=', 'volunteer.id')
   ->select(array('*','volunteer.id AS link_id'))
   ->group_by('volunteer.id')
   ->where('is_published', '=', 1)
   ->get(array('volunteer.id'));

Cutaneous