Last active
December 23, 2015 14:49
-
-
Save patriklindstrom/6651718 to your computer and use it in GitHub Desktop.
StackOverFlowQuestion about how to express a question for left self join in Raven DB. To explain what I want I use T-SQL. Below Example of RavenDB map reduce index that almost does it.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
public class PageTextElement | |
{ | |
public string Page; | |
public string Token; | |
public string Webtext; | |
public string Language; | |
public string Translator; | |
public DateTime CreationTime; | |
} | |
public class ComparePageTextElementCount | |
{ | |
public string Page; | |
public string Token; | |
public string Webtext; | |
public string WebtextCompare; | |
public int Count; | |
} | |
public class LeftJoinPageTextTranslationsCount : AbstractMultiMapIndexCreationTask<ComparePageTextElementCount> | |
{ | |
public LeftJoinPageTextTranslationsCount() | |
{ | |
AddMap<PageTextElement>(baseElements => | |
from baseElement in baseElements.Where(l => l.Language == "en") | |
select | |
new | |
{ | |
baseElement.Page, | |
baseElement.Token, | |
baseElement.Webtext, | |
WebtextCompare = (string)null, | |
Count = 0 | |
}); | |
AddMap<PageTextElement>(compareElements => | |
from compareElement in compareElements.Where(l => l.Language == "sv") | |
select | |
new | |
{ | |
compareElement.Page, | |
compareElement.Token, | |
Webtext = (string)null, | |
WebtextCompare = compareElement.Webtext, | |
Count = 1 | |
} | |
); | |
Reduce = results => from result in results | |
group result by | |
new { result.Page, result.Token } | |
into g | |
select new | |
{ | |
g.Key.Page, | |
g.Key.Token, | |
Webtext = g.Select(x => x.Webtext).FirstOrDefault(x => x != null), | |
WebtextCompare = g.Select(x => x.WebtextCompare).FirstOrDefault(x => x != null), | |
Count = g.Sum(x => x.Count) | |
}; | |
Index(x => x.Webtext, FieldIndexing.Analyzed); | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DROP TABLE PageTextElement_TB | |
CREATE TABLE PageTextElement_TB ( | |
[Page] CHAR(10)NOT NULL | |
,[Token] CHAR(10)NOT NULL | |
,[Webtext] NVARCHAR(Max) NOT NULL | |
,LANGUAGE CHAR(2) NOT NULL | |
,Translator NVARCHAR(10) NOT NULL | |
,CreationTime DATETIME NOT NULL | |
) | |
ALTER TABLE [dbo].[PageTextElement_TB] ADD CONSTRAINT [DF_PageTextElement_TB_CreationTime] DEFAULT (getdate()) FOR [CreationTime] | |
GO | |
USE [test] | |
GO | |
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_PageTextElement_TB_CreationTime]') AND type = 'D') | |
BEGIN | |
ALTER TABLE [dbo].[PageTextElement_TB] DROP CONSTRAINT [DF_PageTextElement_TB_CreationTime] | |
END | |
GO | |
USE [test] | |
GO | |
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PageTextElement_TB]') AND type in (N'U')) | |
DROP TABLE [dbo].[PageTextElement_TB] | |
CREATE TABLE [dbo].[PageTextElement_TB]( | |
[Page] [char](10) NOT NULL, | |
[Token] [char](15) NOT NULL, | |
[Language] [char](2) NOT NULL, | |
[Webtext] [nvarchar](max) NOT NULL, | |
[Translator] [nvarchar](10) NOT NULL, | |
[CreationTime] [datetime] NOT NULL, | |
CONSTRAINT [PK_PageTextElement_TB] PRIMARY KEY CLUSTERED | |
( | |
[Page] ASC, | |
[Token] ASC, | |
[Language] ASC | |
)) | |
ALTER TABLE [dbo].[PageTextElement_TB] ADD CONSTRAINT [DF_PageTextElement_TB_CreationTime] DEFAULT (getdate()) FOR [CreationTime] | |
GO | |
INSERT INTO dbo.PageTextElement_TB | |
( Page , | |
Token , | |
[Language] , | |
Webtext , | |
Translator | |
) | |
VALUES | |
-- English text | |
( 'home' ,'Welcome' ,'en' , | |
N'Welcome to Aniara' , N'robot' | |
), | |
( 'home' ,'RulesOfBoarding' , 'en' , | |
N'Do not break line' , N'robot' | |
) | |
, | |
( 'home' , 'PriceModel' , 'en' , | |
N'Based on weight and oxygen consumption' , N'robot' | |
) | |
-- Here comes the one line that is missing in other language should be visible in left join | |
,( 'home' , 'RebateModel' , 'en' , | |
N'Truly Unique talent cant reduce price with 50%' , N'robot' | |
) | |
-- Swedish text | |
,( 'home' ,'Welcome' ,'sv' , | |
N'Välkommen till Aniara' , N'robot' | |
), | |
( 'home' ,'RulesOfBoarding' , 'sv' , | |
N'Träng dig ej i kön' , N'robot' | |
) | |
, | |
( 'home' , 'PriceModel' , 'sv' , | |
N'Priset baseras på vikt och syreförbrukning' , N'robot' | |
) | |
GO | |
-- I can express this in RavenDB | |
SELECT bt.Page,bt.Token,bt.Webtext AS baseText,COUNT(ct.Webtext) FROM dbo.PageTextElement_TB bt | |
LEFT JOIN dbo.PageTextElement_TB AS ct ON bt.Page=ct.Page AND bt.Token = ct.Token AND ct.Language='sv' | |
WHERE bt.Language='en' | |
GROUP BY bt.Page,bt.Token,bt.Webtext | |
-- But I want to express this in RavenDB | |
SELECT bt.Page,bt.Token,bt.Webtext AS baseText,ct.Webtext AS compareText FROM dbo.PageTextElement_TB bt | |
LEFT JOIN dbo.PageTextElement_TB AS ct ON bt.Page=ct.Page AND bt.Token = ct.Token AND ct.Language='sv' | |
WHERE bt.Language='en' |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
It is based on the http://ayende.com/blog/89089/ravendb-multi-maps-reduce-indexes.
The project with my experiment are here : https://github.com/patriklindstrom/LeftJoinRavenDB/tree/StackOverFlowQuestion