根据默认列和传递的语言ID,如何仅获取多对多表关系中的1或2条记录。

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

How to get only 1 or 2 records on many to many table relation based on default column and passed language id

问题

请考虑以下插入的表格和数据:

CREATE TABLE [dbo].[Language]
(
	[Id] UNIQUEIDENTIFIER NOT NULL,
	[Name] NVARCHAR(256) NOT NULL,
	[Culture] NVARCHAR(10) UNIQUE NOT NULL,
	[DateCreated] DATETIME NOT NULL DEFAULT GETUTCDATE(),
	CONSTRAINT [PK_Language] PRIMARY KEY CLUSTERED ([Id] ASC)
)

CREATE TABLE Book (
	[Id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
	[PublicDate] DATETIME DEFAULT GETUTCDATE()
)

CREATE TABLE [BookLanguage] (
	[BookId] UNIQUEIDENTIFIER NOT NULL,
	[LanguageId] UNIQUEIDENTIFIER NOT NULL,
	[Default] BIT NOT NULL,
	CONSTRAINT [PK_BookLanguage] PRIMARY KEY CLUSTERED ([BookId] ASC, [LanguageId] ASC),
	CONSTRAINT [FK_BookLanguage_Book] FOREIGN KEY ([BookId]) REFERENCES [Book]([Id]),
	CONSTRAINT [FK_BookLanguage_Language] FOREIGN KEY ([LanguageId]) REFERENCES [Language]([Id])
)

CREATE TABLE BookLocalization(
	[Id] INT IDENTITY(1,1) PRIMARY KEY,
	[BookId] UNIQUEIDENTIFIER NOT NULL,
	[Name] NVARCHAR(256),
	[LanguageId] UNIQUEIDENTIFIER,
	CONSTRAINT [FK_BookLocalization_Book] FOREIGN KEY ([BookId]) REFERENCES [Book]([Id])
)

INSERT INTO [Language]([Id], [Name], [Culture], [DateCreated])
VALUES ('2F12CAA6-16D7-4D83-B17E-560241DAE1D2', 'English', 'en', GETUTCDATE())

INSERT INTO [Language]([Id], [Name], [Culture], [DateCreated])
VALUES ('895E0F72-413C-48CD-A1A1-6302AC8A4CB4', 'Spanish', 'es', GETUTCDATE())

INSERT INTO [Book] ([Id], [PublicDate])
VALUES('D31A6823-5415-407F-9B49-49136242F03F', GETUTCDATE())

INSERT INTO [BookLanguage] ([BookId], [LanguageId], [Default])
VALUES ('D31A6823-5415-407F-9B49-49136242F03F', '2F12CAA6-16D7-4D83-B17E-560241DAE1D2', 1)

INSERT INTO [BookLanguage] ([BookId], [LanguageId], [Default])
VALUES ('D31A6823-5415-407F-9B49-49136242F03F', '895E0F72-413C-48CD-A1A1-6302AC8A4CB4', 0)

INSERT INTO [BookLocalization] ([BookId], [Name], [LanguageId])
VALUES ('D31A6823-5415-407F-9B49-49136242F03F', 'My First Book', '2F12CAA6-16D7-4D83-B17E-560241DAE1D2')

INSERT INTO [BookLocalization] ([BookId], [Name], [LanguageId])
VALUES ('D31A6823-5415-407F-9B49-49136242F03F', 'Mi Primer Libro', '895E0F72-413C-48CD-A1A1-6302AC8A4CB4')

我正在尝试找出如何选择用户所需的书籍及其本地化记录,如果所选书籍不存在该语言,则返回该书籍指定的默认语言。

例如,该书籍有英语和西班牙语记录。因此,如果我传递英语语言ID,那么我会得到英语。如果我传递西班牙语语言ID,那么我会得到西班牙语。如果我传递法语语言ID,那么我会得到英语,因为该书籍没有法语语言。

这是我尝试过的:

SELECT B.[Id], B.[PublicDate], 
	BL.[Id] AS BookLocalizationId, BL.[Name], BL.[LanguageId],
	BL2.[LanguageId], BL2.[Default]
FROM [Book] B
	INNER JOIN [BookLocalization] BL ON BL.[BookId] = B.[Id]
	INNER JOIN [BookLanguage] BL2 ON BL2.[BookId] = B.[Id]

以上的问题是它返回了4条记录。我不完全理解为什么最终会有4条记录,我希望有人能解释原因,并告诉我如何只返回1条记录。或者如果不可能只返回1条记录,那么返回两条记录。第一条记录是我选择的语言,第二条记录是该书籍的默认语言,然后我可以在代码中选择要返回给用户的记录。

编辑:BookLanguage 表表示该书籍可用/允许的所有语言。有些书籍翻译成2种语言,有些翻译成5种语言。该表控制了书籍可以翻译成哪些语言。BookLocalization 表是实际的翻译记录。

英文:

Please consider the following tables and data that is inserted:

CREATE TABLE [dbo].[Language]
(
[Id] UNIQUEIDENTIFIER NOT NULL,
[Name] NVARCHAR(256) NOT NULL,
[Culture] NVARCHAR(10) UNIQUE NOT NULL,
[DateCreated] DATETIME NOT NULL DEFAULT GETUTCDATE(),
CONSTRAINT [PK_Language] PRIMARY KEY CLUSTERED ([Id] ASC)
)
CREATE TABLE Book (
[Id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
[PublicDate] DATETIME DEFAULT GETUTCDATE()
)
CREATE TABLE [BookLanguage] (
[BookId] UNIQUEIDENTIFIER NOT NULL,
[LanguageId] UNIQUEIDENTIFIER NOT NULL,
[Default] BIT NOT NULL,
CONSTRAINT [PK_BookLanguage] PRIMARY KEY CLUSTERED ([BookId] ASC, [LanguageId] ASC),
CONSTRAINT [FK_BookLanguage_Book] FOREIGN KEY ([BookId]) REFERENCES [Book]([Id]),
CONSTRAINT [FK_BookLanguage_Language] FOREIGN KEY ([LanguageId]) REFERENCES [Language]([Id])
)
CREATE TABLE BookLocalization(
[Id] INT IDENTITY(1,1) PRIMARY KEY,
[BookId] UNIQUEIDENTIFIER NOT NULL,
[Name] NVARCHAR(256),
[LanguageId] UNIQUEIDENTIFIER,
CONSTRAINT [FK_BookLocalization_Book] FOREIGN KEY ([BookId]) REFERENCES [Book]([Id])
)
INSERT INTO [Language]([Id], [Name], [Culture], [DateCreated])
VALUES ('2F12CAA6-16D7-4D83-B17E-560241DAE1D2', 'English', 'en', GETUTCDATE())
INSERT INTO [Language]([Id], [Name], [Culture], [DateCreated])
VALUES ('895E0F72-413C-48CD-A1A1-6302AC8A4CB4', 'Spanish', 'es', GETUTCDATE())
INSERT INTO [Book] ([Id], [PublicDate])
VALUES('D31A6823-5415-407F-9B49-49136242F03F', GETUTCDATE())
INSERT INTO [BookLanguage] ([BookId], [LanguageId], [Default])
VALUES ('D31A6823-5415-407F-9B49-49136242F03F', '2F12CAA6-16D7-4D83-B17E-560241DAE1D2', 1)
INSERT INTO [BookLanguage] ([BookId], [LanguageId], [Default])
VALUES ('D31A6823-5415-407F-9B49-49136242F03F', '895E0F72-413C-48CD-A1A1-6302AC8A4CB4', 0)
INSERT INTO [BookLocalization] ([BookId], [Name], [LanguageId])
VALUES ('D31A6823-5415-407F-9B49-49136242F03F', 'My First Book', '2F12CAA6-16D7-4D83-B17E-560241DAE1D2')
INSERT INTO [BookLocalization] ([BookId], [Name], [LanguageId])
VALUES ('D31A6823-5415-407F-9B49-49136242F03F', 'Mi Primer Libro', '895E0F72-413C-48CD-A1A1-6302AC8A4CB4')

I am trying to figure out how to select the book that the user wants with its localization record and if the language does not exist for selected book, then to return the default language that is indicated for that book.

For example, the book has english and spanish records. So if I pass in the english language Id then I get english. If I pass the spanish language id then I get spanish. If I pass french language id, then I get english because the book has no french language.

Here is what I tried:

SELECT B.[Id], B.[PublicDate], 
BL.[Id] AS BookLocalizationId, BL.[Name], BL.[LanguageId],
BL2.[LanguageId], BL2.[Default]
FROM [Book] B
INNER JOIN [BookLocalization] BL ON BL.[BookId] = B.[Id]
INNER JOIN [BookLanguage] BL2 ON BL2.[BookId] = B.[Id]

The problem with the above is that it returns 4 records. I don't fully understand exactly how I end up with 4 records and I am hoping somebody can explain why and tell me how I can achieve returning only 1 record. Or if one not possible, then two. 1st being the language I selected, 2nd being the default language for that table and then I can in code pick which record to return to user.

Edit:
BookLanguage represents all languages available/allowed for that book. Some books are translated in 2 languages, other in 5. That table controls what languages are allowed for the book to be translated in. BookLocalization is the actual translation.

答案1

得分: 2

你的书籍语言连接不够具体,你还需要根据语言进行连接,否则你会得到你所经历的交叉连接效果。

而要获取特定语言或默认语言,你可以使用ORDER BYTOP 1,在其中按这两个条件排序。

declare @LanguageId uniqueidentifier = '895E0F72-413C-48CD-A1A1-6302AC8A4CB4';

select top 1 b.[Id], b.[PublicDate], 
    blz.[Id] AS BookLocalizationId, blz.[Name], blz.[LanguageId],
    blg.[LanguageId], blg.[Default]
from Book b
inner join BookLocalization blz on blz.BookId = b.id
inner join BookLanguage blg on blg.BookId = blz.BookId
    and blg.LanguageId = blz.LanguageId
order by case when @LanguageId = blz.LanguageId then 1 else 0 end desc
    , blg.[Default] desc;

DbFiddle

连接注意事项:当你将1本书与2个BookLocalization行连接时,你会得到2行(即Books * BookLocalizations)。当你尝试连接BookLanguage时,你会有另外2行,如果你只连接BookId,你会得到4行,因为每个BookLanguage行都与两个BookLocalizations行匹配(即Books * BookLocalizations * BookLanguages)。但实际上你想要的是在BookId和LanguageId上同时匹配BookLocalization和BookLanguage。

DbFiddle

英文:

Your join of book language is not specific enough, you need to also join on language, else you get the cross-join effect you were experiencing.

And to get either the specific language or the default language, you can use TOP 1 with ORDER BY where you order by those 2 conditions.

declare @LanguageId uniqueidentifier = '895E0F72-413C-48CD-A1A1-6302AC8A4CB4';
select top 1 b.[Id], b.[PublicDate], 
blz.[Id] AS BookLocalizationId, blz.[Name], blz.[LanguageId],
blg.[LanguageId], blg.[Default]
from Book b
inner join BookLocalization blz on blz.BookId = b.id
inner join BookLanguage blg on blg.BookId = blz.BookId
and blg.LanguageId = blz.LanguageId
order by case when @LanguageId = blz.LanguageId then 1 else 0 end desc
, blg.[Default] desc;

DbFiddle

Joining notes: When you join 1 Book with 2 BookLocalization rows you get 2 rows (i.e. Books * BookLocalizations). When you come to join BookLanguage on, for which you have another 2 rows, if you only join with the BookId you get 4 rows, because each BookLanguage row matches both BookLocalizations rows (i.e. Books * BookLocalizations * BookLanguages). But what you actually want is to match a BookLocalization with a BookLanguage on both BookId and LanguageId.

DbFiddle

huangapple
  • 本文由 发表于 2023年7月7日 04:09:01
  • 转载请务必保留本文链接:https://go.coder-hub.com/76632240.html
匿名

发表评论

匿名网友

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

确定