laravel mysql长连接_mysql-Laravel连接表并连接行

所以我有两个表,组织和联系人.两个表都具有“电子邮件”列,我需要做的是保留组织名称,但是在电子邮件列中将所有电子邮件(组织的所有联系电子邮件)连接起来.

这是我没有运气尝试过的一些版本

1)这个不分组：

$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)这实际上很接近,但是它不会过滤掉联系人或客户整体都没有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)我试图与子查询更加紧密(我知道它只会过滤掉没有电子邮件的联系人)

3.1)尝试子查询1导致错误：JoinClause :: whereRaw()不存在

return $customers = DB::table('customers')

->leftJoin('contacts', function($join) {

$join->on('contacts.customer_id', '=', 'customers.id')

->whereRaw('LENGTH(email) > 4');

})...

3.2)这会产生以下语法错误：

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)一些示例说我应该这样做,但这会产生错误：on子句的参数不足.

return $customers = DB::table('customers')

->leftJoin('contacts', function($join) {

$join->on('contacts.customer_id', '=', 'customers.id');

$join->on(DB::raw('LENGTH(contacts.email) > 4'));

})

解决方法:

这对我有用.没有语法错误,并且筛选出长度少于4个字符的联系人：

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

在Laravel 5.3.26,MySQL 5.6.20(无严格模式)中进行了测试.

标签：laravel,laravel-5-2,mysql

来源： https://codeday.me/bug/20191112/2023797.html