我在用Laravel 4 with MySQL后端。
我有两个数据库表，即 -surveyes and templates.
两张表都是self-referencing他们有column named parent那是foreign key该表本身。他们的记录在self referencing parent-child关系。
表格设计如下：
调查：
(PK) (FK_Surveyes)
Id title type parent sort_order deleted_at
1 General group NULL 1 NULL
2 Where..? question 1 1 NULL
3 abc.. answer 2 1 NULL
4 def.. answer 2 2 NULL
5 efg.. answer 2 3 NULL
6 ghi.. answer 2 4 NULL
7 What..? question 1 2 NULL
8 hij.. answer 7 1 NULL
9 ijk.. answer 7 2 NULL
10 How..? question 8 1 NULL
11 jkl.. answer 10 1 NULL
12 mnm.. answer 10 2 NULL
13 Special group NULL 2 NULL
14 Whom..? question 13 1 NULL
15 opq.. answer 14 1 NULL
16 rst.. answer 14 2 NULL
模板：
(PK)(FK_surveyes) (FK_Templates)
Id survey_id type parent sort_order deleted_at
1 NULL group NULL 1 NULL
2 14 question 1 1 NULL
3 15 answer 2 1 NULL
4 16 answer 3 2 NULL
5 NULL group NULL 2 NULL
6 2 question 5 1 NULL
7 3 answer 6 1 NULL
8 4 answer 6 2 NULL
9 5 answer 6 3 NULL
10 6 answer 8 4 NULL
11 7 question 10 2 NULL
12 8 answer 10 1 NULL
13 9 answer 10 2 NULL
现在，我希望他们的记录也以同样的方式与N层次结构的级别。
这样我就创建了一个model for templates如下 ：
class Template extends BaseModel{
protected $table = 'templates';
protected $softDelete = false;
// loads only direct children - 1 level
public function child()
{
return $this->hasMany('Template', 'parent');
}
// recursive, loads all descendants
public function children()
{
return $this->child()->with('children')->orderBy('sort_order');
}
// parent
public function parent()
{
return $this->belongsTo('Template','parent');
}
// all ascendants
public function parentRecursive()
{
return $this->parent()->with('parentRecursive');
}
}
我正在使用下面的方法（效果很好）来获取N模板的层次结构级别：
public function getTemplates(){
$templates = Template::with('children')
->whereNull('parent')
->orderBy('sort_order', 'ASC');
return $templates->toJson();
}
但现在，我想要这个专栏title来自surveyes桌子。专栏survey_id在表中templates is the foreign key表的surveyes.
我怎样才能实现它？
我已经更新了以下方法Template型号如下：
public function children()
{
return $this->child()->with('children')->orderBy('sort_order')
->leftJoin('surveyes', 'surveyes.id', '=', 'templates.survey_id')->select('templates.*','surveyes.title');
}
But it 不提供分层记录，浏览器会挂起。我只有1500记录在templates table.
有人知道如何实现它吗？
EDIT :
我添加了一个方法来获取Survey模板中的模型如下：
public function survey()
{
return $this->belongsTo('D2D\Models\Survey','survey_id');
}
并更新了children()方法如下：
public function children()
{
return $this->child()
->with('survey','children')
->orderBy('sort_order');
}
现在我可以获得的记录Survey模型，但它返回的所有列Survey如下 ：
{
"id": 2,
"survey_id": 522,
"title": "Abc....?",
"type": "question",
"parent": 1200
"survey": {
"id": 522,
"type": "question",
"subtype": null,
"title": "Abc....?",
"parent": 1
},
"children": [{
"id": 3,
"survey_id": 526,
"title": "aaa",
"type": "answer",
"parent": 2
"survey": {
"id": 526,
"type": "answer",
"subtype": null,
"title": "aaa",
"parent": 522
},
"children": []
},
{
"id": 4,
"survey_id": 527,
"title": "bbb",
"type": "answer",
"parent": 2
"survey": {
"id": 527,
"type": "answer",
"title": "bbb",
"parent": 522,
},
"children": []
},
...
}
但我想要如下所示：
{
"id": 2,
"survey_id": 522,
"title": "Abc....?", // Must be from Survey table
"type": "question",
"parent": 1200
"children": [{
"id": 3,
"survey_id": 526,
"title": "aaa", // Must be from Survey table
"type": "answer",
"parent": 2
"children": []
},
{
"id": 4,
"survey_id": 527,
"title": "bbb", // Must be from Survey table
"type": "answer",
"parent": 2
"children": []
},
...
}
有什么办法可以实现这一点吗？
我尝试更新 Survey() 方法，如下所示：
public function survey()
{
return $this->belongsTo('D2D\Models\Survey','survey_id')->select(array('title'));
}
但在这种情况下，它会为每个元素给出 title=NULL 。
请让我知道如何实现这一目标？
Thanks.
