Criteria

How to filter records based on user input or pre-defined criteria.

You can specify criteria using the and and or methods, and group them using orStart, orEnd, andStart, and andEnd methods. So the following query:

$query = QueryBuilder::create()
    ->orStart()
        ->where('firstName', '=', 'Marty')
        ->and('lastName', '=', 'McFly')
    ->orEnd()
    ->orStart()
        ->where('firstName', '=', 'Emmet')
        ->and('lastName', '=', 'Brown')
    ->orEnd()
    ->or('car', '=', 'DeLorean')
    ->andStart()
        ->where('year', '=', 1985)
        ->or('year', '=', 1955)
    ->andEnd()
    ->buildSelectQuery();

...would yield an SQL query that looks something like this (it would be a bit different, as it would fully resolve column names, and use a prepared statement rather than the actual values, but this is just to illustrate the principle):

SELECT * FROM `person` 
WHERE (
    `first_name` = 'Marty'
    AND `last_name` = 'McFly'
)
OR (
    `first_name` = 'Emmet'
    AND `last_name` = 'Brown'
)
OR `car` = 'DeLorean'
AND (
    `year` = 1985
    OR `year` = 1955
);    

Often, the values to use in the criteria will come from a POST array or JSON object as a simple flat array of key/value pairs. You can pass an array of values to the buildSelectQuery method (for a select query - or the equivalent method if using an update or delete query) to whitelist criteria and match up aliases with entity properties within your structured criteria.

Suppose you have an API endpoint that takes in an array of criteria to search on, which looks like this:

$postedValues = [
    'surname'   => 'smith', //Case insensitive
    'postcode'  => 'PE389QP',
    'email'     => 'peter@',
    'random'    => 'This should be ignored!'
];

Now we want to use those values as our search criteria, but the values we have received do not match exactly with the criteria we want to pass to Objectiphy.

• The parameter names don't exactly match the entity property names.

• There is some extra data that we want to ignore.

• We want to use a LIKE operator on the surname to do a partial match and find surnames that start with the specified value.

• We want to use a LIKE operator on the email address to do a partial match and find e-mail addresses which contain the value anywhere within the string.

• We want to find records where either the surname matches, or both the postcode and email address match.

Not a very realistic example, but it is just for illustration purposes! Here is how we can use the QueryBuilder to solve these problems:

$query = QueryBuilder::create()
    ->where('contact.lastName', QB::BEGINS_WITH, ':surname')
    ->orStart()
        ->where('postcode', QB::EQUALS, ':postcode') 
        ->and('email', QB::CONTAINS, ':email')
    ->orEnd()
    ->buildSelectQuery($postedValues);

The colon prefix on the values passed in tell the QueryBuilder that it is dealing with an alias - it will then get the actual value from the array of parameters passed to the buildSelectQuery method, using the alias supplied as the key. If you know some or all of the values up-front (ie. you want to hard-code the values to filter on, or get them from somewhere other than an array of user input), you can specify the value at the time you create the criteria. For example:

$query = QueryBuilder::create()
    ->where('contact.lastName', QB::BEGINS_WITH, 'Mac')
    ->orStart()
        ->where('postcode', QB::EQUALS, 'PE3 8AF')
        ->and('email', QB::CONTAINS, 'info')
    ->orEnd()
    ->buildSelectQuery();

Note that even if you supply hard-coded values as shown in the above example, Objectiphy will still use a prepared statement to execute the query on the database, passing the values you specified as parameters, so there is no risk of SQL injection. You can also use a mixture of aliases and values - just use a colon prefix to specify an alias, and pass the value in via an array of values to the build method. If required, you can call the build method later, for example:

$queryBuilder = QB::create()
    ->where('contact.lastName', QB::BEGINS_WITH, ':surname')
    ->orStart()
        ->where('postcode', QB::EQUALS, 'PE3 8AF')
        ->and('email', QB::CONTAINS, ':email')
    ->orEnd();

//...do something else here to populate $postedValues

$query = $queryBuilder->buildSelectQuery($postedValues);

You can also refer back to another property in your object hierarchy for the value instead of supplying the value yourself. To do that, simply pass in the property surrounded by percent signs as the value. For example:

$query = QB::create()
    ->where('contact.lastName', QB::BEGINS_WITH, 'Mac')
    ->orStart()
        ->where('postcode', QB::EQUALS, 'PE3 8AF')
        ->and('email', QB::CONTAINS, 'info')
    ->orEnd()
    ->and('contact.loginId', QB::EQUALS, '%login.id%')
    ->buildSelectQuery();

In the above example, we are assuming that our parent entity has a property called contact which is a child object that has a property called loginId and our parent entity also has a property called login, which is a child object that has a property called id, and this criteria is saying we will only return a record if the values of those two properties match.