Laravel Eloquent从自引用表以及其他表的列获取N级层次结构记录 [英] Laravel Eloquent get N Level hierarchy records from self-referencing table with other table's columns
问题描述
我正在将Laravel 4与MySQL后端一起使用.
I am using Laravel 4 with MySQL back-end.
我有两个数据库表-surveyes和templates.
I have two database tables namely - surveyes and templates.
这两个表都是self-referencing，它们都有名为parent的column，即该表本身的foreign key.他们的记录处于self referencing parent-child关系.
Both of the tables are self-referencing and they have column named parent that is foreign key of that table itself. Their records are in self referencing parent-child relationship.
表设计如下:
调查:
(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层次结构相同的方式.
Now, I want their records also to be in the same manner with N level of hierarchy.
这样我就为templates创建了model，如下所示:
So that I have creates a model for templates as below :
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级别:
And I am using method below (which is working fine) to get the N level of hierarchy for templates :
public function getTemplates(){
$templates = Template::with('children')
->whereNull('parent')
->orderBy('sort_order', 'ASC');
return $templates->toJson();
}
但是现在，我想要surveyes表中的列title.表templates中的列survey_id是表surveyes的foreign key.
But now, I want the column title from the surveyes table. The column survey_id in table templates is the foreign key of table surveyes.
如何实现?
我在Template模型中更新了以下方法，如下所示:
I have updated following method in Template model as below :
public function children()
{
return $this->child()->with('children')->orderBy('sort_order')
->leftJoin('surveyes', 'surveyes.id', '=', 'templates.survey_id')->select('templates.*','surveyes.title');
}
但是它没有给出层次记录，浏览器挂断.我在templates表中只有 1500 条记录.
But it doesn't gives the hierarchical records and browser gets hang-up. I have only 1500 records in templates table.
有人知道如何实现吗?
我添加了一种在模板中获取Survey模型的方法，如下所示:
I have added a method to get Survey model in templates as below :
public function survey()
{
return $this->belongsTo('D2D\Models\Survey','survey_id');
}
并更新了children()方法，如下所示:
and updated the children() method as below :
public function children()
{
return $this->child()
->with('survey','children')
->orderBy('sort_order');
}
现在我可以获取Survey模型的记录，但是它返回Survey的所有列，如下所示:
Now I am able to get the records of Survey model but it returns all the column of the Survey as below :
{
"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": []
},
...
}
有什么办法可以做到这一点?
Is there any way to achieve this?
我尝试通过更新以下的survey()方法来进行尝试:
I have tried by updating survey() method as below :
public function survey()
{
return $this->belongsTo('D2D\Models\Survey','survey_id')->select(array('title'));
}
但是在那种情况下，它为每个元素都提供title = NULL.
But in that case it gives title=NULL for each and every element.
请让我知道如何实现?
Please let me know how to achieve this?
谢谢.
推荐答案
对此问题的一种可能的解决方案是使用嵌套集模型.这避免了递归查询的问题.有两个可用的Laravel软件包，它们扩展了Eloquent以实现嵌套集: Laravel-NestedSet 和鲍姆.我了解您遇到了现有数据的问题；但是，制作一次性迁移脚本并不难.您可以使用选择的任何嵌套集库创建新模型；这些模型将具有设置调查的父节点的方法.您可以在新表格中拥有新的调查数据(您可以在Eloquent中覆盖表格名称； 然后，编写脚本或受保护的控制器操作，以将现有的调查迁移到新表中.它的工作方式如下(使用laravel-nestedset语法； Baum相似):
One possible solution to this problem is to use the Nested Sets model. This avoids the problem of recursive queries. There are two available Laravel packages that extend Eloquent to implement Nested Sets: Laravel-NestedSet and Baum. I understand you have the issue of existing data; however, it shouldn't be too hard to make a one-time migration script. You can create new models using whichever nested sets library you pick; these models will have methods to set the parent node of a survey. You can have the new survey data in a new table (you can override the table name in Eloquent; see here. Then, write a script or protected controller action to migrate the existing surveys to the new table. It will work something like this (using the laravel-nestedset syntax; Baum is similar):
$oldSurveys = Survey::all();
$oldSurveys->each(function($survey) {
$newSurvey = NestedSurvey::create(array($survey->name, $survey->type ...etc));
});
$newSurveys = NestedSurvey::whereNotNull('parent_id');
$newSurveys->each(function($survey) {
$parent = NestedSurvey::find($survey->parent_id);
$survey->appendTo($parent)->save();
});
将所有调查表导入新表后，就可以切换应用程序以使用新模型.
Once you have imported all your survey into the new table, you can switch your application over to use the new models.
现在，您将能够在线性时间内获取任意深度的层次结构.并且由于您的模型扩展了Eloquent，因此您可以像往常一样获得任何相关列.
Now, you'll be able to fetch an arbitrarily deep hierarchy in linear time. And since your model extends Eloquent, you can get any related columns just as you normally would.
这篇关于Laravel Eloquent从自引用表以及其他表的列获取N级层次结构记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
