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 |
One approach...
select top(2) s.Json from QueryGuidItemStructure s
inner join
(
select si.StructureId from [QueryGuidItemIndexes] si
where (si.[MemberPath]='SortOrder' and si.[IntegerValue] = 2)
union -- REPRESENTS OR
(
select si.StructureId from [QueryGuidItemIndexes] si
where (
(si.[MemberPath]='SortOrder' and si.[IntegerValue] = 1)
)
INTERSECT -- REPRESENTS AND
select si.StructureId from [QueryGuidItemIndexes] si
where (
(si.[MemberPath]='StringValue' and si.[StringValue] = 'B')
)
)
) si on s.StructureId = si.StructureId
left join dbo.QueryGuidItemIndexes sort0 on sort0.StructureId = si.StructureId and sort0.MemberPath = 'StringValue'
left join dbo.QueryGuidItemIndexes sort1 on sort1.StructureId = si.StructureId and sort1.MemberPath = 'SortOrder'
group by s.StructureId, s.Json
order by MIN(sort0.StringValue), MIN(sort1.IntegerValue)
VICTORY!!!! This is done to take into count that MemberPath 'asdfasdfasdf' with a StringValue of 'A' could affect sorting.
select top(2) s.json, min(si.stringvalue) sort0, min(si.integervalue) sort1 from QueryGuidItemIndexes si
inner join
(
select s.StructureId
from [QueryGuidItemStructure] as s
left join [QueryGuidItemIndexes] as crit0 on crit0.[StructureId] = s.[StructureId] and (crit0.[MemberPath]='SortOrder' and crit0.[IntegerValue] = 2)
left join [QueryGuidItemIndexes] as crit1 on crit1.[StructureId] = s.[StructureId] and (crit1.[MemberPath]='SortOrder' and crit1.[IntegerValue] = 1)
left join [QueryGuidItemIndexes] as crit2 on crit2.[StructureId] = s.[StructureId] and (crit2.[MemberPath]='StringValue' and crit2.StringValue = 'B')
where
crit0.StructureId is not null
or
(
crit1.StructureId is not null
and
crit2.StructureId is not null
)
)sids on si.StructureId = sids.StructureId and si.MemberPath in('SortOrder', 'StringValue')
inner join QueryGuidItemStructure s on s.StructureId = sids.StructureId
group by si.StructureId, s.Json
order by sort0, sort1
Sorry, but it should be enough with this
select s.json from [QueryGuidItemStructure] s
left join [QueryGuidItemIndexes] as crit0 on crit0.[StructureId] = s.[StructureId] and (crit0.[MemberPath]='SortOrder' and crit0.[IntegerValue] = 2)
left join [QueryGuidItemIndexes] as crit1 on crit1.[StructureId] = s.[StructureId] and (crit1.[MemberPath]='SortOrder' and crit1.[IntegerValue] = 1)
left join [QueryGuidItemIndexes] as crit2 on crit2.[StructureId] = s.[StructureId] and (crit2.[MemberPath]='StringValue' and crit2.StringValue = 'B')
inner join [QueryGuidItemIndexes] as si on si.StructureId = s.StructureId and si.MemberPath in ('SortOrder', 'StringValue')
where
crit0.StructureId is not null
or
(
crit1.StructureId is not null
and
crit2.StructureId is not null
)
group by s.StructureId, s.Json
order by min(si.stringvalue), min(si.integervalue)
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
Another solution, but how to order by?