我当前在检索一个单元格中包含所有存储注释的串联单元格时遇到问题。
我的数据库设置方式:一个商店可以有多个评论,每个评论都必须与用户相关。
正常情况下,尽管加载很快,但返回这个是可以的,但是我试图实现的是一个单元格,它包含一个商店的每个注释的列表,例如:
store1{Comments:["10-10-2015 - Comment 1 - User 1'n10-10-2015 - Comment 2 - User2"]},
store2{Comments:["10-10-2015 - Comment 3 - User3'n10-10-2015 - Comment 4 - User4'n10-10-2015 - Comment 5 - User5"]}
我尝试过的两种不同的方法是:在检索存储时选择连接的列:
return $stores = Store::with('StoreComment','StoreComment.CreatedBy')
->select(DB::raw("group_concat(DATE_FORMAT(storecomment.created_at,'%Y-%m-%d'), ' - ', Comment, ' - ', ShortName, ''n' ORDER BY storecomment.created_at DESC SEPARATOR '') as storecomments"))
->groupBy('store.StoreID')
->get();
这导致了一些我无法解决的字段未找到错误。
我在我的商店模型中也尝试过这种方法:
public function FormattedComments()
{
return $this->hasOne('App'Models'StoreComment','StoreID','StoreID')
->join('users','StoreComment.created_by','=','users.UserID')
->select(DB::raw("group_concat(DATE_FORMAT(StoreComment.created_at,'%Y-%m-%d'), ' - ', Comment, ' - ', ShortName, ''n' ORDER BY StoreComment.created_at DESC SEPARATOR '')"))
->groupBy('StoreID')
->whereNull('StoreComment.deleted_at')
->orderBy('StoreComment.created_at','DESC');
}
但是,这只检索一个空单元格。
有人知道我在这两种方法中哪里出了问题吗?谢谢
定义StoreCommet模型中注释的用户关系:
class StoreComment extends Model
{
public function user()
{
return $this->belongsTo('App'User','column name in which you have stored user id in store_comment table');
}
}
class Store extends Model
{
public function StoreComment()
{
return $this->hasMany('App'StoreComment','StoreID','StoreID')->with('user');
}
}
现在像这样获取:
$stores = Store::with('StoreComment');
如果你想为StoreComment定制一个属性,可以在StoreComment模型中这样定义:
class StoreComment extends Model
{
protected $appends = ['formattedComment'];
public function user()
{
return $this->belongsTo('App'User','column name in which you have stored user id in store_comment table');
}
public function getFormattedCommentAttribute()
{
return $this->Comment.' - '.$this->ShortName;
}
}