Counting distinct or unique rows from multiple columns with count in Laravel

I am building a small application in Laravel 5.6 where I am having a model under the name of AssociatedCompany:

<?php
namespace Noetic\Plugins\Conxn\Models\Project;

use Illuminate\Database\Eloquent\SoftDeletes;
use Nitseditor\System\Models\AbstractModel;

class AssociateCompany extends AbstractModel {


    use SoftDeletes;

    protected $fillable = [
        'project_id', 'company_role_id', 'company_specialisation_id', 'company_id', 'link', 'file_name'
    ];

    /**
     * The attributes that should be hidden for arrays.
     *
     * @var array
     */
    protected $hidden = [
        'created_at','updated_at','deleted_at'
    ];

    /**
     *  Columns for finding inside the table
     */
    public static $columns = [
        'project_id',  'company_role_id', 'company_id',
    ];


    /**
     * All of the relationships to be touched.
     *
     * @var array
     */
    protected $touches = ['project'];

    public function project()
    {
        return $this->belongsTo('Noetic\Plugins\Conxn\Models\Project','project_id','id');
    }

    public function company()
    {
        return $this->belongsTo('Noetic\Plugins\Conxn\Models\Company','company_id','id');
    }

    public function companyRole()
    {
        return $this->belongsTo('Noetic\Plugins\Conxn\Models\Variables\Company\Role',
            'company_role_id','id');
    }

    public function specialisation()
    {
        return $this->belongsTo('Noetic\Plugins\Conxn\Models\Variables\Company\Role',
            'company_specialisation_id','id');
    }
}

I want to find distinct rows with company_role_id, company_specialisation_id, company_id columns and count them and then push values so that I return this through my controller have:

public function ownerRelationship(Request $request)
{
    $companies = Company::whereIn('slug', collect($request->companies)->pluck('slug'))->get();

    $associated = AssociateCompany::whereHas('company', function ($q) use($companies) {
        $q->whereIn('slug', $companies->pluck('slug'));
    })->get();

    return response()->json(['owner_relationship' => $associated], 200);
}

I found the solution to this link: Count distinct values but in this they are finding distinct relation only for one columns but I need to have distinct relationship for 3 columns.

Even I tried:

$associated = AssociateCompany::whereHas('company', function ($q) use($companies) {
    $q->whereIn('slug', $companies->pluck('slug'));
})->groupBy('company_id', 'company_role_id', 'company_specialisation_id')
    ->with('project', 'company', 'companyRole', 'specialisation')->get();

But this code is not grouping properly. It is throwing me error that associate_companies.id is not grouped and I don't know how to proceed for counts.

Help me out in this, thanks.

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/2oS5xvk
via IFTTT