|
我有以下查询,将数据插入到多对多联接表中* q# w" s5 O$ `' [' {
INSERT INTO playlist_genre(playlist_id, genre_id)9 I* W5 f* _( Z5 C% r* t
VALUES (${playlistId}, ${genre1Id}),
$ E/ { z8 Z, X& D (${playlistId}, ${genre2Id}),
3 z) s6 f7 L, k (${playlistId}, ${genre3Id})
: n/ e. d' d+ S7 I J; N* y);) @0 ]3 u2 m2 k) T' L4 }
但是,我遇到的问题是用户并不需要这些值genre2Id和genre3Id,因此可以是anINTEGER或NULL。
1 I' j. z5 |0 S我正在尝试找到一种方法来写入此相同的查询,但因此仅在存在值的情况下才插入。两列都有NOT NULL约束。9 l$ n6 O+ X n1 ^" I
编辑 :8 n8 e( [" {# D3 ^4 O8 _3 L5 g
这是我的播放列表课程
7 W' k0 z0 _2 M; c# cclass Playlist { _2 `) k V, ^* m" w; ~. L0 ~
constructor(playlist) {. ?# x; W( s& S4 S* O* T) d9 g% q# X
// required fields' S, L: Z) M5 W- c0 ^
this.title = playlist.title;4 [8 Z7 i' i& X" q1 I2 V# d. p/ Z
this.playlistType = playlist.playlistType;
5 [( s# f7 k" N0 ?" | this.userId = playlist.userId;, U& r; N1 X& f* f0 C
this.numberOfTracks = playlist.numberOfTracks;+ K: P, _: R4 [- D' t8 y# J
this.lengthInSeconds = playlist.lengthInSeconds;6 N Y) a& t! K# z& F: c
this.genre1Id = playlist.genre1Id;/ u! U1 Q, f; i; O5 a( E B
// not required fields
7 r0 s% z, g" I1 G this.genre2Id = playlist.genre2Id || null;7 H3 v) G) ?2 ^; H+ n
this.genre3Id = playlist.genre3Id || null;, ?! }! t( [* d: H$ `1 e
this.description = playlist.description || null;
: R1 ^) F- e$ [7 H! c* h" @ this.releaseDate = playlist.releaseDate || null;" Y0 V6 T9 y" `7 }0 J! c8 T5 L' m
}
) i$ |0 m2 _/ ~ save() {0 G5 F8 \ i [* R; Z" E
return new Promise((resolve, reject) => {8 k' t. ?: R# N" e- e
db.one(sqlCreatePlaylist, this)
7 d1 i3 q, H9 e3 Y, `$ X .then((insertedPlaylist) => {5 J! Y0 A: ^: N# z# \/ y
// Here is where i want to use insertedPlaylist's id / ?" M/ Q, b4 ?5 e! Y- ?2 z
// to insert data into 'playlist_genre' table
/ f) o4 M& @5 j1 ]2 x, i. p resolve(insertedPlaylist);
' B- s9 J1 l, ?: x" u })
! W2 p* ~! a- D7 L! |4 Q" E1 } .catch(error => reject(error));: C# G9 M5 N0 Z7 E
});& }) u+ N9 u7 `# c
}3 W( j2 H6 z" U1 A
这是sqlCreatePlaylist的样子
- X: d+ N) ?6 Z) F8 |7 _7 q2 w/ [INSERT INTO playlists(
6 y; S4 N0 Y% W6 p( ]: H user_id,2 d M6 j1 p6 t; q6 ^( N" y
title,* Z' @! z. h6 y3 |* d7 r
playlist_type,3 N2 E j& K' n, T
number_of_tracks,0 Y3 _% @1 a5 t* o" b: U
duration,
8 L% Z" J( P' L( A- }7 S5 D description,
: R' K) @1 s" A) a" [ release_date
+ U% G1 s2 K' D' g( O; Q6 K)% T& }5 J H: u' a* v
VALUES(
' [; A- A3 U4 [- T0 j7 n. q% a ${userId},, i2 X6 v3 D3 O5 f! n9 [/ M1 ]# S* Z
${title},
' i- r2 N* T/ x) I+ _ ${playlistType},
, M1 x4 }8 d; N, y. e ${numberOfTracks},0 h _2 _- {' [
${lengthInSeconds},
$ J f4 H* y! K$ X; M( z ${description}, ~- ]+ c2 l6 C- ^' o9 S$ {$ Z8 M7 n, Q
${releaseDate}
0 G; |; m' Z6 `0 \, Y8 t)
/ ]) Z, z3 U; mRETURNING *;: G" J9 ?# s+ t4 g% \
7 l) W8 @4 u( |解决方案: |
|