mysql - 拉拉维尔 5 : Trouble with putting correct data on screen after combining tables

Question

到目前为止，我正在开发一个 Laravel-5 项目，我想构建某种事件日志。我确实有一个查询，它组合了多个表并为我提供了所需的数据，但是当我尝试将数据显示在屏幕上时，事情就出错了。

这是我现在在 ProfileController 中使用的查询:

$activity = DB::select("SELECT user_id, created_at, source, project_id
    FROM (
        SELECT id as project_id, user_id, created_at, 'project' as source FROM projects
        UNION ALL SELECT project_id, user_id, created_at, 'comment' as source FROM comments
        UNION ALL SELECT project_id, user_id, created_at, 'favorite' as source FROM favorites
        UNION ALL SELECT project_id, user_id, created_at, 'like' as source FROM likes
    ) as a
    WHERE user_id = $id
    ORDER BY created_at DESC");

这就是我的 profile.blade.php 中的代码:

<ul class="list-group">
@foreach($activity as $act)
    @if($act->source == 'project')
        <li class="list-group-item">{{ $act->user_id }} added a new project: {{ $act->project_id }}</li>
    @elseif($act->source == 'like')
        <li class="list-group-item">{{ $act->user_id }} likes {{ $act->project_id }}!</li>
    @elseif($act->source == 'comment')
        <li class="list-group-item">{{ $act->user_id }} commented on {{ $act->project_id }}</li>
    @elseif($act->source == 'favorite')
        <li class="list-group-item">{{ $act->user_id }} has {{ $act->project_id }} to his favourites!</li>
    @endif
@endforeach
</ul>

这就是我的模型的样子:

class Project extends Model
{
    protected $fillable = [

        'title',
        'description',
        'user_id',
        'snapshot',
        'views'
    ];

    public function creator() {
         return $this->belongsTo('App\User', 'user_id', 'id');
    }

    public function tags() {
         return $this->belongsToMany('App\Models\Tag');
    }
}

<小时/>

class Likes extends Model
{
    protected $fillable = [
        'user_id',
        'project_id'
    ];

    public function like() {
         return $this->belongsTo('App\User', 'user_id', 'id');
    }

    public function project() {
         return $this->belongsTo('App\Models\Project', 'project_id', 'id');
    }
}

<小时/>

class Comment extends Model
{
    protected $fillable = [
        'user_id',
        'project_id',
        'comment'
    ];

    public function poster() {
         return $this->belongsTo('App\User', 'user_id', 'id');
    }

    public function project() {
         return $this->belongsTo('App\Models\Project', 'project_id', 'id');
    }
}

<小时/>

class Favorite extends Model
{
    protected $fillable = [
        'user_id',
        'project_id'
    ];

    public function favorite() {
         return $this->belongsTo('App\User', 'user_id', 'id');
    }

    public function project() {
         return $this->belongsTo('App\Models\Project', 'project_id', 'id');
    }
}

现在我知道我只是要求在 Blade 页面上提供 user_id 或 project_id，我这样做是因为 $act->creator->name 或 $act->project->title 之类的东西会给我错误如下:

Undefined property: stdClass::$creator

或

Undefined property: stdClass::$project

我确实知道这可能与我构建查询的方式有关，并且以这种方式混合表可能会使事情变得比应有的更加困难，但我似乎找不到构建此查询的方法在 Eloquent 中查询或找到一种方法来访问属于这些 user_id 和 project_id 的数据，而无需使用我在模型中创建的公共(public)函数。

Answer 1

使用当前设置，您无法从 Laravel 模型中受益，因为您需要循环访问不同的模型。我的建议是建立一个名为 Activity 的新表，其中包含 3 列:id、type、user_id。并使用多态关系。

此外，您还可以从事件/监听器中受益，以便在事件表中创建记录。您可以从排队监听器中受益，因此如果不会影响您的客户体验，则不会在线创建事件记录。