Skip to content

Instantly share code, notes, and snippets.

@sciolist
Created September 18, 2015 17:39
Show Gist options
  • Save sciolist/8852548856c987afd051 to your computer and use it in GitHub Desktop.
Save sciolist/8852548856c987afd051 to your computer and use it in GitHub Desktop.
Awful faceting with NPoco and SQL Server 2012
using NPoco;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Xml.Linq;
namespace NPocoFaceting
{
public class FacetBuilder
{
private IList<Facet> _facets = new List<Facet>();
public void Add(Facet facet)
{
_facets.Add(facet);
}
public Sql CreateFilter(params string[] ignoredFacets)
{
var sql = new Sql();
var hit = false;
foreach (var facet in _facets)
{
if (ignoredFacets.Contains(facet.Name)) continue;
hit = true;
sql = sql.Where(facet.Filter.SQL, facet.Filter.Arguments);
}
if (!hit) return new Sql("WHERE 1=1");
return sql;
}
public Sql ToSql(Sql baseQuery)
{
var selectSql = new Sql();
var sql = new Sql("; WITH _FACETSOURCE AS (");
sql.Append(baseQuery.SQL, baseQuery.Arguments);
sql.Append(")");
for (var i = 0; i < _facets.Count; ++i)
{
var where = CreateFilter(_facets[i].Name);
var value = _facets[i];
sql.Append(", FACET_" + i + "_INNER AS (SELECT (");
sql.Append(value.Expression.SQL, value.Expression.Arguments);
sql.Append(") AS [key], (CASE WHEN (");
sql.Append(value.SelectionFilter.SQL, value.SelectionFilter.Arguments);
sql.Append(") THEN 1 ELSE 0 END) AS [selected] FROM _FACETSOURCE");
sql.Append("GROUP BY (");
sql.Append(value.Expression.SQL, value.Expression.Arguments);
sql.Append("))");
sql.Append(", FACET_" + i + " AS (SELECT *");
sql.Append(", (SELECT COUNT(1) FROM _FACETSOURCE");
sql.Append(where.SQL, where.Arguments);
sql.Append(" AND src.[key] = (");
sql.Append(value.Expression.SQL, value.Expression.Arguments);
sql.Append(")) as [count] FROM FACET_" + i + "_INNER AS src)");
}
sql.Append("SELECT ");
for (var i = 0; i < _facets.Count; ++i)
{
sql.Append(i > 0 ? "," : "");
sql.Append(" (SELECT * FROM FACET_" + i);
if (!_facets[i].IncludeZeroes)
{
sql.Append(" WHERE [count] > 0 OR [selected] = 1");
}
sql.Append(" FOR XML PATH('facet'), TYPE) as [FACET_" + i + "]");
}
sql.Append("FOR XML PATH('facets'), TYPE;");
return sql;
}
public IList<FacetResult> ParseFacets(string facetString)
{
return XDocument.Parse(facetString)
.Root
.Elements()
.Select(el => new FacetResult
{
Name = _facets[Convert.ToInt32(el.Name.LocalName.Substring("FACET_".Length))].Name,
Values = el.Elements().Select(val => new FacetResultValue
{
Key = val.Element("key").Value,
Count = Convert.ToInt32(val.Element("count").Value),
IsSelected = Convert.ToInt32(val.Element("selected").Value) != 0
}).ToList()
})
.ToList();
}
}
public class Facet
{
public Facet(string name, object[] selections)
: this(name, new Sql(name), selections)
{
}
public Facet(string name, Sql expression, object[] selections)
{
Name = name;
HasSelections = selections.Length > 0;
Filter = CreateFilter(expression, selections);
Expression = expression;
}
public Facet(string name, Sql filter, Sql expression, bool hasSelections)
{
Name = name;
HasSelections = hasSelections;
Filter = filter;
Expression = expression;
}
private Sql CreateFilter(Sql expression, object[] selectedValues)
{
var sql = new Sql("(1=1)");
if (selectedValues.Length > 0)
{
sql = new Sql(expression.SQL, expression.Arguments);
sql.Append(" IN (");
for (var i = 0; i < selectedValues.Length; ++i)
{
if (i > 0) sql.Append(",");
sql.Append("@0", selectedValues[i]);
}
sql.Append(")");
}
return sql;
}
public string Name { get; set; }
public Sql Expression { get; set; }
public Sql Filter { get; set; }
public bool HasSelections { get; set; }
public bool IncludeZeroes { get; set; }
public Sql SelectionFilter
{
get { return HasSelections ? Filter : new Sql("(1=0)"); }
}
}
public class FacetResult
{
public string Name { get; set; }
public IList<FacetResultValue> Values { get; set; }
}
public class FacetResultValue
{
public string Key { get; set; }
public int Count { get; set; }
public bool IsSelected { get; set; }
}
class Program
{
static void Main(string[] args)
{
using (var db = new Database("server=.;data source=exampledb;integrated security=sspi;", DatabaseType.SqlServer2012))
{
string sqlCount, sqlPage;
// create a base query, used as the data source for your facets.
var query = new Sql("select * from users");
query.Where("country = 'US'");
// create a facet query and add the fields to collect facets for.
var facetBuilder = new FacetBuilder();
facetBuilder.Add(new Facet("Disabled", new object[] { }));
facetBuilder.Add(new Facet("Role", new object[] { 1 }));
// build an sql query to load the facets.
var facetSql = facetBuilder.ToSql(query);
// you can quickly apply all selected facets to your base query using 'CreateFilter'.
query.Append(facetBuilder.CreateFilter());
// for the heck of it, add some paging too
query.OrderBy("Name");
var queryArgs = query.Arguments;
db.BuildPageQueries<dynamic>(0, 5, query.SQL, ref queryArgs, out sqlCount, out sqlPage);
// now we'll create our main query, which combines for the facets query with your regular query (optional ofc.)
var sql = new Sql();
sql.Append(facetSql);
sql.Append(sqlCount + ";" + sqlPage, queryArgs);
// load the recordsets, recordset 1 is an xml of the facets, recordset 2+3 is our paging query.
var data = db.FetchMultiple<string, int, dynamic>(sql);
// parse the facet xml into a more useable form.
var facets = facetBuilder.ParseFacets(data.Item1[0]);
Console.WriteLine(facets);
}
Console.ReadKey(true);
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment