[[TOC]]
= S3ResourceFilter =

S3ResourceFilter is a class representing the DAL queries and Python filters to select the records for a resource.

Note: class names may still change as this functionality is still under development.
== Overview ==

S3ResourceFilters are a part of S3Resources:

[[Image(rfilter.png)]]

They contain the queries and filters to extract the data from the database.

At the time an S3Resource gets defined, a S3ResourceFilter is automatically added to it. This default filter (so called "master query") covers:

  - available records (i.e. not deleted)
  - accessible records (i.e. access permitted)
  - selected records by record ID and/or UID
  - joined records (e.g. components)

It is possible to extend this filter during the request:

  - by a URL query
  - by the {{{S3Resource.add_filter()}}} method

S3ResourceFilters can filter for both real fields in the database table as well as virtual (computed) fields.

== Design ==

S3ResourceFilters consist of a hierarchical set of web2py Query and S3ResourceQuery objects, where:

  - Query instances represent the DAL queries ("query")
  - S3ResourceQuery instances represent virtual field filters ("vfltr")

It is possible to define S3ResourceQueries even for real fields. When extracting rows from the database table, those parts of a S3ResourceQuery which (only) concern real fields get automatically converted into Query instances and added to the DAL query. Furthermore, where S3ResourceQueries need joins, those get automatically added to the real query as well.

The process for the extraction of rows from the database is as follows:

[[Image(rfilter_process.png)]]

This process supports pagination (slicing), although that would necessarily not only retrieve the requested page from the database, but all records which match the DAL query. Therefore, the virtual filters should always only be the "last mile", while the DAL query reduces the number of candidates to the necessary minimum.

== Field Selectors ==

S3ResourceQuery instances are based on S3FieldSelector, which is a string with the so-called "list_fields syntax":

{{{
alias.{fkey$}field
}}}

  - '''alias''' is the alias of the component without prefix, e.g. "contact" for the pr_contact component (note that components may use aliases different from their table name). For the primary table of a resource (master table), "alias" is just the tablename without prefix.
  - '''fkey''' is the name of a foreign key field in the table specified by ''alias'', both together addressing the table referenced by this foreign key. It is possible to chain foreign key selectors to address tables which are more than one reference level away. Note that only real foreign key fields can be used (i.e. neither integer fields nor virtual fields).
  - '''field''' is the name of the field or a virtual field in the table addressed by ''alias'' (plus ''foreign key'' where present).

To access fields in a table which is linked via a link table, replace the fkey by the link table name and add left and right key if not already specified by add_component:
{{{
alias.{{lkey:}linktable{:rkey}$}field
}}}


  - '''linktable''' is the name of the linktable
  - '''lkey''' is the name of the left foreign key in the linktable, i.e. the foreign key that points to the table specified by alias
  - '''rkey''' is the name of the right foreign key in the linktable, i.e. the foreign key that points to the table containing the field

lkey and rkey ''can'' be omitted if they are not ambiguous, i.e. if there's only one option each.

== URL queries ==

Filters can be specified in URLs simply by adding URL query variables in the form:

{{{
<field selector>__<operator>=<value>
}}}

(Note: these are two underscores between field selector and operator)

The following operators are supported:

||'''Operator'''||'''Meaning'''||'''Comment'''||
||lt||less than||||
||le||less or equal||||
||eq||equal||can be omitted (=default)||
||ne||not equal||||
||ge||greater or equal||||
||gt||greater than||||
||contains||containment||given value belongs to field value (which is a list)||
||belongs||reverse containment||field value belongs to a list of given values||
||like||string containment||case-insensitive||

No negate an operator, simply append a ! to the operator like in:

{{{
?person.first_name__like!=Dominic
}}}

The '''value''' can be a single value, a comma-separated list of values.

Date-time-values must be specified as YYYY-MM-DDThh:mm:ss, dates as YYYY-MM-DD and times as hh:mm:ss. Other types use their standard Python notation. NONE specifies a null-value (undefined, Python None). Values in quotes are treated as strings, i.e.

{{{
?person.gender=NONE
}}}

specifies the value {{{None}}}, whereas

{{{
?person.gender="NONE"
}}}

specifies the string "NONE".

It is currently not possible to escape quotation marks in order to enclose them in strings - thus strings must not contain quotation marks.

For all operators except !__belongs, any comma-separated lists of values are treated as alternatives (OR) ''unless'' the field contains a list as well. All operators except !__like are case-sensitive.

== Extending filters at the back-end ==

S3FieldSelector instances can also be generated at the back-end, simply by:

{{{
selector = S3FieldSelector(<field selector>)
}}}

Such selectors can then be used to create filter queries, using a syntax very similar to web2py queries:

{{{
afilter = S3FieldSelector("person.first_name") == "Dominic"
bfilter = ~(S3FieldSelector("person.first_name").like("Dominic"))  # negation by ~ operator
}}}

This generates '''S3ResourceQuery''' instances.

The equivalent query operators are:

||'''Python Operator'''||'''URL Operator'''||
||{{{<}}}||!__lt||
||{{{<=}}}||!__le||
||{{{==}}}||!__eq||
||{{{!=}}}||!__ne||
||{{{>=}}}||!__ge||
||{{{>}}}||!__gt||
||{{{.like()}}}||!__like||
||{{{.contains()}}}||!__contains||
||{{{.belongs()}}}||!__belongs||

These filter queries can also be joined together by {{{&}}} and {{{|}}} operators:

{{{
cfilter = (S3FieldSelector("person.first_name") == "Dominic") & (S3FieldSelector("contact.value") == "dominic@nursix.org")
}}}

Note: be diligent with brackets here ({{{&}}} and {{{|}}} operators take precedence).

To add a filter to a resource is as simple as:

{{{
myfilter = S3FieldSelector("person.first_name") == "Dominic"
resource.add_filter(myfilter)
}}}

Joins which become necessary for filters will be added automatically for S3ResourceQuery instances.

You can though also specify a standard web2py Query for add_filter, in which case you would have to specify the respective join manually. If you specify a component alias as "c" parameter, the join for this component would be added automatically.

{{{
myfilter = db.pr_contact.value == "dominic@nursix.org"
resource.add_filter(myfilter, c="contact")
}}}

Note that S3ResourceQuery instances are resource-agnostic, so they can be re-used across resources (provided the same field selector applies in all of them).

S3ResourceQueries for fields which are not defined in a resource will be ignored quietly when retrieving records from the resource.