Laravel 5.1通过急切加载返回连接字段

I'm currently having an issue retrieving a concatenated cell containing all store comments in one cell.

我目前在检索包含一个单元格中所有商店评论的连锁单元格时遇到问题。

The way my database is set up: One store can have multiple comments, and every comment must relate back to the user.

我的数据库设置方式:一个商店可以有多个注释,每个注释都必须与用户相关。

Returning this normally though eager loading is fine, however what I'm trying to achieve is a single cell that contains a list of every comment for a store such as:

正常地返回这个虽然急切加载很好,但是我想要实现的是一个单元格,其中包含商店的每个评论的列表,例如:

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"]}

The two different methods I've tried to get this to work are: selecting the concatenated columns when I retrieve the stores:

我尝试使用它的两种不同方法是:在检索商店时选择连接列:

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

Which resulted in some field not found errors that I couldn't resolve.

这导致一些我发现无法解决的字段错误。

I've also tried this approach in my store model:

我也在我的商店模型中尝试过这种方法:

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');
}

However this only retrieves an empty cell.

但是,这只会检索一个空单元格。

Does anyone know where I've gone wrong in either approach? Thanks!

有谁知道我在哪里出错了?谢谢!

1 个解决方案

#1

Define the user relation of comment in StoreCommet Model:

在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');
  }
}

Now fetch like this:

现在这样取:

$stores = Store::with('StoreComment');

And If you want to have a customized property for StoreComment, define it like this in StoreComment Model:

如果您想为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;
  }
}

#1