DAO DSL

Sometimes using array params is not enough. In those cases, UES has a dsl to build the statement in pure PHP.

Why?

The motivation for building a PHP dsl for select statements can be summed up into a single reason: meta information.

Because UES provides dynamic meta table linking (convention over configuration), filtering records based on meta information would be a nightmarish SQL statement at its best.

Recognized Words

All of the recognized words are conveniently placed at the top of classes/dao/filter.php.

Using Joins

Currently, only the inner join is supported. For more complexity, stick with using the by_sql method for retrieval.

The sql builder maintains a list of join aliases that can be used to buil rather complex join with pure PHP.

<?php

// Law instructors whose first name begins with J with a fake meta of salary greater than $30,000
$law_teachers = ues_user::get_all(ues::where()
  ->join(ues_teacher::tablename(' '), 'teach')->on('id', 'userid')
  ->join(ues_section::tablename(' '), 'sec')->on('teach.sectionid', 'id')
  ->join(ues_course::tablename(' '), 'cou')->on('sec.courseid', 'id')
  ->cou->department->equal('LAW')
  ->teach->status->in(ues::ENROLLED, ues::PROCESSED)
  ->sec->status->equal(ues::MANIFESTED)
  ->firstname->starts_with('J')
  ->user_salary->greater(30000))
);

The above dsl would expand into:

SELECT z.* FROM
  {enrol_ues_user} z,
  {enrol_ues_usermeta} a,
  {enrol_ues_teachers} teach,
  {enrol_ues_sections} sec,
  {enrol_ues_courses} cou
WHERE (z.id = teach.userid)
  AND (teach.sectionid = sec.id)
  AND (sec.courseid = cou.id)
  AND (cou.department = 'LAW')
  AND (teach.status IN ('processed', 'enrolled'))
  AND (sec.status IN ('manifested'))
  AND (z.firstname LIKE 'J%')
  AND (a.userid = z.id)
  AND (a.name = 'user_salary')
  AND (a.value > 30000)

The query expand a lot when meta information is added. The underlying framework expands the meta information almost as if it directly stored on the user table.

Examples

Sometimes it's best just to see the DSL in action. In this example I'm going to filter records based on this information: firstnames starts with P, lastnames ends with J, the number of years in the institute is either 1, 4, 6, and they possess a clicker id.

The entry point to every standard dsl behavior is ues::where().

<?php
$filters = ues::where()
  ->firstname->starts_with('P')
  ->lastname->ends_with('J')
  ->user_year->in(1, 4, 6)
  ->user_keypadid->not_equal('')

$users = ues_user::get_all($filters);

The fields prefaces with user_ are designated meta fields. The API takes advantage of PHP magic methods __get and __call to provide pure PHP DSL through method chaining.

More Explicit

Some camps in PHP do not appreciate this form of dynamics. The DSL allows for explicit method invocation:

<?php
$filters = ues::where('firstname')->starts_with('P')
  ->plus('lastname')->ends_with('J')
  ->plus('user_year')->in(1, 4, 6)
  ->plus('user_keypadid')->not_equal('')

$users = ues_user::get_all($filters);

This pattern of filter building is more explicit, and reduces the need to invoke an __get calls and all the dsl methods are directly delegated to a build who defines these methods.

Making your own words

Note that using any word that is not mentioned above cause big problems:

<?php
// This will throw an exception
$filters->plus('idnumber')->contains('1234');

Adding your own dsl words is as simply as:

1. creating your own dsl field who knows these words, and
2. providing a builder for your custom dsl field

<?php
// I know "contains"!
class custom_field extends ues_dao_field {
    public function contains($value) {
        return $this->like($value);
    }
}

// I know about your custom dsl!
class custom_dsl extends ues_dao_filter_builder {
    public function create_filter($field) {
        return new custom_field($field);
    }
}

// In action
$filters = new custom_dsl();

$users = ues_user::get_all($filters->idnumber->contains('1234'));