Using Distinct in Laravel Fluent

Glenn Williams picture Glenn Williams · Apr 30, 2013 · Viewed 34.2k times · Source

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.

Answer

totymedli picture totymedli · Aug 12, 2013

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