如何将列与Laravel 4连接起来?

I have a table called tenantdetails which contains

我有一个叫做tenantdetails的表格，它包含

Tenant_Id | First_Name | Last_Name | ........

and I want to retrieve First_Name and Last Name as one column via the concatenation function of MySQL. So I write in my controller as follows

我想通过MySQL的连接函数来检索First_Name和Last Name作为一列。我在控制器中写如下。

$tenants = Tenant::orderBy('First_Name')->lists('CONCAT(`First_Name`," ",`Last_Name`)','Tenant_Id');

But results the following error:

但导致以下错误:

 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 '`," ",`First_Name`)`, 
 `Id` from `tenantdetails` order by `F' at line 1 

 (SQL: select `CONCAT(`First_Name`," ",`Last_Name`)`, `Id` 
 from `tenantdetails` order by `First_Name` asc).

How can we avoid the backticks while calling a function of MySQL in Laravel Eloquent. I am interested only in Eloquent (not in fluent query). Thanks in advance.

如何避免在Laravel中调用MySQL函数时出现的反例。我只对口才感兴趣(不喜欢流利的问句)。提前谢谢。

Update

更新

Thanks to @Andreyco for helping me. We can achieve this in a more elegant way using Laravel models, as below:

感谢@Andreyco对我的帮助。我们可以使用Laravel模型以更优雅的方式实现这一点，如下所示:

In our model:

在我们的模型:

public function getTenantFullNameAttribute()
{
    return $this->attributes['First_Name'] .' '. $this->attributes['Last_Name'];
}

and in our controller:

在我们的控制器:

$tenants = Tenant::orderBy('First_Name')->get();
$tenants = $tenants->lists('TenantFullName', 'Tenant_Id');

4 个解决方案

#1

Tenant::select('Tenant_Id', DB::raw('CONCAT(First_Name, " ", Last_Name) AS full_name'))
    ->orderBy('First_Name')
    ->lists('full_name', 'Tenant_Id');

#2

An easy way is to use selectRaw. It was implemented by Tailor in Jan 30, 2014

一个简单的方法是使用selectRaw。它是由裁缝在2014年1月30日实施的。

Source

源

Tenant::selectRaw('CONCAT(First_Name, " ", Last_Name) as TenantFullName, id')->orderBy('First_Name')->lists('TenantFullName', 'id'))

#3

lists() method used to select column from selected result. So first contact first name and last name and give this column with new alias name in select statement

list()方法，用于从选定的结果中选择列。因此，在select语句中，首先联系姓名，然后给这个列加上新的别名

 $tenants = Tenant::orderBy('First_Name')->select(DB::row('CONCAT(`First_Name`," ",`Last_Name`) as name'),'Tenant_Id')->lists('name', 'id');

then you can select this alias in lists() method

然后可以在list()方法中选择这个别名

#4

You should use the DB::raw() to concat those of field

您应该使用DB::raw()来处理字段的那些

Tenant::select(
          'Tenant_Id',
          DB::raw('CONCAT(First_Name,"-",Last_Name) as full_name')

        )
       ->orderBy('First_Name')
       ->lists('full_name', 'Tenant_Id');

#1

Tenant::select('Tenant_Id', DB::raw('CONCAT(First_Name, " ", Last_Name) AS full_name'))
    ->orderBy('First_Name')
    ->lists('full_name', 'Tenant_Id');

#2