baguus
2 years ago

ORM scope query with left join and group_concat

Posted 2 years ago by baguus

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?

Please sign in or create an account to participate in this conversation.