public override IDbCommand SELECT(DBConnection db, Tabloid tabloid, IEnumerable <string> columnNames = null, Manager <Condition> conditions = null, Manager <Join> joins = null, Order order = null, GroupBy groupBy = null, int?limit = null, Page page = null, DropStep dropStep = null)
{
MySqlCommand command = new MySqlCommand();
List <string> withTable = new List <string>();
/// get columns to select
List <Tuple <string, string> > columns = new List <Tuple <string, string> >();
// column are set
if (columnNames != null && columnNames.Any())
{
columns.AddRange(ColumnsToTuple(db.Application, tabloid.Name, columnNames));
}
// all columns
else
{
// origin table
columns.AddRange(tabloid.Columns.Select(c => new Tuple <string, string>(tabloid.Name, c.Name)));
// table not found - every table have some column
if (columns.Count == 0)
{
throw new Exception($"Tabulka/View [{tabloid.Name}]({tabloid.RealName}) nenalezena!");
}
// joined tables
foreach (Join join in joins)
{
columns.AddRange(new Tabloid(db)
{
Name = join.joinTableName
}.Columns.Select(c => new Tuple <string, string>(join.joinTableName, c.Name)));
}
}
List <Tuple <string, string> > whereColumns = new List <Tuple <string, string> >(columns);
foreach (Condition condition in conditions)
{
Tuple <string, string> tuple = ColumnsToTuple(db.Application, tabloid.Name, new List <string> {
condition.column
}).First();
if (!whereColumns.Any(wc => wc.Item1.ToLower() == tuple.Item1.ToLower() && wc.Item2.ToLower() == tuple.Item2.ToLower()))
{
whereColumns.Add(tuple);
}
}
/// SELECT FROM, JOIN
command.CommandText =
$"SELECT {ColumnTuplesToString(db.Application, whereColumns)} FROM {ToRealTableName(db.Application, tabloid.Name)} {joins.ToSql(this, command)}";
/// WHERE, GROUP, HAVING
string conditionString = conditions.ToSql(this, command);
if (!string.IsNullOrEmpty(conditionString) || groupBy != null)
{
withTable.Add($"__table1 as ({command.CommandText})");
// WHERE
if (!string.IsNullOrEmpty(conditionString))
{
conditionString =
$"WHERE {conditionString}";
if (groupBy == null)
{
command.CommandText =
$"SELECT {ColumnTuplesToString(db.Application, columns, true, false)} FROM __table1 {conditionString}";
}
}
// GROUP BY
if (groupBy != null)
{
string havingString = groupBy.Having.ToSql(this, command);
if (!string.IsNullOrEmpty(havingString))
{
havingString = $"HAVING {havingString}";
}
if (groupBy.Function == ESqlFunction.none)
{
columns = ColumnsToTuple(db.Application, tabloid.Name, groupBy.Columns);
command.CommandText =
$"SELECT {ColumnTuplesToString(db.Application, columns, true, false)} FROM __table1 {conditionString} GROUP BY {ColumnTuplesToString(db.Application, columns, true, false)} {havingString}";
}
// first, last -> inner query
else if (groupBy.Function.NeedsInnerQuery())
{
command.CommandText =
$"SELECT * FROM __table1 WHERE {conditionString} {(string.IsNullOrEmpty(conditionString) ? "" : "AND")} {FullPrimaryKey(tabloid.Name)} IN (SELECT {(groupBy.Function == ESqlFunction.FIRST ? "MIN" : "MAX")}({FullPrimaryKey(tabloid.Name)}) FROM __table1 GROUP BY {ColumnTuplesToString(db.Application, ColumnsToTuple(db.Application, tabloid.Name, groupBy.Columns), true, false)} {havingString})";
}
// other functions
else
{
// get only numeric columns
if (groupBy.Function.RequireNumeric())
{
List <Tuple <string, string> > currentColumns = ColumnsToTuple(db.Application, tabloid.Name, groupBy.Columns);
foreach (string tabloidName in columns.Select(c => c.Item1).Distinct())
{
Tabloid currentTabloid = tabloidName == tabloid.Name
? tabloid
: new Tabloid(db)
{
Name = tabloidName
};
currentColumns = currentColumns.Concat(ColumnsToTuple(db.Application, currentTabloid.Name, currentTabloid.Columns.Where(c => columns.Contains(new Tuple <string, string>(currentTabloid.Name, c.Name)) && DataType.IsNumeric(c.Type)).Select(c => c.Name))).ToList();
}
columns = currentColumns;
}
command.CommandText =
$"SELECT {ColumnTuplesToString(db.Application, columns, true, false, groupBy.Function.ToString(), groupBy.Columns)} FROM __table1 {conditionString} GROUP BY {ColumnTuplesToString(db.Application, ColumnsToTuple(db.Application, tabloid.Name, groupBy.Columns), true, false)} {havingString}";
}
}
}
/// Drop step
if (dropStep != null)
{
if (dropStep.Order == null)
{
throw new ArgumentException("Order needs to be filled");
}
withTable.Add($"__table2 as ({command.CommandText})");
string rowNum = $"FLOOR((@__ROWNUM:=@__ROWNUM+1)*{dropStep.FinalCount}/(SELECT Count(*) FROM __table2)) _rowNum";
if (dropStep.Function == ESqlFunction.none)
{
throw new ArgumentException("DropStep with no function return no data!");
}
// first, last -> inner query
else if (dropStep.Function.NeedsInnerQuery())
{
command.CommandText =
$"SELECT * FROM __table2 WHERE {FullPrimaryKey(tabloid.Name)} IN (SELECT {(dropStep.Function == ESqlFunction.FIRST ? "MIN" : "MAX")}({FullPrimaryKey(tabloid.Name)}) FROM (SELECT {rowNum}, __table2.* FROM __table2, (SELECT @__ROWNUM:=-1) __fooo) __t GROUP BY _rowNum)";
}
// other functions
else
{
command.CommandText =
$"SELECT {ColumnTuplesToString(db.Application, columns, true, false, dropStep.Function.ToString())} FROM (SELECT {rowNum}, __table2.* FROM __table2, (SELECT @__ROWNUM:=-1) __fooo) __t GROUP BY _rowNum";
}
}
string limitString = page != null
? $"LIMIT {page.RowsPerPage} OFFSET {page.PageIndex * page.RowsPerPage}"
: limit != null
? $"LIMIT {limit}"
: "";
command.CommandText =
$"{(withTable.Any() ? "with " : "")}{string.Join(",", withTable)} {command.CommandText} {order?.ToSql(this, command)} {limitString}";
return(command);
}
public abstract IDbCommand SELECT(DBConnection db, Tabloid table, IEnumerable <string> columnNames = null, Manager <Condition> conditions = null, Manager <Join> joins = null, Order order = null, GroupBy groupBy = null, int?limit = null, Page page = null, DropStep dropStep = null);