Querying the database — IColligoQueryable

IColligoQueryable<T> translates a fluent operator chain into SQL executed against a live database connection. It requires FluentSQL for SQL generation and a DataEngine connection.

Compile-time gate

IColligoQueryable<T> implementation is gated behind the {$DEFINE QUERYABLE} compiler directive in Colligo.Queryable.pas. The type declarations are always available; the method bodies compile only when QUERYABLE is defined.

Setup

uses
  Colligo,
  Colligo.Queryable;

Creating a queryable from a connection

var LQuery: IColligoQueryable<TCustomer>;
LQuery := IColligoQueryable<TCustomer>.CreateForDatabase(
  procedure(var ADatabase: TDBEngineDriver; var AConnection: IDBConnection)
  begin
    ADatabase  := dnFirebird;      // from DataEngine.FactoryInterfaces
    AConnection := MyDBConnection; // your IDBConnection implementation
  end);

Or with explicit parameters:

LQuery := IColligoQueryable<TCustomer>.CreateForDatabase(
  dnFirebird,
  MyDBConnection
  {, optional pre-built IFluentSQLAST }
);

Basic SELECT

var LCustomers := LQuery
  .From('CUSTOMERS')
  .Where('ACTIVE = 1')
  .Select('ID, NAME, EMAIL')
  .ToList;
// Executes: SELECT ID, NAME, EMAIL FROM CUSTOMERS WHERE ACTIVE = 1

WHERE with typed expressions

Use QE (query expression factory) for typed WHERE conditions:

var LResult := LQuery
  .From('ORDERS')
  .Where(LQuery.QE.Field('TOTAL').GreaterThan(100.0))
  .ToArray;

IColligoQueryExpression operators

IColligoQueryExpression (from Colligo.Expression) supports:

• Field(name) — selects a field
• Equal, NotEqual, GreaterThan, LessThan, GreaterThanOrEqual, LessThanOrEqual
• IsNull, IsNotNull
• Like, NotLike, Contains, StartsWith, EndsWith
• InValues, NotInValues
• Exists, NotExists
• AndWith, OrWith

Joins

var LResult := LQuery
  .From('ORDERS', 'O')
  .InnerJoin('CUSTOMERS', 'C')
  .OnCond('O.CUSTOMER_ID = C.ID')
  .Select('O.ID, C.NAME, O.TOTAL')
  .Where('O.TOTAL > 500')
  .ToList;

OrderBy / ThenBy

var LResult := LQuery
  .From('PRODUCTS')
  .OrderBy('CATEGORY')
  .ThenBy(LQuery.QE.Field('PRICE'))
  .ToList;

Take / Skip (pagination)

var LPage := LQuery
  .From('ORDERS')
  .OrderBy('ID')
  .Skip(20)
  .Take(10)
  .ToList;
// Page 3 of 10 records per page

Aggregations on DB

var LTotal := LQuery
  .From('ORDERS')
  .Where('CUSTOMER_ID = 42')
  .Sum<Double>('TOTAL');

var LCount := LQuery
  .From('ORDERS')
  .Count;

AsString — inspect generated SQL

var LSQL := LQuery
  .From('PRODUCTS')
  .Where('ACTIVE = 1')
  .Select('ID, NAME')
  .AsString;
Writeln(LSQL);
// SELECT ID, NAME FROM PRODUCTS WHERE ACTIVE = 1

Supported databases

IColligoQueryable<T> delegates SQL generation to FluentSQL, which supports:

MSSQL, MySQL, Firebird, SQLite, Interbase, DB2, Oracle, Informix, PostgreSQL, ADS, ASA, AbsoluteDB, MongoDB, ElevateDB, NexusDB.

DataEngine connection contract

IColligoQueryable<T> requires an IDBConnection from DataEngine.FactoryInterfaces. The key members used internally are:

Member	Purpose
CreateDataSet(ASQL)	Opens a read-only result set for the given SQL string.
ExecuteDirect(ASQL)	Runs a non-query statement (INSERT / UPDATE / DELETE).
StartTransaction / Commit / Rollback	Transaction control (inherited from IDBTransaction).
IsConnected	Guards against executing queries on a closed connection.
GetDriver: TDBEngineDriver	Returns the engine enum used to select the correct FluentSQL dialect.

TDBEngineDriver is declared in DataEngine.FactoryInterfaces:

TDBEngineDriver = (
  dnMSSQL, dnMySQL, dnFirebird, dnSQLite, dnInterbase, dnDB2,
  dnOracle, dnInformix, dnPostgreSQL, dnADS, dnASA,
  dnFirebase, dnFirebird3, dnAbsoluteDB, dnMongoDB,
  dnElevateDB, dnNexusDB, dnMariaDB, dnMemory
);

TConnectionInitializer (used by CreateForDatabase with a procedure argument) is a procedure reference declared in Colligo.Queryable:

TConnectionInitializer = reference to procedure(
  var ADatabase:   TDBEngineDriver;
  var AConnection: IDBConnection);

Pass your own IDBConnection implementation (e.g. from DataEngine's FireDAC or SQLite adapters) to either CreateForDatabase overload. IColligoQueryable<T> does not own the connection — lifetime management is the caller's responsibility.