First of all, the data and table are as follows.
CREATE TABLE products
(
p_id INT,
p_name VARCHAR(32),
PRIMARY KEY (p_id)
);
CREATE TABLE color
(
p_id INT,
p_color VARCHAR(24)
);
CREATE TABLE size
(
p_id INT,
p_size VARCHAR(12)
);
INSERT INTO products
VALUES
(1, 'test1'),
(2, 'test2'),
(3, 'test3'),
(4, 'test4'),
(5, 'test5');
INSERT INTO color
VALUES
(1, 'black'),
(1, 'white'),
(1, 'gray'),
(2, 'black'),
(2, 'beige'),
(3, 'blue'),
(3, 'red'),
(3, 'green'),
(4, 'black'),
(4, 'white'),
(4, 'beige'),
(4, 'gray'),
(4, 'mustard'),
(5, 'black'),
(5, 'white');
INSERT INTO size
VALUES
(1, 'S'),
(1, 'M'),
(1, 'L'),
(1, 'XL'),
(2, 'Free'),
(3, 'XS'),
(3, 'S'),
(3, 'M'),
(3, 'L'),
(3, 'XL'),
(4, 'M'),
(4, 'L'),
(4, 'XL'),
(5, 'Free');
I'd like to change the query that I put in the console to the query that I put in the sql statement in the query that I put in the sequence.
SELECT p.p_Id, p.p_name, s.p_size, c.p_color
FROM products AS p
LEFT OUTER JOIN size AS s
ON p.p_id = s.p_id
LEFT OUTER JOIN color AS c
ON p.p_id = c.p_id;
+-----------+-------------+-------------+--------------+
| | productId | productName | productSize | productColor |
+-----------+-------------+-------------+--------------+
| | 1 | test_1 | S | black |
| | 1 | test_1 | M | black |
| | 1 | test_1 | L | black |
| | 1 | test_1 | XL | black |
| | 1 | test_1 | S | white |
| | 1 | test_1 | M | white |
| | 1 | test_1 | L | white |
| | 1 | test_1 | XL | white |
| | 1 | test_1 | S | gray |
| | 1 | test_1 | M | gray |
| | 1 | test_1 | L | gray |
| | 1 | test_1 | XL | gray |
| | 2 | test_2 | Free | black |
| | 2 | test_2 | Free | beige |
| | 3 | test_3 | XS | blue |
| | 3 | test_3 | S | blue |
| | 3 | test_3 | M | blue |
| | 3 | test_3 | L | blue |
| | 3 | test_3 | XL | blue |
| | 3 | test_3 | XS | red |
| | 3 | test_3 | S | red |
| | 3 | test_3 | M | red |
| | 3 | test_3 | L | red |
| | 3 | test_3 | XL | red |
| | 3 | test_3 | XS | green |
| | 3 | test_3 | S | green |
| | 3 | test_3 | M | green |
| | 3 | test_3 | L | green |
| | 3 | test_3 | XL | green |
| | 4 | test_4 | M | black |
| | 4 | test_4 | L | black |
| | 4 | test_4 | XL | black |
| | 4 | test_4 | M | white |
| | 4 | test_4 | L | white |
| | 4 | test_4 | XL | white |
| | 4 | test_4 | M | beige |
| | 4 | test_4 | L | beige |
| | 4 | test_4 | XL | beige |
| | 4 | test_4 | M | gray |
| | 4 | test_4 | L | gray |
| | 4 | test_4 | XL | gray |
| | 4 | test_4 | M | mustard |
| | 4 | test_4 | L | mustard |
| | 4 | test_4 | XL | mustard |
| | 5 | test_5 | Free | black |
| | 5 | test_5 | Free | white |
+-----------+-------------+-------------+--------------+
46 rows in set (0.00 sec)
models.products.findAll({
include: [{
model: models.size,
required: false,
}, {
model: models.color,
required: false
}]
});
Of course, there are several p_id in the color and size table, so PK cannot be set
As I run the query as above, I set the constraint only in the products table. And since p_id is a common column for each table, FK is set only to p_id.
SELECT
`p`.`p_id`,
`p`.`p_name`,
`s`.`p_id` AS `s.p_id`,
`s`.`p_id` AS `s.p_id`,
`s`.`p_size` AS `s.p_size`,
`c`.`p_id` AS `c.p_id`,
`c`.`p_id` AS `c.p_id`,
`c`.`p_color` AS `c.p_color`,
FROM `products` AS `p`
LEFT OUTER JOIN `size` AS `s`
ON `p`.`p_id` = `s`.`id`
LEFT OUTER JOIN `color` AS `c`
ON `p`.`p_id` = `c`.`id`
It's coming out together. In the ON section, p.p_id = c.It should be p_id, but a column that is not even in the color table comes up and floats to p.p_id = c.id. I would like to ask for your help if you know how to solve the ON clause without using raw query at the level of quality.
After asking you a question, I also set up association. In addition, we solved the problem caused by sequelize-cli chronic problems caused by id, updatedAt, and createdAt automatically generated in migrations. The code is as follows.
products.hasMany(db.size, {
foreignKey: 'p_id'
});
products.hasMany(db.color, {
foreignKey: 'p_id'
});
color.belongsTo(db.products, {
foreignKey: 'p_id'
});
size.belongsTo(db.products, {
foreignKey: 'p_id'
});
db.ProductColor.removeAttribute('id');
db.ProductSize.removeAttribute('id');
The funny thing is that even if you remove belongsTo() and set up the relationship only with hasMany(), JSON data will be returned.
let productId = req.params.id;
const product = products.findAll({
where: {
p_id: productId
},
include: [{
model: size,
attributes: {
exclude: ['p_id', 'id', 'createdAt', 'updatedAt']
}
}, {
model: color,
attributes: {
exclude: ['p_id', 'id', 'createdAt', 'updatedAt']
}
}]
});
This is how the query was created. p_id was removed because it overlaps with the products table, and createdAt and updatedAt are data that does not exist in the table. Lastly, id doesn't appear in particular even if you don't exclude it using removeAttribute() The returned JSON data is as follows:
{
"product": [
{
"p_id": 1,
"p_ame": "test_1",
"createdAt": "2018-10-13T00:00:00.000Z",
"updatedAt": "2018-10-14T00:00:00.000Z",
"size": [
{
"size": "S"
}
],
"color": [
{
"color": "black"
}
]
}
]
}
It comes out like this. There are 4 records with p_id in the size table and 3 records in the color table, but it's so frustrating to see only 1 record from the top.
"size": [
{
"productSize": "S"
},
{
"productSize": "M"
},
{
"productSize": "L"
},
{
"productSize": "XL"
}
],
"color": [
{
"productColor": "black"
},
{
"productColor": "white"
},
{
"productColor": "gray"
}
]
I didn't make a connection table because I don't think this is the case. I'm in desperate need of help. I don't know what the problem is ㅠ<
© 2024 OneMinuteCode. All rights reserved.