php - Laravel 将多行合并为多列

Question

所以我有四个表:

• 实验(表格)- id
• feature_classes(列)- id、experiment_id、标题
• 实体(行)- id、experiment_id
• 特征(单元格)- id、entity_id、feature_class_id、值

我需要从这四个表构建一个表。

我试过这个:

$experiment_id = $request->input('experiment_id');
    $feature_classes = FeatureClass::where('experiment_id', $experiment_id)->select('title', 'id')->get();

    $select = [
        'entities.id',
        'entities.prediction',
        'entities.result'
    ];

    foreach ($feature_classes as $f) {
        $select[] = $f->id . ".value AS " .$f->id;
    }

    $entities = DB::table('entities')
        ->where('experiment_id', $experiment_id);

    foreach ($feature_classes as $f) {
        $entities = $entities->leftJoin('features AS ' . $f->id, function ($join) use ($f){
            $join->on($f->id . '.entity_id', '=', 'entities.id')
                ->where($f->id . '.feature_class_id', $f->id);
        });
    }

    return $entities
        ->select($select)
        ->get();

但我的努力得到了错误消息 SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax;查看与您的 MariaDB 服务器版本对应的手册，了解在 '1 附近使用的正确语法？ left joinfeaturesas2on2.entity_id=实体.idand2.fe' at line 1 (SQL: select entities.id, entities .预测, entities.result, 1.value as 1, 2.value as 2 from entities left join features as 1 on 1.entity_id = entities.id 和1.feature_class_id 1 left join features as 2 on 2. entity_id = entities.id and 2.feature_class_id 2 其中experiment_id = 1)`

Answer 1

我认为您不必这样做。您应该通过仅在模型中添加多对多关系来解决这个问题，然后使用 Eloquent 来完成其余所有工作。例如，在特征模型中:

public function Classes(){
    return $this->belongsToMany('App\Classes', 'feature_classes', 'feature_class_id', 'id');
}

然后是类似的东西，它定义了通过 experiment_id 链接的类和实体之间的关系。然后你应该能够通过使用像

这样的本地 Eloquent 功能来访问你需要的数据

$entities = Features::where('experiment_id', $experiment_id)->Classes->Entities;

return $entities

更多信息在这里:

https://laravel.com/docs/5.3/eloquent-relationships#many-to-many