<commit_before>=====
Usage
=====

Now that a database table and the corresponding PHP model are created, you can
start using Phormium.

Bootstrap
---------

If you installed Phormium via Composer, just include `vendor/autoload.php` in
your application and Phormium will be autoloaded. Afterwards, you have to
configure Phormium using your `configuration file <setup.html>`_.

.. code-block:: php

    require 'vendor/autoload.php';

    Phormium\DB::configure('/path/to/config.json');

Alternatively, if you are not using Composer, Phormium has it's own autoloader:

.. code-block:: php

    require '/path/to/phormium/src/Phormium/Autoloader.php';

    Phormium\Autoloader::register();
    Phormium\DB::configure('/path/to/config.json');

Querying individual records
---------------------------

Fetch a single record by primary key; throws exception if it doesn't exist.

.. code-block:: php

    Person::get(13);

Fetch a single record by primary key; return null if it doesn't exist.

.. code-block:: php

    Person::find(13);

Check if a record exists with the given primary key.

.. code-block:: php

    Person::exists(13);

Also works for composite primary keys:

.. code-block:: php

    Trade::get('2013-01-01', 123);
    Trade::find('2013-01-01', 123);
    Trade::exists('2013-01-01', 123);

Primary key can be given as an array:

.. code-block:: php

    $tradeID = array('2013-01-01', 123);
    Trade::get($tradeID);
    Trade::find($tradeID);
    Trade::exists($tradeID);

Querying multiple records
-------------------------

To fetch all data from a table, run:

.. code-block:: php

    Person::all();

This is shorthand for calling:

.. code-block:: php

    Person::objects()->fetch();

The `objects()` method will return a `QuerySet` object which is used for
querying data, and `fetch()` will form and execute the corresponding SQL query
and return the results as an array of `Person` objects.

Filtering data
--------------

In order to retrieve only selected rows, `QuerySets` can be filtered. Filters
are used to consturct a WHERE clause in the resulting SQL query.

Column filters
~~~~~~~~~~~~~~

.. code-block:: php

    Person::objects()
        ->filter('birthday', '<' '2000-01-01')
        ->fetch();

This kind of filter is called a **column filter** since it acts on a single
column of the SQL statement, and it will result in the following query:

.. code-block:: sql

    SELECT ... FROM person WHERE birthday < ?;

.. note::

    Since Phormium uses `prepared statements`_, the values for each filter are
    given as `?` and are passed in separately when executing the query. This
    prevents any possibility of SQL injection.

.. _prepared statements: http://php.net/manual/en/pdo.prepared-statements.php

Available column filters:

.. code-block:: php

    Person::objects()
        ->filter($column, '=',  $value)
        ->filter($column, '!=', $value)
        ->filter($column, '>',  $value)
        ->filter($column, '>=', $value)
        ->filter($column, '<',  $value)
        ->filter($column, '<=', $value)
        ->filter($column, 'IN', $array)
        ->filter($column, 'NOT IN', $array)
        ->filter($column, 'LIKE', $value)
        ->filter($column, 'ILIKE', $value)  // case insensitive like
        ->filter($column, 'NOT LIKE', $value)
        ->filter($column, 'BETWEEN', array($low, $high))
        ->filter($column, 'IS NULL')
        ->filter($column, 'NOT NULL')

You can also create a column filter using the `Filter::col()` factory method and
pass the resulting ColumnFilter object to `QuerySet::filter()` as a single
argument.

.. code-block:: php

    use Phormium\Filter\Filter;

    $filter = Filter::col('birthday', '<' '2000-01-01');

    Person::objects()
        ->filter($filter)
        ->fetch();

Filters can be chained; chanining multiple filters will AND them

.. code-block:: php

    Person::objects()
        ->filter('birthday', '<', '2000-01-01')
        ->filter('income', '>', 10000)
        ->fetch();

This will create:

.. code-block:: sql

    SELECT ... FROM person WHERE birthday < ? AND income > ?;

Raw filters
~~~~~~~~~~~

Sometimes column filters can be limiting, since they only allow operations
on a single column. **Raw filters** allow usage of custom SQL code in your WHERE
clause. They will pass any given SQL condition into the WHERE clause.

Raw filters can be created by passing a single string into `QuerySet::filter()`.

.. code-block:: php

    Table::objects()
        ->filter("col1 > col2")
        ->fetch();

This will produce:

.. code-block:: sql

    SELECT ... FROM table WHERE col1 > col2;

Raw filters also work with arguments, for prepared queries:

.. code-block:: php

    PriceList::objects()
        ->filter('unit_price * quantity < ?', [100])
        ->fetch();

Which produces:

.. code-block:: sql

    SELECT ... FROM price_list WHERE unit_price * quantity < ?;

Alternative methods of creating raw filters:

.. code-block:: php<commit_msg>Additional warning for raw filters in docs<commit_after>=====
Usage
=====

Now that a database table and the corresponding PHP model are created, you can
start using Phormium.

Bootstrap
---------

If you installed Phormium via Composer, just include `vendor/autoload.php` in
your application and Phormium will be autoloaded. Afterwards, you have to
configure Phormium using your `configuration file <setup.html>`_.

.. code-block:: php

    require 'vendor/autoload.php';

    Phormium\DB::configure('/path/to/config.json');

Alternatively, if you are not using Composer, Phormium has it's own autoloader:

.. code-block:: php

    require '/path/to/phormium/src/Phormium/Autoloader.php';

    Phormium\Autoloader::register();
    Phormium\DB::configure('/path/to/config.json');

Querying individual records
---------------------------

Fetch a single record by primary key; throws exception if it doesn't exist.

.. code-block:: php

    Person::get(13);

Fetch a single record by primary key; return null if it doesn't exist.

.. code-block:: php

    Person::find(13);

Check if a record exists with the given primary key.

.. code-block:: php

    Person::exists(13);

Also works for composite primary keys:

.. code-block:: php

    Trade::get('2013-01-01', 123);
    Trade::find('2013-01-01', 123);
    Trade::exists('2013-01-01', 123);

Primary key can be given as an array:

.. code-block:: php

    $tradeID = array('2013-01-01', 123);
    Trade::get($tradeID);
    Trade::find($tradeID);
    Trade::exists($tradeID);

Querying multiple records
-------------------------

To fetch all data from a table, run:

.. code-block:: php

    Person::all();

This is shorthand for calling:

.. code-block:: php

    Person::objects()->fetch();

The `objects()` method will return a `QuerySet` object which is used for
querying data, and `fetch()` will form and execute the corresponding SQL query
and return the results as an array of `Person` objects.

Filtering data
--------------

In order to retrieve only selected rows, `QuerySets` can be filtered. Filters
are used to consturct a WHERE clause in the resulting SQL query.

Column filters
~~~~~~~~~~~~~~

.. code-block:: php

    Person::objects()
        ->filter('birthday', '<' '2000-01-01')
        ->fetch();

This kind of filter is called a **column filter** since it acts on a single
column of the SQL statement, and it will result in the following query:

.. code-block:: sql

    SELECT ... FROM person WHERE birthday < ?;

.. note::

    Since Phormium uses `prepared statements`_, the values for each filter are
    given as `?` and are passed in separately when executing the query. This
    prevents any possibility of SQL injection.

.. _prepared statements: http://php.net/manual/en/pdo.prepared-statements.php

Available column filters:

.. code-block:: php

    Person::objects()
        ->filter($column, '=',  $value)
        ->filter($column, '!=', $value)
        ->filter($column, '>',  $value)
        ->filter($column, '>=', $value)
        ->filter($column, '<',  $value)
        ->filter($column, '<=', $value)
        ->filter($column, 'IN', $array)
        ->filter($column, 'NOT IN', $array)
        ->filter($column, 'LIKE', $value)
        ->filter($column, 'ILIKE', $value)  // case insensitive like
        ->filter($column, 'NOT LIKE', $value)
        ->filter($column, 'BETWEEN', array($low, $high))
        ->filter($column, 'IS NULL')
        ->filter($column, 'NOT NULL')

You can also create a column filter using the `Filter::col()` factory method and
pass the resulting ColumnFilter object to `QuerySet::filter()` as a single
argument.

.. code-block:: php

    use Phormium\Filter\Filter;

    $filter = Filter::col('birthday', '<' '2000-01-01');

    Person::objects()
        ->filter($filter)
        ->fetch();

Filters can be chained; chanining multiple filters will AND them

.. code-block:: php

    Person::objects()
        ->filter('birthday', '<', '2000-01-01')
        ->filter('income', '>', 10000)
        ->fetch();

This will create:

.. code-block:: sql

    SELECT ... FROM person WHERE birthday < ? AND income > ?;

Raw filters
~~~~~~~~~~~

.. versionadded:: 0.6

Sometimes column filters can be limiting, since they only allow operations
on a single column. **Raw filters** allow usage of custom SQL code in your WHERE
clause. They will pass any given SQL condition into the WHERE clause.

Raw filters can be created by passing a single string into `QuerySet::filter()`.

.. code-block:: php

    Table::objects()
        ->filter("col1 > col2")
        ->fetch();

This will produce:

.. code-block:: sql

    SELECT ... FROM table WHERE col1 > col2;

Raw filters also work with arguments, for prepared queries:

.. code-block:: php

    PriceList::objects()
        ->filter('unit_price * quantity < ?', [100])
        ->fetch();

Which produces:

.. code-block:: sql

    SELECT ... FROM price_list WHERE unit_price * quantity < ?;

.. warning::

    Any string passed in as a raw filter is inserted into the resulting SQL
    query without any validation. This makes it easy to:

    - break a query by passing in invalid SQL
    - create queries which are platform dependent (e.g. by using
      database-specific functions)
    - pass in unvalidated values (use arguments instead)

    Be careful.

Alternative methods of creating raw filters:

.. code-block:: php
{'fraction_non_alphanumeric': 0.15120208554600753, 'fraction_numerical': 0.01680023172733417, 'mean_word_length': 3.017843289371606, 'pattern_counts': {'":': 0, '<': 23, '<?xml version=': 0, '>': 69, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2794788', 'n_tokens_mistral': 3207, 'n_tokens_neox': 2948, 'n_words': 1103}