Laravel join tables and concatenate rows

Question

So I have two tables, organizations and contacts. Both tables have column "email", what I need to do is to keep the name of organization, but in email column concatenate all emails (organization's + all contact emails).

Here are some versions that I tried with no luck

1) this one doesn't group:

$customers = DB::table('customers')
    ->whereRaw('LENGTH(customers.email) > 4')
    ->select([
        'customers.id',
        'customers.name',
        'customers.email'
    ]);

$contacts = DB::table('contacts')
    ->whereRaw('LENGTH(contacts.email) > 4')
    ->leftJoin('customers', 'contacts.customer_id', '=', 'customers.id')
    ->select([
        'customers.id',
        'customers.name',
        'contacts.email'
    ]);

return $customers
    ->union($contacts)
    ->select([
        'id',
        'name',
        DB::raw('GROUP_CONCAT(DISTINCT email, ", ") AS emails'),
    ])
    ->groupBy('id')
    ->get();

2) this one is actually pretty close, but it doesn't filter out entries where neither contact or customer entires have DB::raw('LENGTH(email) > 4')

return $customers = DB::table('customers')
                ->leftJoin('contacts', 'contacts.customer_id', '=', 'customers.id')
                ->select([
                    'customers.id',
                    'customers.name',
                    'registration',
                    DB::raw('GROUP_CONCAT(DISTINCT contacts.email, ", ") AS contact_emails'),
                    'customers.email'
                ])
                ->groupBy('customers.id')
                ->get();

3) I tried to get closer with subqueries (I know it would only filter out contacts with no emails)

3.1) Trying subquery 1 results in error: JoinClause::whereRaw() doesn't exist

return $customers = DB::table('customers')
            ->leftJoin('contacts', function($join) {
                $join->on('contacts.customer_id', '=', 'customers.id')
                    ->whereRaw('LENGTH(email) > 4');
            })...

3.2) This one produces the syntax error below:

return $customers = DB::table('customers')
            ->leftJoin('contacts', function($join) {
                $join->on('contacts.customer_id', '=', 'customers.id')
                    ->where(DB::raw('LENGTH(email) > 4'));
            })

1/2 PDOException in Connection.php line 333: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? group by customers.id' at line 1

2/2 QueryException in Connection.php line 713: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? group by customers.id' at line 1 (SQL: select customers.id, customers.name, registration, GROUP_CONCAT(DISTINCT contacts.email, ", ") AS contact_emails, customers.email from customers left join contacts on contacts.customer_id = customers.id and LENGTH(contacts.email) 4 group by customers.id)

3.3) some examples say I should do it this way, but this produces the error Not enough arguments for the on clause.

return $customers = DB::table('customers')
    ->leftJoin('contacts', function($join) {
        $join->on('contacts.customer_id', '=', 'customers.id');
        $join->on(DB::raw('LENGTH(contacts.email) > 4'));
    })

Answer 1

This works for me. No syntax errors and filters out contacts with length less than 4 characters:

DB::table('customers')
  ->leftJoin('contacts', function ($join) {
      $join->on('contacts.customer_id', '=', 'customers.id')
              ->where(DB::raw('length(contacts.email)'), '>', 4);
  })
  ->select([
      'customers.id',
      'customers.name',
      DB::raw('group_concat(distinct contacts.email separator ", ") AS contact_emails'),
  ])
  ->groupBy('customers.id')
  ->get();

Tested in Laravel 5.3.26, MySQL 5.6.20 (no strict mode).