libSmalldb
v0.6
|
A very generic listing based on Flupdo::SelectBuilder.
Syntax is designed for use in query part of URL. First, filter name is looked up in statemachine filters. If not found, filter name is considered a property name and if matches, equality condition is added. Otherwise if operator is detected at the end of property name, given condition is added.
...?property<=value
.!
means "not equal".<
means "lesser or equal".<<
means "less than".>
means "greater or equal".>>
means "greater than".:
is range check. The value must be in format min..max
or min...max
. Two dots means min <= x < max. Three or more dots include max (min <= x <= max; using BETWEEN operator). The value may be also an array of two elements or with 'min' and 'max' keys.~
is REGEXP operator.%
is LIKE operator.!:
, !~
and !%
are negated variants of previous three operators.limit
(int; default = 100)offset
(int; default = undefined)order_by
(string; comma separated list of properties/column names; property prefixed with minus is sorted in oposite order; default = undefined) - order_asc
(bool; default = true; applied only when order_by is set)order-by
(alias to order_by
)order-asc
(alias to order_asc
, but only if order-by
is used)Select all items, where foo
is greater or equal to 5, and bar
is between 10 and 20 (excluding 20), and category
is 'fruit'.
http://example.com/items?foo>=5&bar:=10..20&category=fruit $filter = array( 'foo>' => 5, 'bar:' => array(10, 20), 'category' => 'fruit', )
FlupdoMachine machine can have filters option set. This option defines custom filters. Custom filters will override predefined filters of the same name.
Each filter is list of statements added to Flupdo::SelectBuilder. Each filter must have these properties defined:
stmt
: Name of SelectBuilder method which adds the statement.sql
: Raw SQL code with positional parameters (question marks).params
: Filter names wich values will be passed as query parameters. These must be in the same order as in sql
.Filter can be defined as a simple filter using query
property, or as value-dependent filter using query_map
property. The query_map
will select one of specified filters by filter value. The query
is used when query_map
is missing or no value is matched.
"filters": { "path": { "query": [ { "stmt": "where", "sql": "`path_mask` = ? OR `path_mask` = \"*\" OR ? REGEXP CONCAT(\"^\", REPLACE(`path_mask`, \"*\", \"[^/]+\"), \"$\")", "params": [ "path", "path" ] } ] }, "date": { "query_map": { "past": [ { "stmt": "where", "sql": "mtime < NOW()", "params": [ ] } ], "future": [ { "stmt": "where", "sql": "mtime >= NOW()", "params": [ ] } ] }, "query": [ { "stmt": "where", "sql": "mtime BETWEEN NOW() - INTEVAL ? DAY AND NOW + INTERVAL ? DAY", "params": [ "date", "date" ] } ] } }
Possible use:
http://example.com/item?date=past&path=/foo/bar
– select items modified in past and matching given path.http://example.com/item?date=2
– select items modified within +/- 2 days from now.Public Member Functions | |
__construct (AbstractMachine $machine,\Smalldb::Flupdo::SelectBuilder $query_builder,\Smalldb::Flupdo::IFlupdo $sphinx=null, $query_filters, $machine_table, $machine_filters, $machine_properties, $machine_references, $additional_filters_data, $state_select, $filtering_flags=0) | |
Prepare query builder. More... | |
getQueryBuilder () | |
Get raw query builder. More... | |
query () | |
Execute SQL query or do whatever is required to get this listing populated. More... | |
describeProperties () | |
Get description of all properties (columns) in the listing. More... | |
fetchAll () | |
Returns an array of all items in the listing. More... | |
getProcessedFilters () | |
Get filter configuration (processed and filled with pagination data). More... | |
getUnknownFilters () | |
Return unknown filters, subset of $query_filters. More... | |
Protected Member Functions | |
calculateAdditionalFiltersData (&$filters) | |
Calculate additional filter data. More... | |
setupSphinxSearch ($filter_name, $value, $machine_filter) | |
Setup query for lookup in Sphinx search engine. More... | |
Protected Attributes | |
$machine | |
Parent state machine, which created this listing. More... | |
$query | |
SQL query to execute. More... | |
$result | |
PDOStatement, result of the query. More... | |
$query_filters | |
Actual filters. More... | |
$additional_filters_data | |
Additional filter data source definition (these are evaluated and added to processed filters). More... | |
$state_select | |
SQL expression to select machine state. More... | |
$before_query = array() | |
List of callables to be called just before the query is executed. More... | |
$after_query = array() | |
List of callables to be called in destructor, only if the query has been executed. More... | |
$unknown_filters = array() | |
Unknown filters - subset of $query_filters. More... | |
Additional Inherited Members | |
![]() | |
const | IGNORE_UNKNOWN_FILTERS = 0x0100 |
Filtering flag: If there are unknown filters, no exception will be thrown by constructor. More... | |
__construct | ( | AbstractMachine | $machine, |
\Smalldb::Flupdo::SelectBuilder | $query_builder, | ||
\Smalldb::Flupdo::IFlupdo | $sphinx = null , |
||
$query_filters, | |||
$machine_table, | |||
$machine_filters, | |||
$machine_properties, | |||
$machine_references, | |||
$additional_filters_data, | |||
$state_select, | |||
$filtering_flags = 0 |
|||
) |
Prepare query builder.
$machine | State machine implementation to inspect. |
$query_builder | Query builder where filters will be added. |
$sphinx | Flupdo instance connected to Sphinx index (optional) |
$query_filters | Requested filters to add to $query_builder. |
$machine_table | The primary table to query. |
$machine_filters | Custom filter definitions (how things should be filtered, not filtering itself). |
$machine_properties | State machine properties definitions. |
$machine_references | State machine references to other state machines. |
$additional_filters_data | Additional filter data source definition (these are evaluated and added to processed filters). |
$state_select | SQL expression to get state column. |
$filtering_flags | Additional options how to handle filters. |
getQueryBuilder | ( | ) |
Get raw query builder.
It may be useful to configure query outside of this listing, but it is ugly. It is also specific to FlupdoGenericListing only.
query | ( | ) |
Execute SQL query or do whatever is required to get this listing populated.
Implements IListing.
describeProperties | ( | ) |
Get description of all properties (columns) in the listing.
Implements IListing.
fetchAll | ( | ) |
getProcessedFilters | ( | ) |
Get filter configuration (processed and filled with pagination data).
This method should be called after query(), otherwise it will not contain total_count
.
The total_count
is calculated using second query, same as the primary query, but with COUNT(*)
in SELECT
clause, and limit & offset removed (everything else is preserved). This is a few orders of magnitude faster than SQL_CALC_FOUND_ROWS.
Implements IListing.
getUnknownFilters | ( | ) |
Return unknown filters, subset of $query_filters.
|
protected |
Calculate additional filter data.
$filters | Filters to populate with additional data. |
|
protected |
Setup query for lookup in Sphinx search engine.
Since Sphinx is accessed using standalone MySQL connection, this will create temporary table and populate it with the fulltext search result. Then the temporary table will be used to access and filter the original query.
|
protected |
Parent state machine, which created this listing.
|
protected |
SQL query to execute.
|
protected |
PDOStatement, result of the query.
|
protected |
Actual filters.
|
protected |
Additional filter data source definition (these are evaluated and added to processed filters).
|
protected |
SQL expression to select machine state.
|
protected |
List of callables to be called just before the query is executed.
|
protected |
List of callables to be called in destructor, only if the query has been executed.
|
protected |
Unknown filters - subset of $query_filters.