ORM scope query with left join and group_concat

Hi guys I want to prepare queries for Excel download. I have 2 models. User and Company with belongsTo/hasMany relationship. User belongs to one company and company can have many users (or none).

I have relations defined

/* User model*/
public function company() {
        return $this->belongsTo(Company::class);
}

/* Company model */
public function users() {
        return $this->hasMany(User::class, 'company_id');
}

I want to produce a query like this

SELECT companies.`name` AS "Company Name", companies.address AS "Company Address",
GROUP_CONCAT(users.username) AS "Users"
FROM Companies
LEFT JOIN users ON users.company_id = companies.id
GROUP BY companies.`name`

Can this be acheived with Laravel ORM or would it be better to write query directly

Something like single model query?

public function scopeUsers($query) {
        return $query->select(
        'companies.name AS Company Name',
        'companies.address AS Company Address')->get();
}

// Can I add LEFT JOIN and GROUP_CONCAT here?

baguus

8 years ago

Level 1

I'm not sure if this is the best practice but this works :)

public function scopeCompanies($query) {
        return $query->
        leftJoin('users', 'company_id', '=', 'companies.id')->
        select(
            DB::raw('GROUP_CONCAT(username) AS Users'),
            'companies.index AS Index',
            'companies.name AS Company Name',
            'companies.address AS Address')
            ->groupBy('companies.index')
            ->orderBy('Users','DESC')
            ->get();
}

Please or to participate in this conversation.