// 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); }