在使用laravel查询构造器的时候,需要用到mysql的一些函数,需要使用到DB::raw() 方法来处理,在处理过程中遇到的问题记录一下:
写法
DB::table('users')->select('users.*', DB::raw('CONCAT(rel.name, "=", rel.realname)'))->leftJoin('real as rel', function($join){
$join->on('rel.id', '=', 'users.id')
->where('rel.status', '=', 1)
})->first();
实际sql:
select `tr_users.*`, CONCAT(rel.name, '=', rel.realname) from `tr_users` left join
`tr_real` as `tr_rel` on `tr_rel.id` = `tr_users.id` and `tr_rel.status` = 1
报错:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'rel.name' in 'field list'
问题原因:
查询构造器在加入前缀的时候不会给DB::raw()函数中的字段添加,在思否中看到同样的问题,说可以通过DB::getTablePrefix()来获取前缀,于是来了灵感,做了如下修改问题解决
DB::table('users')->select('users.*', DB::raw('CONCAT('.DB::getTablePrefix().'rel.name, "=", '.DB::getTablePrefix().'rel.realname)'))->leftJoin('real as rel', function($join){
$join->on('rel.id', '=', 'users.id')
->where('rel.status', '=', 1)
})->first();
注:根据源码,表的别名添加的前缀是去不掉的,除非修改源码,下面是warp源码
public function wrapTable($table)
{
if ($this->isExpression($table)) {
return $this->getValue($table);
}
return $this->wrap($this->tablePrefix.$table, true);
}
public function wrap($value, $prefixAlias = false)
{
if ($this->isExpression($value)) {
return $this->getValue($value);
}
// If the value being wrapped has a column alias we will need to separate out
// the pieces so we can wrap each of the segments of the expression on it
// own, and then joins them both back together with the "as" connector.
if (strpos(strtolower($value), ' as ') !== false) {
$segments = explode(' ', $value);
if ($prefixAlias) {
$segments[2] = $this->tablePrefix.$segments[2];
}
return $this->wrap($segments[0]).' as '.$this->wrapValue($segments[2]);
}
$wrapped = [];
$segments = explode('.', $value);
// If the value is not an aliased table expression, we'll just wrap it like
// normal, so if there is more than one segment, we will wrap the first
// segments as if it was a table and the rest as just regular values.
foreach ($segments as $key => $segment) {
if ($key == 0 && count($segments) > 1) {
$wrapped[] = $this->wrapTable($segment);
} else {
$wrapped[] = $this->wrapValue($segment);
}
}
return implode('.', $wrapped);
}
if (strpos(strtolower($value), ' as ') !== false) {
$segments = explode(' ', $value);
if ($prefixAlias) {
$segments[2] = $this->tablePrefix.$segments[2];
}
return $this->wrap($segments[0]).' as '.$this->wrapValue($segments[2]);
}