如何在祖先属性集中包含来自较低级别包含的属性？

huangapple
go评论73阅读模式
英文:

How do you include an attribute from a lower level include in an ancestor attribute set?

问题

I have translated the relevant portions of your text as requested. Here is the translated text:

Sequalize（MySQL）熟悉的人有一个问题。 有人知道将嵌套包含中的属性放在上面的属性集中的正确方法吗？

示例Sequelize请求...

```js
const pupils = await sequelize.Pupil.findByPk(1, {
	attributes: ['id','name'],
	include: [
		{
			model: sequelize.PupilGrade,
			as: 'pupilGrades',
			attributes: ['id','pupilId','subjectId','grade'],
			include: [
				{
					model: sequelize.Subject,
					as: 'subject',
					attributes: ['id','name'],
				},
			],
		},
	],
});

结果数组...

[
	{
		"id": 1,
		"name": '学生A',
		"pupilGrades": [
			{
				"id": 1,
				"pupilId": 1,
				"subjectId": 1,
				"grade": 'B+',
				"subject": {
					"id": 1,
					"name": "数学"
				}
			},				
		],
	},
]

期望的数组...

[
	{
		"id": 1,
		"name": '学生A',
		"pupilGrades": [
			{
				"id": 1,
				"pupilId": 1,
				"subject": "数学",
				"grade": 'B+',
			},				
		],
	},
]

我想知道如何在“PupilGrades”的属性集中包含“Subject”的“name”属性。

我尝试过使用Sequelize.col()...

[Sequelize.col('subject.name'), 'subject'],

然而，它认为subject是一个属性而不是一个包含表，并返回：

错误：未知列 'pupil->pupilGrades.subject.name'

在包含级别，我试过以下方法但无法解决：


{
	model: sequelize.PupilGrade,
	as: 'pupilGrades',
	attributes: [
		'id',
		'pupilId',
		'subjectId',
		'grade',
		[Sequelize.col('subject.name'), 'name'],
	],
	include: [
		{
			model: sequelize.Subject,
			as: 'subject',
			attributes: ['id','name'],
		},
	],
},

任何帮助将不胜感激。

根据Emma的建议，我尝试了以下方法。

[Sequelize.literal('`pupilGrades->subject`.name'), 'subject'],

经过进一步测试，似乎Sequelize存在问题。

const pupil = await req.db.Pupil.findByPk(1, {
	logging: console.log,
	attributes: [
		'id',
		'name',
	],
	include: [
		{ 
			model: req.db.PupilGrade,
			as: 'pupilGrades',
			attributes: ['id','pupilId','subjectId','grade',
				[Sequelize.literal('`pupilGrades->subject`.name'), 'subject'],
			],
			include: [
				{
					model: req.db.Subject,
					as: 'subject',
					attributes: [],
				},
			],
		},
	],
});

以上代码生成以下SQL语句：

SELECT 
`Pupil`.`id`,
`Pupil`.`name`, 
`pupilGrades`.`id` AS `pupilGrades.id`,
`pupilGrades`.`pupil_id` AS `pupilGrades.pupilId`,
`pupilGrades`.`subject_id` AS `pupilGrades.subjectId`, 
`pupilGrades`.`grade` AS `pupilGrades.grade`, 
`pupilGrades->subject`.name AS `pupilGrades.subject`
FROM `pupil` AS `Pupil`
LEFT OUTER JOIN `pupil_grade` AS `pupilGrades` ON `Pupil`.`id` = `pupilGrades`.`pupil_id` 
LEFT OUTER JOIN `subject` AS `pupilGrades->subject` ON `pupilGrades`.`subject_id` = `pupilGrades->subject`.`id` 
WHERE `Pupil`.`id` = 1;

返回正确的数据集：


+----+---------+----------------+---------------------+-----------------------+-------------------+---------------------+
| id | name    | pupilGrades.id | pupilGrades.pupilId | pupilGrades.subjectId | pupilGrades.grade | pupilGrades.subject |
+----+---------+----------------+---------------------+-----------------------+-------------------+---------------------+
|  1 | 学生 A  |              1 |                   1 |                     1 | B+                | 数学                |
+----+---------+----------------+---------------------+-----------------------+-------------------+---------------------+

然而，Sequelize返回以下对象，其中pupilGrades.subject列丢失。


{
	"pupil":{
		"id":1,
		"name":"学生 A",
		"pupilGrades":[
			{
				"id":1,
				"pupilId":1,
				"subjectId":1,
				"grade":"B+"
			}
		]
	}
}


<details>
<summary>英文:</summary>

A question for those familiar with Sequalize (MySQL). Does anyone know the correct method to place an attribute from a nested include within the attribute set above?

Example Sequelize request...

const pupils = await sequelize.Pupil.findByPk(1, {
attributes: ['id','name'],
include: [
{
model: sequelize.PupilGrade,
as: 'pupilGrades',
attributes: ['id','pupilId','subjectId','grade'],
include: [
{
model: sequelize.Subject,
as: 'subject',
attributes: ['id','name'],
},
],
},
],
});


Resulting array...

[
{
"id": 1,
"name": 'Pupil A',
"pupilGrades": [
{
"id": 1,
"pupilId": 1,
"subjectId": 1,
"grade": 'B+',
"subject": {
"id": 1,
"name": "Math"
}
},
],
},
]


Desired array...

[
{
"id": 1,
"name": 'Pupil A',
"pupilGrades": [
{
"id": 1,
"pupilId": 1,
"subject": "Math",
"grade": 'B+',
},
],
},
]


I would like to know how to include the &#39;name&#39; attribute from &#39;Subject&#39; within the attribute set of &#39;PupilGrades&#39;. 

I have tried using Sequelize.col()...

[Sequelize.col('subject.name'), 'subject'],

However, it thinks subject is a attribute not an include table and returns:

Error: Unknown column 'pupil->pupilGrades.subject.name'


I am struggling to figure out how to achieve this at the include level having tried the following:


    {
        model: sequelize.PupilGrade,
        as: &#39;pupilGrades&#39;,
        attributes: [
			&#39;id&#39;,
			&#39;pupilId&#39;,
			&#39;subjectId&#39;,
			&#39;grade&#39;,
			[Sequelize.col(&#39;subject.name&#39;), &#39;name&#39;],
		],
        include: [
            {
                model: sequelize.Subject,
                as: &#39;subject&#39;,
                attributes: [&#39;id&#39;,&#39;name&#39;],
            },
        ],
    },


Any help much appreciated.

I have tried the following as per Emma&#39;s suggestion.

[Sequelize.literal('pupilGrades-&gt;subject.name'), 'subject'],


After further tests it looks like there is an issue with Sequelize.

const pupil = await req.db.Pupil.findByPk(1, {
logging: console.log,
attributes: [
'id',
'name',
],
include: [
{
model: req.db.PupilGrade,
as: 'pupilGrades',
attributes: ['id','pupilId','subjectId','grade',
[Sequelize.literal('pupilGrades-&gt;subject.name'), 'subject'],
],
include: [
{
model: req.db.Subject,
as: 'subject',
attributes: [],
},
],
},
],
});


The code above generates the following SQL statement:

SELECT
Pupil.id,
Pupil.name,
pupilGrades.id AS pupilGrades.id,
pupilGrades.pupil_id AS pupilGrades.pupilId,
pupilGrades.subject_id AS pupilGrades.subjectId,
pupilGrades.grade AS pupilGrades.grade,
pupilGrades-&gt;subject.name AS pupilGrades.subject
FROM pupil AS Pupil
LEFT OUTER JOIN pupil_grade AS pupilGrades ON Pupil.id = pupilGrades.pupil_id
LEFT OUTER JOIN subject AS pupilGrades-&gt;subject ON pupilGrades.subject_id = pupilGrades-&gt;subject.id
WHERE Pupil.id = 1;


Returning the correct dataset:

+----+---------+----------------+---------------------+-----------------------+-------------------+---------------------+
| id | name | pupilGrades.id | pupilGrades.pupilId | pupilGrades.subjectId | pupilGrades.grade | pupilGrades.subject |
+----+---------+----------------+---------------------+-----------------------+-------------------+---------------------+
| 1 | Pupil A | 1 | 1 | 1 | B+ | Math |
+----+---------+----------------+---------------------+-----------------------+-------------------+---------------------+


However, Sequelize returns the following object with the pupilGrades.subject column missing.

{
"pupil":{
"id":1,
"name":"Pupil A",
"pupilGrades":[
{
"id":1,
"pupilId":1,
"subjectId":1,
"grade":"B+"
}
]
}
}



</details>


# 答案1
**得分**: 0

我不知道为什么你的`Sequelize.col`语法不起作用，但你可以尝试这样做。`Sequelize.col`应该正确地转义别名，但在你的情况下似乎没有。如果我找到了解决方法，我会更新这个答案。

据我所知，在将`Sequelize.col`添加到模型别名（`pupilGrades`）的情况下它不起作用。如果我将`Sequelize.col`添加到顶级，我就看不到任何问题。

```javascript
{
    model: sequelize.PupilGrade,
    as: 'pupilGrades',
    attributes: [
        'id',
        'pupilId',
        'grade',
        [Sequelize.literal('`pupilGrades->subject`.name'), 'subject_name'],
    ],
    include: [
        {
            model: sequelize.Subject,
            as: 'subject',
            attributes: [],  // 不需要嵌套子对象
        },
    ],
}

请注意，我不能在关联模型和同一查询中的属性中使用相同的别名。

英文:

I don't know why your syntax with Sequelize.col doesn't work but you can try this. Sequelize.col supposed to escape the aliases correctly, however, it didn't in your case. If I find something, I will update this answer.

AFAIK, it doesn't work when I add Sequelize.col to where a model is aliased (pupilGrades). If I add Sequelize.col to top level, I don't see any problems.

{
    model: sequelize.PupilGrade,
    as: &#39;pupilGrades&#39;,
    attributes: [
        &#39;id&#39;,
        &#39;pupilId&#39;,
        &#39;grade&#39;,
        [Sequelize.literal(&#39;`pupilGrades-&gt;subject`.name&#39;), &#39;subject_name&#39;],
    ],
    include: [
        {
            model: sequelize.Subject,
            as: &#39;subject&#39;,
            attributes: [],  // Do not need sub nested object
        },
    ],
},

** Note that I cannot use the same alias name in associated model and attributes in same query.

huangapple
  • 本文由 发表于 2023年1月9日 22:23:14
  • 转载请务必保留本文链接：https://go.coder-hub.com/75058645.html
  • node.js
  • sequelize.js
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定