EZOQL stands for EZPDO Object Query Language. EZOQL is a mini query language that resembles the standard SQL SELECT statements. It provides great flexibility in object retrieval.

Despite the resemblance to standard SQL statements, please keep in mind EZOQL is not your ordinary SQL. EZOQL is fully object-orientated. It understands notions like inheritance, polymorphism and object relationships.

You can find the EZOQL syntax in rigorous Backus Normal Form (BNF) here, but you can safely skip it. If you are familiar with the standard SQL SELECT statement, it probably takes no time to for you to understand EZOQL. Even if you don’t, the following examples should easily get you started.

EZOQL allows you to do queries using the keywords from and where. Please note that EZOQL keywords are case-insensitive. So ‘from’ is the same as ‘FROM’. The following are examples of simple queries. Notice the use of ‘?’ as the placeholder for parameters passing into queries.

// get EZPDO manager 
$m = epManager::instance();
 
// find books by exact title
$books = $m->find("from Book as b where b.title = ?", $title);
 
// find books by the wild card (use LIKE)
$books = $m->find("from Book as b where b.title like 'Intro%'");
 
// find books who title is set to null
$books = $m->find("from Book as b where b.title is null");
 
// find books that has less than 100 pages
$books = $m->find("from Book as b where b.pages < ?", $pages);
 
// combination of search expressions
$books = $m->find("from Book as b where b.title like ? and b.pages < ?", $title, $pages);

Starting from EZPDO 1.1.61), the in operator is supported in EZOQL. Here is a simple use.

// find books of which the prices is either $2.50 or $100.01
$books = $m->find("from Book as b where b.price in (2.50, 100.01)");
// find books authored by either Joe or Jane
$books = $m->find("from Book as b where b.author.name in ('Joe Smith', 'Jane Smith')");

Alternatively, you could pass an array as the parameter for the in operator.

// find books of which the prices is either $2.50 or $100.01
$books = $m->find("from Book as b where b.price in (?)", array(2.50, 100.01));
// find books authored by either Joe or Jane
$books = $m->find("from Book as b where b.author.name in (?)", array('Joe Smith', 'Jane Smith'));

The power of EZOQL dose not stop at finding objects. It can also help you sort (order by … asc|desc) and limit your search results.

// find books and sort by book id (default ascending order)
$books = $m->find("from Book as b where b.title like ? order by b.id", $title);
 
// find books and sort by id in ascending order
$books = $m->find("from Book as b where b.title like ? order by b.id asc", $title);
 
// find books and sort by id in desscending order
$books = $m->find("from Book as b where b.title like ? order by b.id desc", $title);
 
// find books and sort by id in desscending order and limit to the first two only
$books = $m->find("from Book as b where b.title like ? order by b.id desc limit 0, 2", $title);

You can also sort by multiple keys and orders. For example,

// find books and sort by id (ascending) and title (descending)
$books = $m->find("from Book as b where b.title like ? order by b.uuid asc, b.title desc", $title);

Sometimes order a set of returned objects randomly is useful. With EZOQL, You can use order by random(). Here is an example,

// find books with its matching a pattern in a random order
$books = $m->find("from Book as b where b.title like ? order by random()", $title);

One may challenge the necessity of using the aliases (i.e. the as clauses). They are indeed not required. The following three statements work exactly the same way.

$books = $m->find("from Book as b where b.title like ? order by b.uuid desc", $title);
$books = $m->find("from Book where Book.title like ? order by Book.uuid desc", $title);
$books = $m->find("from Book where title like ? order by uuid desc", $title);

Starting from EZPDO 1.1.02), aggregate functions are supported in EZOQL. The supported aggregate functions in EZOQL are

• AVG(),
• COUNT(),
• MAX(),
• MIN(), and
• SUM().

The importance of the functions is obvious. Without them, if you want to compute the average of an integer or float variable in a class, you would have to fetch all the objects in the class. Retrieving all objects in a class (imagine all rows in a large table) is an expensive operation that normally you don’t want to do. With these functions the computation is delegated to the databases.

Example: Say you want to know what is the average cost per page and average cost per book for all PHP books, do this.

$cost = $m->find("sum(price) from Book where title like '%PHP%'"); 
$num_pages = $m->find("sum(pages) from Book where title like '%PHP%'");
$num_books = $m->find("count(*) from Book where title like '%PHP%'");
$cost_per_page = $cost/$num_pages;
$cost_per_book = $cost/$num_books;

With the advent of EZPDO 1.1.03) comes the new EZOQL engine that allows you to do more advanced queries.

One of the biggest advantages that EZPDO has over other ORM packages is its capability of handling object relationships without involving you manually creating and maintaining relationships among database tables. Instead you can simply use @orm has|composed_of one|many to define your relationship variables in the class and EZPDO takes care of the creation and maintenance of relationship tables automatically.

EZOQL also provides an easy way for you to navigate through the relationship variables defined in your classes. And this is where EZOQL differs greatly from regular SQL statements. The following examples will make this clear.

Using a single-valued relationship var in EZOQL is the same as using a primitive var like we have done in the above examples. For example, if you want to use zipcode in the Contact object associated to an Author object to find all authors within the same zipcode, you can fire a query like this.

// find all authors within the same zip code
$authors = $m->find("from Author as a where a.contact.zipcode = '12345'); 

The variable ‘a.contact.zipcode’ involves one object relationship, an Author has one Contact. You can access variable more than one levels from the original object/class in your data model. For example,

// find all authors living on Walnut street
$authors = $m->find(
  "from Authors as a where a.contact.address.street LIKE '%Walnut Street%'");

Two object relationships are involved in ‘a.contact.address.street: an Author has one Contact and a Contact has an Address. Sometimes it is handy to use an associative array (or an example object) to specify the search criteria. You can use the placeholder (?) and write the query like this,

// find all authors living on Walnut street
$authors = $m->find(
  "from Authors as a where a.contact = ?", 
  array('address' => 
    array('street' => '100 Walnut Street') 
  ));

To use an example object for search, the query can be written as

// create an example object
$contact = $m->create('Contact');
$contact->address = $m->create('Address');
$contact->address->street = '100 Walnut Street';
 
// find all authors living on Walnut street
$authors = $m->find("from Authors as a where a.contact = ?", $contact);

To use a many-valued relationship var in query, you can use contains(). We give some examples.

Example-1: Say you want to find a book that is written by an author whose name is “Smith”. Keep in mind that a book can have more than one authors so authors is a many-valued relationship var in class Book. You can do a query like this.

$books = $m->find("from Book as b where b.authors.contains(a) and a.name = 'Smith'");

The contains() function following the many-valued variable (authors) has an alias (a) of class Author as its argument. The following condition, “a.name = ‘Smith’”, specifies the selection condition for the alias (a).

Example-2: You can get the same result by passing an array argument to the query,

$books = $m->find(
  "from Book as b where b.authors.contains(?)", array('name' => 'Smith'));

or by passing an example object,

$author = $m->create('Author');
$author->name = 'Smith';
$books = $m->find("from Book as b where b.authors.contains(?)", $author);