// FIXME Check if facet can be refactored away
public string Compile(QueryBuilder.QuerySetup querySetup, Facet facet, IEnumerable <ResultSpecificationField> fields)
{
Debug.Assert(querySetup.Facet.FacetCode.Equals(facet.FacetCode), "Refactor check: Refactor away facet");
string sql = $@"
SELECT DISTINCT {facet.CategoryIdExpr} AS id_column, {facet.CategoryNameExpr} AS name, coalesce(latitude_dd, 0.0) AS latitude_dd, coalesce(longitude_dd, 0) AS longitude_dd
FROM {querySetup.Facet.TargetTable.ResolvedSqlJoinName}
{querySetup.Joins.Combine("")}
WHERE 1 = 1
{"AND ".GlueTo(querySetup.Criterias.Combine(" AND "))}
";
return(sql);
}
public string Compile(QueryBuilder.QuerySetup querySetup, Facet notUsed, IEnumerable <ResultSpecificationField> fields)
{
string sql = $@"
SELECT {fields.GetResultCompiledValueFields().ToList().Combine(", ")}
FROM (
SELECT {fields.GetResultColumnNameAliasPairs().Select(x => $"{x.ColumnName} AS {x.Alias}").ToList().Combine(", ")}
FROM {querySetup.Facet.TargetTable.ResolvedSqlJoinName}
{querySetup.Joins.Combine("")}
WHERE 1 = 1
{"AND ".GlueTo(querySetup.Criterias.Combine(" AND "))}
GROUP BY {fields.GetResultInnerGroupByFields().ToList().Combine(", ")}
) AS X
{"GROUP BY ".GlueTo(fields.GetResultGroupByFields().ToList().Combine(", "))}
{"ORDER BY ".GlueTo(fields.GetResultSortFields().ToList().Combine(", "))}
";
return(sql);
}
public string Compile(QueryBuilder.QuerySetup query, List <int> picks)
{
if (picks.Count == 0)
{
throw new ArgumentException("No picks specified!");
}
string picks_clause = picks.Combine(",", x => $"('{x}'::text)");
string sql = $@"
SELECT DISTINCT pick_id, {query.Facet.CategoryNameExpr} AS name
FROM {query.Facet.TargetTable.ResolvedSqlJoinName}
JOIN (VALUES {picks_clause}) AS x(pick_id)
ON x.pick_id = {query.Facet.CategoryIdExpr}::text
{query.Joins.Combine("")}
WHERE 1 = 1
{" AND ".GlueTo(query.Criterias.Combine(" AND "))}
";
return(sql);
}
