如何将mysql查询翻译为sormize orm [英] How to translate mysql query to sequelize orm

问题描述

我需要使用续集执行此多个LEFT JOIN查询:

  SELECT电影，类型来自`yt_movies` M左联接`genres_link` GL ON M.id = GL.movi​​e_id左联接'genres` G GL.genre_id = G.idM.id = 1098的位置 

我试过了

const YtMovies = db.yt_movies;const 流派 = db.genres;const GenresLink = db.genres_link;YtMovies.hasMany(GenresLink，{as:'GL'，foreignKey:'movie_id'});YtMovies.hasMany(Genres，{as:'G'，foreignKey:'genre'});const res =等待db.yt_movies.findAll({属性:['电影']，其中:{id:movie_id}，包括: [{型号:db.genres_link，如:"GL"，必填:假，属性:['genre_id']，}，{型号:db.genres，必填:假，如:"G"，属性:['体裁']，}，]，}); 

返回的查询看起来像

  SELECT`yt_movies`.id`，电影yt_movies.GL.id作为AS GL.id，GL.genre_id AS GL.genre_id`G`.id`作为`G.id`，「G.genre」作为「G.genre」从`yt_movies`作为`yt_movies`左侧外部加入`genres_link`作为``yt_movies..id` =`GL`.`movie_id`中的`GL`.LEFT OUTER JOIN `genres` AS `G` ON `yt_movies`.`id` = `G`.`genre`哪里`yt_movies`.`id` = 1098; 

在最后两个字符串中，我们可以看到它与 yt_movies 一起使用了ON但我希望它与 genres_link

一起使用

 按照GL.genre_id = G.id<-的预期加入流派 

我想我不太了解表关联，并且弄乱了hasMany，或者我想念另一条语句

表看起来像

yt_movies

  |id |电影|pj |pq || ---- | --------- || ---- | --- ||1 |头像||||2 |捕食者||||3 | ... ||| 

流派链接

  |id |genre_id |movie_id || ---- | --------- || ---------- ||1 |12 |1 |//头像|2 |13 |2 |//捕食者|3 |14 |2 |//捕食者 

类型

  |id |体裁|| ---- | ---------- ||12 |动作||13 |惊悚片||14 |恐怖| 

我所做的所有工作都设法执行了LEFT JOIN ...添加第二个include并没有帮助，而且即使阅读了文档后，恐怕我也不真正理解表关联:

我想我需要使用belongsToMany，但此刻我不明白如何:))

感谢所有帮助！谢谢，谢谢！

解决方案

要加入A-> B-> C，您应将C的包含内容嵌套在B的包含内容中，例如

  A.findAll({包括: [{型号:B，包括: [{型号:C}]}]}) 

但是，如果表genres_link除了电影和流派的PK之外没有其他属性，则使用至.

  YtMovies.belongsToMany(Genres，{through:GenresLink，foreignKey:'movie_id'});Genres.belongsToMany(YtMovies，{through:GenresLink，foreignKey:'genre_id'});YtMovies.findAll({包括: [{模特:流派，必填:正确，通过:GenresLink}]}); 

手册包含有关此主题的一些有用信息...

I need to perform this multiple LEFT JOIN query with sequelize:

SELECT movie, genre FROM `yt_movies` M 
LEFT JOIN `genres_link` GL ON M.id = GL.movie_id
LEFT JOIN `genres` G ON GL.genre_id = G.id
WHERE M.id = 1098

I tried

const YtMovies = db.yt_movies;
const Genres = db.genres;
const GenresLink = db.genres_link;

YtMovies.hasMany(GenresLink, { as: 'GL', foreignKey: 'movie_id' });
YtMovies.hasMany(Genres, { as: 'G', foreignKey: 'genre' });

const res = await db.yt_movies.findAll({
                    attributes: ['movie'],
                    where: { id: movie_id },
                    include: [
                        {
                            model: db.genres_link,
                            as: 'GL',
                            required: false,
                            attributes: ['genre_id'],
                        },
                        {
                            model: db.genres,
                            required: false,
                            as: 'G',
                            attributes: ['genre'],
                        },
                    ],
                });

the returned query looks like

SELECT
`yt_movies`.`id`,
`yt_movies`.`movie`,
`GL`.`id` AS `GL.id`,
`GL`.`genre_id` AS `GL.genre_id`,
`G`.`id` AS `G.id`,
`G`.`genre` AS `G.genre`
FROM `yt_movies` AS `yt_movies`
LEFT OUTER JOIN `genres_link` AS `GL` ON `yt_movies`.`id` = `GL`.`movie_id`
LEFT OUTER JOIN `genres` AS `G` ON `yt_movies`.`id` = `G`.`genre`
WHERE `yt_movies`.`id` = 1098;

in the last two string we can see that it uses ON with yt_movies but i expect it to use ON with genres_link

LEFT JOIN `genres` AS G ON GL.genre_id = G.id <-- expected

I guess i don't really understand table associations and messed up with hasMany or I miss another statement

tables look like

yt_movies

| id | movie   | pj | pq|
|----|---------|----|---|
|  1 |Avatar   |    |   |
|  2 |Predator |    |   |
|  3 |...      |    |   | 

genres_link

| id | genre_id| movie_id |
|----|---------|----------|
|  1 |  12     |    1     | // avatar
|  2 |  13     |    2     | // predator
|  3 |  14     |    2     | // predator

genres

| id | genre    |
|----|----------|
| 12 | action   | 
| 13 | thriller | 
| 14 | horror   | 

all I did is managed to perform first LEFT JOIN... adding second include didn't help, and Im afraid I don't truely undestand table associations even after reading the docs :|

I guess i need to use belongsToMany, but at the moment i don't understand how :))

I appreciate all the help! Thanks and HALP!

解决方案

To join A->B->C, you should nest the include for C inside the include for B, e.g.

A.findAll({    
    include: [
       {
         model: B,
         include: [
             {model: C}
             ]
         }
     ]
     })

But, if table genres_link has no attributes other than PKs of movie and genre, then use through.

   YtMovies.belongsToMany(Genres, {through: GenresLink, foreignKey: 'movie_id' });
   Genres.belongsToMany (YtMovies,{through: GenresLink, foreignKey: 'genre_id '});

    YtMovies.findAll({    
       include: [
          {
           model: Genres, 
           required : true,
           through: GenresLink 
          }
          ]
       });

The manual has some helpful info on this topic...

这篇关于如何将mysql查询翻译为sormize orm的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！