public virtual string GetDataJoinTable(ReportColumnMapping groupByColumn, List <TableRelationship> statsRelationships, TableRelationshipGraphBuilder graphBuilder)
{
var statsJoinTables = statsRelationships
.Select(x => x.Table1 is StatsTableMapping ? x.Table2.KnownTableName : x.Table1.KnownTableName)
.ToList();
// find the nearest stats table to the group by table.
var graph = graphBuilder.Build(_tableMappings.GetAllTableRelationships(), groupByColumn.KnownTable);
graphBuilder.TrimToTables(graph, statsJoinTables);
var dataTables = graphBuilder.GetAllTables(graph).Distinct().ToList();
dataTables = dataTables.OrderBy(x => graphBuilder.GetDistance(graph, x)).ToList();
var joinTable = dataTables.FirstOrDefault(statsJoinTables.Contains);
return(joinTable);
}
public virtual void BuildFrom(
SelectQuery query,
List <ReportColumnMapping> selectedColumns,
ReportColumnMapping groupByColumn,
ReportColumnMapping sortByColumn,
MappedSearchRequest request)
{
var tables = request.SelectedAndDependantColumns.Select(x => x.KnownTable).ToList();
tables.Add(groupByColumn.KnownTable);
tables = tables.Distinct().ToList();
var currencyColumn = GetCurrencyColumn();
if (currencyColumn != null && !tables.Contains(currencyColumn.KnownTable))
{
tables.Add(currencyColumn.KnownTable);
}
var rootTableMapping = _tableMappings.GetTableMapping(_constants.RootTableName);
query.From(rootTableMapping.DbTableName, rootTableMapping.Alias);
var graphBuilder = new TableRelationshipGraphBuilder();
// bulild a graph of the table relationships to calculate the distance from the root table
// include missing tables needed for joins
// order the tables by distance
var relationshipGraph = graphBuilder.Build(_tableMappings.GetAllTableRelationships(), _constants.RootTableName);
graphBuilder.TrimToTables(relationshipGraph, tables);
tables = graphBuilder.GetAllTables(relationshipGraph).Union(tables).Distinct().ToList();
tables = tables.OrderBy(x => graphBuilder.GetDistance(relationshipGraph, x)).ToList();
// dictionary of TableName, IsAggregated for tables which have been added to the query
var addedTables = new Dictionary <string, bool> {
{ _constants.RootTableName, false }
};
foreach (var table in tables.Distinct())
{
AddFromTableJoin(query, groupByColumn, table, ref addedTables, graphBuilder, relationshipGraph);
}
}
protected void AddMissingDataTables(
SelectQuery query,
ReportColumnMapping groupByColumn,
TableRelationshipGraphBuilder graphBuilder,
List <string> tables,
QueryBuilderBase queryBuilderBase,
Dictionary <string, bool> addedTables)
{
// build a graph of the table relationships to calculate the distance from the root table
// include missing tables needed for joins
// order the tables by distance
var relationshipGraph = graphBuilder.Build(_tableMappings.GetAllTableRelationships(), _constants.RootTableName);
graphBuilder.TrimToTables(relationshipGraph, tables);
tables = graphBuilder.GetAllTables(relationshipGraph).Union(tables).Distinct().ToList();
tables = tables.OrderBy(x => graphBuilder.GetDistance(relationshipGraph, x)).ToList();
foreach (var table in tables.Distinct())
{
queryBuilderBase.AddFromTableJoin(query, groupByColumn, table, ref addedTables, graphBuilder, relationshipGraph);
}
}