libSmalldb  v0.7-57-g12e9bf1
Public Member Functions | Protected Member Functions | Protected Attributes | List of all members
FlupdoGenericListing Class Reference

Description

A very generic listing based on Flupdo::SelectBuilder.

Default Filter Syntax

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.

Note
Please keep in mind that there is '=' character in URL between filter name and value. For example '<' filter looks like ...?property<=value.
Warning
Filters are in fact simple key-value structure. Conditions described below are only an ilusion. All this only means that for each state machine property a group of filters is generated (on demand).

Conditions:

Predefined filters:

Example:

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',
)

Custom filters

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:

Note
Operators of default filters have nothing to do with custom filters.

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.

Example

"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:

Note
Additional properties may be added to filter definition. It is expected that GUI will be generated from this definition as well.
Warning
Do not use GROUP BY, it will break item count calculation. Use sub-selects instead.

Inheritance diagram for FlupdoGenericListing

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...
 
 getQueryFilters ()
 Get original filters without unknown filters. 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

- Public Attributes inherited from IListing
const IGNORE_UNKNOWN_FILTERS = 0x0100
 Filtering flag: If there are unknown filters, no exception will be thrown by constructor. More...
 

Constructor & Destructor Documentation

◆ __construct()

__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.

Parameters
$machineState machine implementation to inspect.
$query_builderQuery builder where filters will be added.
$sphinxFlupdo instance connected to Sphinx index (optional)
$query_filtersRequested filters to add to $query_builder.
$machine_tableThe primary table to query.
$machine_filtersCustom filter definitions (how things should be filtered, not filtering itself).
$machine_propertiesState machine properties definitions.
$machine_referencesState machine references to other state machines.
$additional_filters_dataAdditional filter data source definition (these are evaluated and added to processed filters).
$state_selectSQL expression to get state column.
$filtering_flagsAdditional options how to handle filters.

Member Function Documentation

◆ getQueryBuilder()

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()

query ( )

Execute SQL query or do whatever is required to get this listing populated.

Implements IListing.

◆ describeProperties()

describeProperties ( )

Get description of all properties (columns) in the listing.

Implements IListing.

◆ fetchAll()

fetchAll ( )

Returns an array of all items in the listing.

This decodes properties.

Implements IListing.

◆ getProcessedFilters()

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.

◆ getQueryFilters()

getQueryFilters ( )

Get original filters without unknown filters.

◆ getUnknownFilters()

getUnknownFilters ( )

Return unknown filters, subset of $query_filters.

◆ calculateAdditionalFiltersData()

calculateAdditionalFiltersData ( $filters)
protected

Calculate additional filter data.

Parameters
$filtersFilters to populate with additional data.
Returns
Nothing.

◆ setupSphinxSearch()

setupSphinxSearch (   $filter_name,
  $value,
  $machine_filter 
)
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.

Member Data Documentation

◆ $machine

$machine
protected

Parent state machine, which created this listing.

◆ $query

$query
protected

SQL query to execute.

◆ $result

$result
protected

PDOStatement, result of the query.

◆ $query_filters

$query_filters
protected

Actual filters.

◆ $additional_filters_data

$additional_filters_data
protected

Additional filter data source definition (these are evaluated and added to processed filters).

◆ $state_select

$state_select
protected

SQL expression to select machine state.

◆ $before_query

$before_query = array()
protected

List of callables to be called just before the query is executed.

◆ $after_query

$after_query = array()
protected

List of callables to be called in destructor, only if the query has been executed.

◆ $unknown_filters

$unknown_filters = array()
protected

Unknown filters - subset of $query_filters.