LiteSQL
Selecting Persistents and Expr-API

select-function

template <class T> select(const Database& db, const Expr& filter=Expr());

Template function select<T> returns DataSource which can be used to retrieve Persistent-objects or to create more complex queries.

select-function has two parameters: Database and expression (Expr) which can be used to limit the result set.

DataSource

DataSource<T> is basically a wrapper for selection query and is used to access objects of type T.

DataSource<T> has following methods:

  • idQuery(): returns SelectQuery which returns id-number of Persistent objects
  • objectQuery(): returns SelectQuery which returns data rows of Persistent objects
  • cursor(): returns Cursor<T> which will return Persistent objects one by one
  • one(): returns first Persistent object in the result set. Note: throws NotFound - if the result set is empty
  • all(): returns the whole result set as Persistent objects in vector
  • count(): returns number of object in the result set
  • orderBy(FieldType field, bool ascending=true): result set can be ordered with this method.
  • orderByRelation(FieldType id, FieldType field, bool ascending=true): orders the result set by external relation

Selection Examples

An example of select and selectOne with database:

PersonDatabase db("sqlite3", "database=person.db");
vector<Person> = select<Person>(db).all();
Person bob = select<Person>(db, Person::Name == "Bob").one();

SelectQuery-class can be used when creating SQL SELECT-statements. Refer to documentation of SelectQuery for a list of methods. Methods can be combined:

Records recs = db.query(SelectQuery().result("id_")
.source("Person_")
.where(Person::Name == "Bob"));

Selection expressions (filters)

Filters for select are created using Expr-API. The simplest form of a filter expression is <field> <operator> <value>. Example:

Person::Name == "Bob"

These expression can be combined using parenthesis and connectives:

() && ||

Operators that can be used in expressions are

== > < >= <= ! != .in .like

Persistent's (static) field information can be accessed using static FieldType-objects The name of the FieldType-object is almost the same as the field. Capitalize name of the field to get name of the FieldType-object.

// Person's name
Person::Name

Set-operations intersect, union_ and except

template <class T> intersect(const DataSource<T>& ds1,
const DataSource<T>& ds2);
template <class T> union_(const DataSource<T>& ds1,
const DataSource<T>& ds2);
template <class T> except(const DataSource<T>& ds1,
const DataSource<T>& ds2);

Two DataSources of same type can be used to create a new DataSource which can be used to access intersection, union or difference of the sources.

Notes: -'union' is a reserved word of C so union_ is the name of set operation. -see MySQL specific notes about using these set operations with MySQL

// intersection of Bob's and Bill's friends
DataSource<Person> commonFriends =
intersect(bob.friends().get(), bill.friends().get());
// persons that are Bob's friends and/or Bill's friends
DataSource<Person> allFriends =
union_(bob.friends().get(), bill.friends().get());
// persons that are Bob's friends but not Bill's friends
DataSource<Person> allFriends =
except(bob.friends().get(), bill.friends().get());
litesql::union_
litesql::DataSource< T > union_(const litesql::DataSource< T > &ds1, const litesql::DataSource< T > &ds2)
returns DataSource for accessing union of two sets of objects of type T
Definition: operations.hpp:31
litesql::Records
std::vector< Record > Records
shortcut
Definition: types.hpp:26
litesql::intersect
litesql::DataSource< T > intersect(const litesql::DataSource< T > &ds1, const litesql::DataSource< T > &ds2)
returns DataSource for accessing intersection of two sets of objects of type T
Definition: operations.hpp:23
litesql::select
litesql::DataSource< T > select(const litesql::Database &db, const litesql::Expr &e=litesql::Expr())
returns DataSource for accessing objects of type T
Definition: operations.hpp:16
litesql::except
litesql::DataSource< T > except(const litesql::DataSource< T > &ds1, const litesql::DataSource< T > &ds2)
returns DataSource for accessing objects of type T that are in first DataSource but not in second.
Definition: operations.hpp:40

SourceForge.net Logo