这是一个任务管理器web应用程序
我有三张table rows , tasks 以及 inputs 这个 inputs 表有 order 选择要首先显示的列 row 有很多
tasks input 有很多 tasks 所以每 task 有一个 input 和一个 row 我想把任务按 row_id 然后 input.order 第一次尝试
App\Row::with(['user', 'tasks', 'tasks.option', 'tasks.input'])
->orderBy('rows.id', 'ASC', 'order', 'ASC')
->get();它忽略了顺序
结果
[
{
"id": 1,
"user_id": 1,
"created_at": "2018-06-07 18:40:23",
"updated_at": "2018-06-07 18:40:23",
"tasks": [
{
"id": 1,
"row_id": 1,
"input_id": 1,
"option_id": 1,
"value": null,
"input": {
"id": 1,
"name": "assigned to",
"type": 1,
"value": "mario",
"required": 1,
"order": 2,
"user_id": 1,
"created_at": "2018-06-07 18:40:16",
"updated_at": "2018-06-08 10:08:19"
}
},
{
"id": 2,
"row_id": 1,
"input_id": 2,
"option_id": null,
"value": "test new option",
"input": {
"id": 2,
"name": "test new option",
"type": 0,
"value": "test new option",
"required": 0,
"order": 3,
"user_id": 1,
"created_at": "2018-06-07 18:40:44",
"updated_at": "2018-06-08 10:08:19"
}
},
{
"id": 3,
"row_id": 1,
"input_id": 3,
"option_id": null,
"value": "2018-07-01",
"input": {
"id": 3,
"name": "deadline",
"type": 3,
"value": "2018-07-01",
"required": 0,
"order": 4,
"user_id": 1,
"created_at": "2018-06-08 10:07:37",
"updated_at": "2018-06-08 10:08:19"
}
},
{
"id": 4,
"row_id": 1,
"input_id": 4,
"option_id": null,
"value": "",
"input": {
"id": 4,
"name": "priority",
"type": 6,
"value": "",
"required": 0,
"order": 1,
"user_id": 1,
"created_at": "2018-06-08 10:08:19",
"updated_at": "2018-06-08 10:08:19"
}
}
]
}
]第二次尝试
App\Row::with(['user', 'tasks', 'tasks.option', 'tasks.input'])
->orderBy('rows.id', 'ASC')
->orderBy('order', 'ASC')
->get();但它产生了一个错误
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'order' in 'order clause' (SQL: select * from `rows` order by `rows`.`id` asc, `order` asc)第三次尝试
App\Row::with([
'user',
'tasks',
'tasks.option',
'tasks.input' =>
function($query) {
$query->orderBy('order', 'ASC');
}
])
->orderBy('rows.id', 'ASC')
->get();它忽略与第一个相同的顺序结果
第四次尝试
return App\Row::with(['user', 'tasks', 'tasks.option', 'tasks.input'])
->join('tasks', 'rows.id', '=', 'tasks.row_id')
->join('inputs', 'inputs.id', '=', 'tasks.input_id')
->orderBy('rows.id', 'ASC')
->orderBy( 'inputs.order', 'ASC')
->get();它为每一列生成行，而不是像它假设的那样生成行
结果
[
{
"id": 4,
"user_id": 1,
"created_at": "2018-06-08 10:08:19",
"updated_at": "2018-06-08 10:08:19",
"row_id": 1,
"input_id": 4,
"option_id": null,
"value": "",
"name": "priority",
"type": 6,
"required": 0,
"order": 1,
"tasks": []
},
{
"id": 1,
"user_id": 1,
"created_at": "2018-06-07 18:40:16",
"updated_at": "2018-06-08 10:08:19",
"row_id": 1,
"input_id": 1,
"option_id": 1,
"value": "mario",
"name": "assigned to",
"type": 1,
"required": 1,
"order": 2,
"tasks": [
{
"id": 1,
"row_id": 1,
"input_id": 1,
"option_id": 1,
"value": null,
"input": {
"id": 1,
"name": "assigned to",
"type": 1,
"value": "mario",
"required": 1,
"order": 2,
"user_id": 1,
"created_at": "2018-06-07 18:40:16",
"updated_at": "2018-06-08 10:08:19"
}
},
{
"id": 2,
"row_id": 1,
"input_id": 2,
"option_id": null,
"value": "test new option",
"input": {
"id": 2,
"name": "test new option",
"type": 0,
"value": "test new option",
"required": 0,
"order": 3,
"user_id": 1,
"created_at": "2018-06-07 18:40:44",
"updated_at": "2018-06-08 10:08:19"
}
},
{
"id": 3,
"row_id": 1,
"input_id": 3,
"option_id": null,
"value": "2018-07-01",
"input": {
"id": 3,
"name": "deadline",
"type": 3,
"value": "2018-07-01",
"required": 0,
"order": 4,
"user_id": 1,
"created_at": "2018-06-08 10:07:37",
"updated_at": "2018-06-08 10:08:19"
}
},
{
"id": 4,
"row_id": 1,
"input_id": 4,
"option_id": null,
"value": "",
"input": {
"id": 4,
"name": "priority",
"type": 6,
"value": "",
"required": 0,
"order": 1,
"user_id": 1,
"created_at": "2018-06-08 10:08:19",
"updated_at": "2018-06-08 10:08:19"
}
}
]
},
{
"id": 2,
"user_id": 1,
"created_at": "2018-06-07 18:40:44",
"updated_at": "2018-06-08 10:08:19",
"row_id": 1,
"input_id": 2,
"option_id": null,
"value": "test new option",
"name": "test new option",
"type": 0,
"required": 0,
"order": 3,
"tasks": []
},
{
"id": 3,
"user_id": 1,
"created_at": "2018-06-08 10:07:37",
"updated_at": "2018-06-08 10:08:19",
"row_id": 1,
"input_id": 3,
"option_id": null,
"value": "2018-07-01",
"name": "deadline",
"type": 3,
"required": 0,
"order": 4,
"tasks": []
}
]这个文件在这里
完整代码在这里
1条答案
按热度按时间owfi6suc1#
因为它搜索表中的列
rows不在input.