|
我有以下查询,将数据插入到多对多联接表中- R h* U! i$ {* S1 M; l/ o3 N. B% \
INSERT INTO playlist_genre(playlist_id, genre_id)' m1 l, Z7 O+ \% a
VALUES (${playlistId}, ${genre1Id}),! I% \) |" |3 \* @! L8 @0 g0 L
(${playlistId}, ${genre2Id}),
2 ^$ L6 h& `- r' v( P (${playlistId}, ${genre3Id})5 M0 t z5 p Z0 o p$ U
);
9 n; S& u# W2 [" Q L但是,我遇到的问题是用户并不需要这些值genre2Id和genre3Id,因此可以是anINTEGER或NULL。7 D1 s9 G1 g: b3 a
我正在尝试找到一种方法来写入此相同的查询,但因此仅在存在值的情况下才插入。两列都有NOT NULL约束。: U. B4 T; N6 b
编辑 :
+ F, q5 V+ |2 G7 p+ t7 O3 j* A这是我的播放列表课程
* q$ v$ s2 R- P. [. ]+ D: y7 ~% h$ yclass Playlist {
% G$ p: c, n) {5 S- s constructor(playlist) {0 D% J, Q. \/ {7 i B
// required fields
# K- J9 w$ D2 g* u% o this.title = playlist.title;( i7 u* F" b9 ]+ t+ _$ ]! R2 s# t
this.playlistType = playlist.playlistType;
5 C* ?( {: B( J* k9 x this.userId = playlist.userId;
4 H: l" c: S3 C: ] this.numberOfTracks = playlist.numberOfTracks;
; z E" X3 {9 o4 X1 O$ m# W this.lengthInSeconds = playlist.lengthInSeconds;( Y6 T, P& `. Z2 q
this.genre1Id = playlist.genre1Id;
+ _! W9 l0 Y" j& @- E // not required fields
0 V$ r* E. J$ T; O this.genre2Id = playlist.genre2Id || null;/ Y/ W+ r, B; F/ ~
this.genre3Id = playlist.genre3Id || null;4 H( C# C) y4 E' R0 G+ Z' I4 l
this.description = playlist.description || null;4 ~" }, t V" [1 S! }% h
this.releaseDate = playlist.releaseDate || null;
4 t8 F4 D& V' D0 m }
' u3 f5 ?; d* P save() {
' J t1 w- n( r7 z' t3 L return new Promise((resolve, reject) => {
2 Y1 L6 [# g3 | db.one(sqlCreatePlaylist, this)) @4 S- e/ [ n9 F, ~9 S
.then((insertedPlaylist) => {5 P5 x" D2 u8 ^5 V1 T( e4 m% r
// Here is where i want to use insertedPlaylist's id
6 Q! t6 [1 _1 q: W8 `6 N // to insert data into 'playlist_genre' table6 O1 Y+ K1 V, X8 W# E; T) Q
resolve(insertedPlaylist);
x7 a$ o$ i. b) K3 J3 X% A })
7 r9 I: T; i, k$ B .catch(error => reject(error));& m) j+ @7 p& ?( B. G& p
});
" S0 h" W6 T) W* [9 K4 m, L; {& o! F }# z! ^% p4 [, r/ v9 D4 q$ G/ v
这是sqlCreatePlaylist的样子$ A2 R. G# r! u A N* K3 Y
INSERT INTO playlists(2 `* {' y" P, p7 \. I
user_id,- t* p- L7 U' ^8 _. a+ v
title,
/ r' T6 }8 r; w; p9 ]2 [: e9 r playlist_type,+ ?0 M7 o: X7 i. A/ w
number_of_tracks,0 H0 M* {" D, j4 k2 I1 p
duration,- s4 V0 R. u- K/ b+ I8 p# C' k
description,
+ p0 W" @, I. q release_date
( i" S& s( r6 x6 N)0 ]4 A5 n5 f$ A( P
VALUES(
]* i1 Q" Y5 d* r ${userId},0 k" e8 s T8 W/ B$ R6 T! R! r
${title},% T1 U7 j6 k" V
${playlistType},
( H) E, U* E: S ${numberOfTracks},, e$ m1 J7 ]* o! @4 Y6 Q0 z+ F
${lengthInSeconds},5 R* {) D9 `6 v0 e
${description},% s" ]% z1 }8 m. X* q, j
${releaseDate}
6 B& Q: v: M, e3 F5 z) u)
# T N4 |4 M3 hRETURNING *;! p4 `5 {$ p$ E( a% l
5 w& X% G4 f$ ^4 I解决方案: |
|