Created
November 6, 2011 20:43
-
-
Save danielwertheim/1343449 to your computer and use it in GitHub Desktop.
Key-value querying
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
-- C# Predicate: item.SortOrder == 2 || (item.SortOrder == 1 && item.StringValue == "B").Take(2).SortBy(item => item.StringValue) | |
-- Existing rows | |
-- {"StructureId":"208fad84b608e111b21c544249037e42","SortOrder":2,"StringValue":"D"} | |
-- {"StructureId":"218fad84b608e111b21c544249037e42","SortOrder":2,"StringValue":"C"} | |
-- {"StructureId":"228fad84b608e111b21c544249037e42","SortOrder":1,"StringValue":"B"} | |
-- {"StructureId":"238fad84b608e111b21c544249037e42","SortOrder":1,"StringValue":"A"} | |
-- Desired outcome | |
-- {"StructureId":"228fad84b608e111b21c544249037e42","SortOrder":1,"StringValue":"B"} | |
-- {"StructureId":"218fad84b608e111b21c544249037e42","SortOrder":2,"StringValue":"C"} | |
-- Failing SQL for Sql2008, SqlCe has limited support giving other syntax, but first things first and that is SQL2008 | |
select top(2) s.Json | |
from [QueryGuidItemStructure] as s | |
inner join [QueryGuidItemIndexes] as si on si.[StructureId] = s.[StructureId] | |
where | |
( | |
(si.[MemberPath]='SortOrder' and si.[IntegerValue] = 2) | |
or | |
( | |
(si.[MemberPath]='SortOrder' and si.[IntegerValue] = 1) | |
and | |
(si.[MemberPath]='StringValue' and si.[StringValue] = 'B') | |
) | |
) | |
group by s.[StructureId], s.[Json] | |
order by min(si.[StringValue]) Asc; |
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
USE [SisoDbSpecs] | |
GO | |
/****** Object: Table [dbo].[QueryGuidItemStructure] Script Date: 11/06/2011 21:30:59 ******/ | |
SET ANSI_NULLS ON | |
GO | |
SET QUOTED_IDENTIFIER ON | |
GO | |
CREATE TABLE [dbo].[QueryGuidItemStructure]( | |
[StructureId] [uniqueidentifier] ROWGUIDCOL NOT NULL, | |
[Json] [nvarchar](max) NOT NULL, | |
CONSTRAINT [PK_QueryGuidItemStructure] PRIMARY KEY CLUSTERED | |
( | |
[StructureId] ASC | |
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] | |
) ON [PRIMARY] | |
GO | |
INSERT [dbo].[QueryGuidItemStructure] ([StructureId], [Json]) VALUES (N'2b0a73d9-6908-e111-b21c-544249037e42', N'{"StructureId":"2b0a73d96908e111b21c544249037e42","SortOrder":2,"StringValue":"D"}') | |
INSERT [dbo].[QueryGuidItemStructure] ([StructureId], [Json]) VALUES (N'2c0a73d9-6908-e111-b21c-544249037e42', N'{"StructureId":"2c0a73d96908e111b21c544249037e42","SortOrder":2,"StringValue":"C"}') | |
INSERT [dbo].[QueryGuidItemStructure] ([StructureId], [Json]) VALUES (N'2d0a73d9-6908-e111-b21c-544249037e42', N'{"StructureId":"2d0a73d96908e111b21c544249037e42","SortOrder":1,"StringValue":"B"}') | |
INSERT [dbo].[QueryGuidItemStructure] ([StructureId], [Json]) VALUES (N'2e0a73d9-6908-e111-b21c-544249037e42', N'{"StructureId":"2e0a73d96908e111b21c544249037e42","SortOrder":1,"StringValue":"A"}') | |
/****** Object: Table [dbo].[SisoDbIdentities] Script Date: 11/06/2011 21:30:59 ******/ | |
SET ANSI_NULLS ON | |
GO | |
SET QUOTED_IDENTIFIER ON | |
GO | |
SET ANSI_PADDING ON | |
GO | |
CREATE TABLE [dbo].[SisoDbIdentities]( | |
[EntityHash] [varchar](8) NOT NULL, | |
[EntityName] [varchar](100) NOT NULL, | |
[CurrentId] [bigint] NOT NULL, | |
CONSTRAINT [PK_SisoDbIdentities] PRIMARY KEY CLUSTERED | |
( | |
[EntityHash] ASC | |
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] | |
) ON [PRIMARY] | |
GO | |
SET ANSI_PADDING OFF | |
GO | |
/****** Object: Table [dbo].[QueryGuidItemUniques] Script Date: 11/06/2011 21:30:59 ******/ | |
SET ANSI_NULLS ON | |
GO | |
SET QUOTED_IDENTIFIER ON | |
GO | |
SET ANSI_PADDING ON | |
GO | |
CREATE TABLE [dbo].[QueryGuidItemUniques]( | |
[StructureId] [uniqueidentifier] NOT NULL, | |
[UqStructureId] [uniqueidentifier] NULL, | |
[UqMemberPath] [varchar](250) NOT NULL, | |
[UqValue] [nvarchar](10) NOT NULL | |
) ON [PRIMARY] | |
GO | |
SET ANSI_PADDING OFF | |
GO | |
/****** Object: Table [dbo].[QueryGuidItemIndexes] Script Date: 11/06/2011 21:30:59 ******/ | |
SET ANSI_NULLS ON | |
GO | |
SET QUOTED_IDENTIFIER ON | |
GO | |
SET ANSI_PADDING ON | |
GO | |
CREATE TABLE [dbo].[QueryGuidItemIndexes]( | |
[RowId] [bigint] IDENTITY(1,1) NOT NULL, | |
[StructureId] [uniqueidentifier] NOT NULL, | |
[MemberPath] [varchar](250) NOT NULL, | |
[StringValue] [nvarchar](max) NULL, | |
[IntegerValue] [bigint] NULL, | |
[FractalValue] [real] NULL, | |
[DateTimeValue] [datetime] NULL, | |
[BoolValue] [bit] NULL, | |
[GuidValue] [uniqueidentifier] NULL, | |
CONSTRAINT [PK_QueryGuidItemIndexes] PRIMARY KEY CLUSTERED | |
( | |
[RowId] ASC | |
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] | |
) ON [PRIMARY] | |
GO | |
SET ANSI_PADDING OFF | |
GO | |
SET IDENTITY_INSERT [dbo].[QueryGuidItemIndexes] ON | |
INSERT [dbo].[QueryGuidItemIndexes] ([RowId], [StructureId], [MemberPath], [StringValue], [IntegerValue], [FractalValue], [DateTimeValue], [BoolValue], [GuidValue]) VALUES (1, N'2b0a73d9-6908-e111-b21c-544249037e42', N'SortOrder', NULL, 2, NULL, NULL, NULL, NULL) | |
INSERT [dbo].[QueryGuidItemIndexes] ([RowId], [StructureId], [MemberPath], [StringValue], [IntegerValue], [FractalValue], [DateTimeValue], [BoolValue], [GuidValue]) VALUES (2, N'2b0a73d9-6908-e111-b21c-544249037e42', N'StringValue', N'D', NULL, NULL, NULL, NULL, NULL) | |
INSERT [dbo].[QueryGuidItemIndexes] ([RowId], [StructureId], [MemberPath], [StringValue], [IntegerValue], [FractalValue], [DateTimeValue], [BoolValue], [GuidValue]) VALUES (3, N'2c0a73d9-6908-e111-b21c-544249037e42', N'SortOrder', NULL, 2, NULL, NULL, NULL, NULL) | |
INSERT [dbo].[QueryGuidItemIndexes] ([RowId], [StructureId], [MemberPath], [StringValue], [IntegerValue], [FractalValue], [DateTimeValue], [BoolValue], [GuidValue]) VALUES (4, N'2c0a73d9-6908-e111-b21c-544249037e42', N'StringValue', N'C', NULL, NULL, NULL, NULL, NULL) | |
INSERT [dbo].[QueryGuidItemIndexes] ([RowId], [StructureId], [MemberPath], [StringValue], [IntegerValue], [FractalValue], [DateTimeValue], [BoolValue], [GuidValue]) VALUES (5, N'2d0a73d9-6908-e111-b21c-544249037e42', N'SortOrder', NULL, 1, NULL, NULL, NULL, NULL) | |
INSERT [dbo].[QueryGuidItemIndexes] ([RowId], [StructureId], [MemberPath], [StringValue], [IntegerValue], [FractalValue], [DateTimeValue], [BoolValue], [GuidValue]) VALUES (6, N'2d0a73d9-6908-e111-b21c-544249037e42', N'StringValue', N'B', NULL, NULL, NULL, NULL, NULL) | |
INSERT [dbo].[QueryGuidItemIndexes] ([RowId], [StructureId], [MemberPath], [StringValue], [IntegerValue], [FractalValue], [DateTimeValue], [BoolValue], [GuidValue]) VALUES (7, N'2e0a73d9-6908-e111-b21c-544249037e42', N'SortOrder', NULL, 1, NULL, NULL, NULL, NULL) | |
INSERT [dbo].[QueryGuidItemIndexes] ([RowId], [StructureId], [MemberPath], [StringValue], [IntegerValue], [FractalValue], [DateTimeValue], [BoolValue], [GuidValue]) VALUES (8, N'2e0a73d9-6908-e111-b21c-544249037e42', N'StringValue', N'A', NULL, NULL, NULL, NULL, NULL) | |
SET IDENTITY_INSERT [dbo].[QueryGuidItemIndexes] OFF | |
/****** Object: Default [DF__SisoDbIde__Curre__7F60ED59] Script Date: 11/06/2011 21:30:59 ******/ | |
ALTER TABLE [dbo].[SisoDbIdentities] ADD DEFAULT ((0)) FOR [CurrentId] | |
GO | |
/****** Object: ForeignKey [FK_QueryGuidItemIndexes_QueryGuidItemStructure] Script Date: 11/06/2011 21:30:59 ******/ | |
ALTER TABLE [dbo].[QueryGuidItemIndexes] WITH NOCHECK ADD CONSTRAINT [FK_QueryGuidItemIndexes_QueryGuidItemStructure] FOREIGN KEY([StructureId]) | |
REFERENCES [dbo].[QueryGuidItemStructure] ([StructureId]) | |
ON DELETE CASCADE | |
GO | |
ALTER TABLE [dbo].[QueryGuidItemIndexes] CHECK CONSTRAINT [FK_QueryGuidItemIndexes_QueryGuidItemStructure] | |
GO | |
/****** Object: ForeignKey [FK_QueryGuidItemUniques_QueryGuidItemStructure] Script Date: 11/06/2011 21:30:59 ******/ | |
ALTER TABLE [dbo].[QueryGuidItemUniques] WITH NOCHECK ADD CONSTRAINT [FK_QueryGuidItemUniques_QueryGuidItemStructure] FOREIGN KEY([StructureId]) | |
REFERENCES [dbo].[QueryGuidItemStructure] ([StructureId]) | |
ON DELETE CASCADE | |
GO | |
ALTER TABLE [dbo].[QueryGuidItemUniques] CHECK CONSTRAINT [FK_QueryGuidItemUniques_QueryGuidItemStructure] | |
GO |
select top 2 s.json from [QueryGuidItemStructure] s
left join [QueryGuidItemIndexes] as mem0 on mem0.[StructureId] = s.[StructureId] and mem0.[MemberPath]='SortOrder'
left join [QueryGuidItemIndexes] as mem1 on mem1.[StructureId] = s.[StructureId] and mem1.[MemberPath]='SortOrder'
left join [QueryGuidItemIndexes] as mem2 on mem2.[StructureId] = s.[StructureId] and mem2.[MemberPath]='StringValue'
inner join [QueryGuidItemIndexes] as si on si.StructureId = s.StructureId and si.MemberPath in ('SortOrder', 'StringValue')
where
(mem0.[IntegerValue] = 2)
or
((mem1.[IntegerValue] = 1) and (mem2.StringValue = 'B'))
group by s.StructureId, s.Json
order by min(si.stringvalue), min(si.integervalue)
select top 2 min(s.json) from [QueryGuidItemStructure] s
inner join [QueryGuidItemIndexes] as si on si.StructureId = s.StructureId
inner join [QueryGuidItemIndexes] as mem0 on mem0.[StructureId] = s.[StructureId] and mem0.[MemberPath]='SortOrder'
inner join [QueryGuidItemIndexes] as mem2 on mem2.[StructureId] = s.[StructureId] and mem2.[MemberPath]='StringValue'
where
(mem0.[IntegerValue] = 2)
or
((mem0.[IntegerValue] = 1) and (mem2.StringValue = 'B'))
group by s.StructureId
order by min(mem2.stringvalue), min(mem0.integervalue)
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Sorry, but it should be enough with this