Idiorm Documentation

Jamie Matthews and Simon Holywell

May 31, 2018

Contents

1 Philosophy 3

2 Installation 5

2.1 Packagist . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5

2.2 Download . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5

3 Conﬁguration 7

3.1 Setup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7

3.2 Conﬁguration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8

4 Querying 13

4.1 A note on PSR-1 and camelCase . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13

4.2 Single records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14

4.3 Multiple records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14

4.4 Counting results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16

4.5 Filtering results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16

4.6 Grouping . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20

4.7 Having . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20

4.8 Result columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20

4.9 DISTINCT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22

4.10 Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22

4.11 Aggregate functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24

4.12 Raw queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24

5 Models 27

5.1 Getting data from objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27

5.2 Updating records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28

5.3 Creating new records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28

5.4 Checking whether a property has been modiﬁed . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29

5.5 Deleting records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29

6 Transactions 31

7 Multiple Connections 33

7.1 Supported Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33

8 Indices and tables 35

Idiorm Documentation

Contents:

Contents 1

Idiorm Documentation

2 Contents

CHAPTER 1

Philosophy

The Pareto Principle states that roughly 80% of the effects come from 20% of the causes. In software development

terms, this could be translated into something along the lines of 80% of the results come from 20% of the complexity.

In other words, you can get pretty far by being pretty stupid.

Idiorm is deliberately simple. Where other ORMs consist of dozens of classes with complex inheritance hierarchies,

Idiorm has only one class, ORM, which functions as both a ﬂuent SELECT query API and a simple CRUD model class.

If my hunch is correct, this should be quite enough for many real-world applications. Let’s face it: most of us aren’t

building Facebook. We’re working on small-to-medium-sized projects, where the emphasis is on simplicity and rapid

development rather than inﬁnite ﬂexibility and features.

You might think of Idiorm as a micro-ORM. It could, perhaps, be “the tie to go along with Slim’s tux” (to borrow a

turn of phrase from DocumentCloud). Or it could be an effective bit of spring cleaning for one of those horrendous

SQL-littered legacy PHP apps you have to support.

Idiorm might also provide a good base upon which to build higher-level, more complex database abstractions. For

example, Paris is an implementation of the Active Record pattern built on top of Idiorm.

Idiorm Documentation

4 Chapter 1. Philosophy

CHAPTER 2

Installation

2.1 Packagist

This library is available through Packagist with the vendor and package identiﬁer of j4mie/idiorm

Please see the Packagist documentation for further information.

2.2 Download

You can clone the git repository, download idiorm.php or a release tag and then drop the idiorm.php ﬁle in the ven-

dors/3rd party/libs directory of your project.

Idiorm Documentation

6 Chapter 2. Installation

CHAPTER 3

Conﬁguration

The ﬁrst thing you need to know about Idiorm is that you don’t need to deﬁne any model classes to use it. With almost

every other ORM, the ﬁrst thing to do is set up your models and map them to database tables (through conﬁguration

variables, XML ﬁles or similar). With Idiorm, you can start using the ORM straight away.

3.1 Setup

First, require the Idiorm source ﬁle:

<?php

require_once 'idiorm.php';

Then, pass a Data Source Name connection string to the configure method of the ORM class. This is used by PDO

to connect to your database. For more information, see the PDO documentation.

<?php

ORM::configure('sqlite:./example.db');

You may also need to pass a username and password to your database driver, using the username and password

conﬁguration options. For example, if you are using MySQL:

<?php

ORM::configure('mysql:host=localhost;dbname=my_database');

ORM::configure('username', 'database_user');

ORM::configure('password', 'top_secret');

Also see “Conﬁguration” section below.

Idiorm Documentation

3.2 Conﬁguration

Other than setting the DSN string for the database connection (see above), the configure method can be used to set

some other simple options on the ORM class. Modifying settings involves passing a key/value pair to the configure

method, representing the setting you wish to modify and the value you wish to set it to.

<?php

ORM::configure('setting_name', 'value_for_setting');

A shortcut is provided to allow passing multiple key/value pairs at once.

<?php

ORM::configure(array(

'setting_name_1' => 'value_for_setting_1',

'setting_name_2' => 'value_for_setting_2',

'etc' => 'etc'

));

Use the get_config method to read current settings.

<?php

$isLoggingEnabled = ORM::get_config('logging');

ORM::configure('logging', false);

// some crazy loop we don't want to log

ORM::configure('logging', $isLoggingEnabled);

3.2.1 Database authentication details

Settings: username and password

Some database adapters (such as MySQL) require a username and password to be supplied separately to the DSN

string. These settings allow you to provide these values. A typical MySQL connection setup might look like this:

<?php

ORM::configure('mysql:host=localhost;dbname=my_database');

ORM::configure('username', 'database_user');

ORM::configure('password', 'top_secret');

Or you can combine the connection setup into a single line using the conﬁguration array shortcut:

<?php

ORM::configure(array(

'connection_string' => 'mysql:host=localhost;dbname=my_database',

'username' => 'database_user',

'password' => 'top_secret'

));

3.2.2 Result sets

Setting: return_result_sets

Collections of results can be returned as an array (default) or as a result set. See the ﬁnd_result_set() documentation

for more information.

8 Chapter 3. Conﬁguration

Idiorm Documentation

<?php

ORM::configure('return_result_sets', true); // returns result sets

Note: It is recommended that you setup your projects to use result sets as they are more ﬂexible.

3.2.3 PDO Driver Options

Setting: driver_options

Some database adapters require (or allow) an array of driver-speciﬁc conﬁguration options. This setting allows you to

pass these options through to the PDO constructor. For more information, see the PDO documentation. For example,

to force the MySQL driver to use UTF-8 for the connection:

<?php

ORM::configure('driver_options', array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8

˓→'));

3.2.4 PDO Error Mode

Setting: error_mode

This can be used to set the PDO::ATTR_ERRMODE setting on the database connection class used by Idiorm. It should

be passed one of the class constants deﬁned by PDO. For example:

<?php

ORM::configure('error_mode', PDO::ERRMODE_WARNING);

The default setting is PDO::ERRMODE_EXCEPTION. For full details of the error modes available, see the PDO set

attribute documentation.

3.2.5 PDO object access

Should it ever be necessary, the PDO object used by Idiorm may be accessed directly through ORM::get_db(), or

set directly via ORM::set_db(). This should be an unusual occurance.

After a statement has been executed by any means, such as ::save() or ::raw_execute(), the

PDOStatement instance used may be accessed via ORM::get_last_statement(). This may be use-

ful in order to access PDOStatement::errorCode(), if PDO exceptions are turned off, or to access the

PDOStatement::rowCount() method, which returns differing results based on the underlying database. For

more information, see the PDOStatement documentation.

3.2.6 Identiﬁer quote character

Setting: identifier_quote_character

Set the character used to quote identiﬁers (eg table name, column name). If this is not set, it will be autodetected based

on the database driver being used by PDO.

3.2. Conﬁguration 9

Idiorm Documentation

3.2.7 ID Column

By default, the ORM assumes that all your tables have a primary key column called id. There are two ways to override

this: for all tables in the database, or on a per-table basis.

Setting: id_column

This setting is used to conﬁgure the name of the primary key column for all tables. If your ID column is called

primary_key, use:

<?php

ORM::configure('id_column', 'primary_key');

You can specify a compound primary key using an array:

<?php

ORM::configure('id_column', array('pk_1', 'pk_2'));

Note: If you use a auto-increment column in the compound primary key then it should be the ﬁrst one deﬁned into the

array.

Setting: id_column_overrides

This setting is used to specify the primary key column name for each table separately. It takes an associative array

mapping table names to column names. If, for example, your ID column names include the name of the table, you can

use the following conﬁguration:

<?php

ORM::configure('id_column_overrides', array(

'person' => 'person_id',

'role' => 'role_id',

));

As with id_column setting, you can specify a compound primary key using an array.

3.2.8 Limit clause style

Setting: limit_clause_style

You can specify the limit clause style in the conﬁguration. This is to facilitate a MS SQL style limit clause that uses

the TOP syntax.

Acceptable values are ORM::LIMIT_STYLE_TOP_N and ORM::LIMIT_STYLE_LIMIT.

Note: If the PDO driver you are using is one of sqlsrv, dblib or mssql then Idiorm will automatically select the

ORM::LIMIT_STYLE_TOP_N for you unless you override the setting.

3.2.9 Query logging

Setting: logging

Idiorm can log all queries it executes. To enable query logging, set the logging option to true (it is false by

default).

When query logging is enabled, you can use two static methods to access the log. ORM::get_last_query()

returns the most recent query executed. ORM::get_query_log() returns an array of all queries executed.

10 Chapter 3. Conﬁguration

Idiorm Documentation

Note: The code that does the query log is an approximation of that provided by PDO/the database (see the Idiorm

source code for detail). The actual query isn’t even available to idiorm to log as the database/PDO handles the binding

outside of idiorm’s reach and doesn’t pass it back.

This means that you might come across some inconsistencies between what is logged and what is actually run. In

these case you’ll need to look at the query log provided by your database vendor (eg. MySQL).

3.2.10 Query logger

Setting: logger

Note: You must enable logging for this setting to have any effect.

It is possible to supply a callable to this conﬁguration setting, which will be executed for every query that idiorm

executes. In PHP a callable is anything that can be executed as if it were a function. Most commonly this will take

the form of a anonymous function.

This setting is useful if you wish to log queries with an external library as it allows you too whatever you would like

from inside the callback function.

<?php

ORM::configure('logger', function($log_string, $query_time) {

echo $log_string . ' in ' . $query_time;

});

3.2.11 Query caching

Setting: caching

Idiorm can cache the queries it executes during a request. To enable query caching, set the caching option to true

(it is false by default).

<?php

ORM::configure('caching', true);

Setting: caching_auto_clear

Idiorm’s cache is never cleared by default. If you wish to automatically clear it on save, set caching_auto_clear

to true

<?php

ORM::configure('caching_auto_clear', true);

When query caching is enabled, Idiorm will cache the results of every SELECT query it executes. If Idiorm encounters

a query that has already been run, it will fetch the results directly from its cache and not perform a database query.

Warnings and gotchas

• Note that this is an in-memory cache that only persists data for the duration of a single request. This is not a

replacement for a persistent cache such as Memcached.

3.2. Conﬁguration 11

Idiorm Documentation

• Idiorm’s cache is very simple, and does not attempt to invalidate itself when data changes. This means that if

you run a query to retrieve some data, modify and save it, and then run the same query again, the results will be

stale (ie, they will not reﬂect your modiﬁcations). This could potentially cause subtle bugs in your application.

If you have caching enabled and you are experiencing odd behaviour, disable it and try again. If you do need to

perform such operations but still wish to use the cache, you can call the ORM::clear_cache() to clear all

existing cached queries.

• Enabling the cache will increase the memory usage of your application, as all database rows that are fetched

during each request are held in memory. If you are working with large quantities of data, you may wish to

disable the cache.

Custom caching

If you wish to use custom caching functions, you can set them from the conﬁgure options.

<?php

$my_cache = array();

ORM::configure('cache_query_result', function ($cache_key, $value, $table_name,

˓→$connection_name) use (&$my_cache) {

$my_cache[$cache_key] = $value;

});

ORM::configure('check_query_cache', function ($cache_key, $table_name, $connection_

˓→name) use (&$my_cache) {

if(isset($my_cache[$cache_key])){

return $my_cache[$cache_key];

} else {

return false;

}

});

ORM::configure('clear_cache', function ($table_name, $connection_name) use (&$my_

˓→cache) {

$my_cache = array();

});

ORM::configure('create_cache_key', function ($query, $parameters, $table_name,

˓→$connection_name) {

$parameter_string = join(',', $parameters);

$key = $query . ':' . $parameter_string;

$my_key = 'my-prefix'.crc32($key);

return $my_key;

});

12 Chapter 3. Conﬁguration

CHAPTER 4

Querying

Idiorm provides a *ﬂuent interface* to enable simple queries to be built without writing a single character of SQL. If

you’ve used jQuery at all, you’ll be familiar with the concept of a ﬂuent interface. It just means that you can chain

method calls together, one after another. This can make your code more readable, as the method calls strung together

in order can start to look a bit like a sentence.

All Idiorm queries start with a call to the for_table static method on the ORM class. This tells the ORM which

table to use when making the query.

Note that this method **does not* escape its query parameter and so the table name should not be passed directly

from user input.*

Method calls which add ﬁlters and constraints to your query are then strung together. Finally, the chain is ﬁnished by

calling either find_one() or find_many(), which executes the query and returns the result.

Let’s start with a simple example. Say we have a table called person which contains the columns id (the primary

key of the record - Idiorm assumes the primary key column is called id but this is conﬁgurable, see below), name,

age and gender.

4.1 A note on PSR-1 and camelCase

All the methods detailed in the documentation can also be called in a PSR-1 way: underscores (_) become camelCase.

Here follows an example of one query chain being converted to a PSR-1 compliant style.

<?php

// documented and default style

$person = ORM::for_table('person')->where('name', 'Fred Bloggs')->find_one();

// PSR-1 compliant style

$person = ORM::forTable('person')->where('name', 'Fred Bloggs')->findOne();

As you can see any method can be changed from the documented underscore (_) format to that of a camelCase method

name.

Idiorm Documentation

Note: In the background the PSR-1 compliant style uses the __call() and __callStatic() magic methods to map the

camelCase method name you supply to the original underscore method name. It then uses call_user_func_array()

to apply the arguments to the method. If this minimal overhead is too great then you can simply revert to using the

underscore methods to avoid it. In general this will not be a bottle neck in any application however and should be

considered a micro-optimisation.

As __callStatic() was added in PHP 5.3.0 you will need at least that version of PHP to use this feature in any meaningful

way.

4.2 Single records

Any method chain that ends in find_one() will return either a single instance of the ORM class representing the

database row you requested, or false if no matching record was found.

To ﬁnd a single record where the name column has the value “Fred Bloggs”:

<?php

$person = ORM::for_table('person')->where('name', 'Fred Bloggs')->find_one();

This roughly translates into the following SQL: SELECT

FROM person WHERE name = "Fred

Bloggs"

To ﬁnd a single record by ID, you can pass the ID directly to the find_one method:

<?php

$person = ORM::for_table('person')->find_one(5);

If you are using a compound primary key, you can ﬁnd the records using an array as the parameter:

<?php

$person = ORM::for_table('user_role')->find_one(array(

'user_id' => 34,

'role_id' => 10

));

4.3 Multiple records

Note: It is recommended that you use results sets over arrays - see As a result set below.

Any method chain that ends in find_many() will return an array of ORM class instances, one for each row matched

by your query. If no rows were found, an empty array will be returned.

To ﬁnd all records in the table:

<?php

$people = ORM::for_table('person')->find_many();

To ﬁnd all records where the gender is female:

<?php

$females = ORM::for_table('person')->where('gender', 'female')->find_many();

14 Chapter 4. Querying

Idiorm Documentation

4.3.1 As a result set

Note: There is a conﬁguration setting return_result_sets that will cause find_many() to return result sets

by default. It is recommended that you turn this setting on:

ORM::configure('return_result_sets', true);

You can also ﬁnd many records as a result set instead of an array of Idiorm instances. This gives you the advantage

that you can run batch operations on a set of results.

So for example instead of running this:

<?php

$people = ORM::for_table('person')->find_many();

foreach ($people as $person) {

$person->age = 50;

$person->save();

}

You can simply do this instead:

<?php

ORM::for_table('person')->find_result_set()

->set('age', 50)

->save();

To do this substitute any call to find_many() with find_result_set().

A result set will also behave like an array so you can count() it and foreach over it just like an array.

<?php

foreach(ORM::for_table('person')->find_result_set() as $record) {

echo $record->name;

}

<?php

echo count(ORM::for_table('person')->find_result_set());

Note: For deleting many records it is recommended that you use delete_many() as it is more efﬁcient than calling

delete() on a result set.

4.3.2 As an associative array

You can also ﬁnd many records as an associative array instead of Idiorm instances. To do this substitute any call to

find_many() with find_array().

<?php

$females = ORM::for_table('person')->where('gender', 'female')->find_array();

This is useful if you need to serialise the the query output into a format like JSON and you do not need the ability to

update the returned records.

4.3. Multiple records 15

Idiorm Documentation

4.4 Counting results

To return a count of the number of rows that would be returned by a query, call the count() method.

<?php

$number_of_people = ORM::for_table('person')->count();

4.5 Filtering results

Idiorm provides a family of methods to extract only records which satisfy some condition or conditions. These methods

may be called multiple times to build up your query, and Idiorm’s ﬂuent interface allows method calls to be chained

to create readable and simple-to-understand queries.

4.5.1 Caveats

Only a subset of the available conditions supported by SQL are available when using Idiorm. Additionally, all the

WHERE clauses will be ANDed together when the query is run. Support for ORing WHERE clauses is not currently

present.

These limits are deliberate: these are by far the most commonly used criteria, and by avoiding support for very complex

queries, the Idiorm codebase can remain small and simple.

Some support for more complex conditions and queries is provided by the where_raw and raw_query methods

(see below). If you ﬁnd yourself regularly requiring more functionality than Idiorm can provide, it may be time to

consider using a more full-featured ORM.

4.5.2 Equality: where, where_equal, where_not_equal

By default, calling where with two parameters (the column name and the value) will combine them using an

equals operator (=). For example, calling where('name', 'Fred') will result in the clause WHERE name

= "Fred".

If your coding style favours clarity over brevity, you may prefer to use the where_equal method: this is identical

to where.

The where_not_equal method adds a WHERE column != "value" clause to your query.

You can specify multiple columns and their values in the same call. In this case you should pass an associative array

as the ﬁrst parameter. The array notation uses keys as column names.

<?php

$people = ORM::for_table('person')

->where(array(

'name' => 'Fred',

'age' => 20

))

->find_many();

// Creates SQL:

SELECT

FROM `person` WHERE `name` = "Fred" AND `age` = "20";

16 Chapter 4. Querying

Idiorm Documentation

4.5.3 Shortcut: where_id_is

This is a simple helper method to query the table by primary key. Respects the ID column speciﬁed in the conﬁg. If

you are using a compound primary key, you must pass an array where the key is the column name. Columns that don’t

belong to the key will be ignored.

4.5.4 Shortcut: where_id_in

This helper method is similar to ‘‘where_id_is‘, but it expects an array of primary keys to be selected. It is compound

primary keys aware.

4.5.5 Less than / greater than: where_lt, where_gt, where_lte, where_gte

There are four methods available for inequalities:

• Less than: $people = ORM::for_table('person')->where_lt('age',

10)->find_many();

• Greater than: $people = ORM::for_table('person')->where_gt('age',

5)->find_many();

• Less than or equal: $people = ORM::for_table('person')->where_lte('age',

10)->find_many();

• Greater than or equal: $people = ORM::for_table('person')->where_gte('age',

5)->find_many();

4.5.6 String comparision: where_like and where_not_like

To add a WHERE ... LIKE clause, use:

<?php

$people = ORM::for_table('person')->where_like('name', '%fred%')->find_many();

Similarly, to add a WHERE ... NOT LIKE clause, use:

<?php

$people = ORM::for_table('person')->where_not_like('name', '%bob%')->find_many();

4.5.7 Multiple OR’ed conditions

You can add simple OR’ed conditions to the same WHERE clause using where_any_is. You should specify

multiple conditions using an array of items. Each item will be an associative array that contains a multiple conditions.

<?php

$people = ORM::for_table('person')

->where_any_is(array(

array('name' => 'Joe', 'age' => 10),

array('name' => 'Fred', 'age' => 20)))

->find_many();

// Creates SQL:

SELECT

FROM `widget` WHERE (( `name` = 'Joe' AND `age` = '10' ) OR ( `name` = 'Fred

˓→' AND `age` = '20' ));

(continues on next page)

4.5. Filtering results 17

Idiorm Documentation

(continued from previous page)

By default, it uses the equal operator for every column, but it can be overriden for any column using a second param-

eter:

<?php

$people = ORM::for_table('person')

->where_any_is(array(

array('name' => 'Joe', 'age' => 10),

array('name' => 'Fred', 'age' => 20)), array('age' => '>'))

->find_many();

// Creates SQL:

SELECT

FROM `widget` WHERE (( `name` = 'Joe' AND `age` = '10' ) OR ( `name` = 'Fred

˓→' AND `age` > '20' ));

If you want to set the default operator for all the columns, just pass it as the second parameter:

<?php

$people = ORM::for_table('person')

->where_any_is(array(

array('score' => '5', 'age' => 10),

array('score' => '15', 'age' => 20)), '>')

->find_many();

// Creates SQL:

SELECT

FROM `widget` WHERE (( `score` > '5' AND `age` > '10' ) OR ( `score` > '15'

˓→AND `age` > '20' ));

4.5.8 Set membership: where_in and where_not_in

To add a WHERE ... IN () or WHERE ... NOT IN () clause, use the where_in and where_not_in

methods respectively.

Both methods accept two arguments. The ﬁrst is the column name to compare against. The second is an array of

possible values. As all the where_ methods, you can specify multiple columns using an associative array as the only

parameter.

<?php

$people = ORM::for_table('person')->where_in('name', array('Fred', 'Joe', 'John'))->

˓→find_many();

4.5.9 Working with NULL values: where_null and where_not_null

To add a WHERE column IS NULL or WHERE column IS NOT NULL clause, use the where_null and

where_not_null methods respectively. Both methods accept a single parameter: the column name to test.

4.5.10 Raw WHERE clauses

If you require a more complex query, you can use the where_raw method to specify the SQL fragment for the

WHERE clause exactly. This method takes two arguments: the string to add to the query, and an (optional) array

of parameters which will be bound to the string. If parameters are supplied, the string should contain question mark

18 Chapter 4. Querying

Idiorm Documentation

characters (?) to represent the values to be bound, and the parameter array should contain the values to be substituted

into the string in the correct order.

This method may be used in a method chain alongside other where_

methods as well as methods such as offset,

limit and order_by_

. The contents of the string you supply will be connected with preceding and following

WHERE clauses with AND.

<?php

$people = ORM::for_table('person')

->where('name', 'Fred')

->where_raw('(`age` = ? OR `age` = ?)', array(20, 25))

->order_by_asc('name')

->find_many();

// Creates SQL:

SELECT

FROM `person` WHERE `name` = "Fred" AND (`age` = 20 OR `age` = 25) ORDER BY

˓→`name` ASC;

Note: You must wrap your expression in parentheses when using any of ALL, ANY, BETWEEN, IN, LIKE, OR and

SOME. Otherwise the precedence of AND will bind stronger and in the above example you would effectively get WHERE

(`name` = "Fred" AND `age` = 20) OR `age` = 25

Note that this method only supports “question mark placeholder” syntax, and NOT “named placeholder” syntax. This

is because PDO does not allow queries that contain a mixture of placeholder types. Also, you should ensure that the

number of question mark placeholders in the string exactly matches the number of elements in the array.

If you require yet more ﬂexibility, you can manually specify the entire query. See Raw queries below.

4.5.11 Limits and offsets

Note that these methods **do not* escape their query parameters and so these should not be passed directly from user

input.*

The limit and offset methods map pretty closely to their SQL equivalents.

<?php

$people = ORM::for_table('person')->where('gender', 'female')->limit(5)->offset(10)->

˓→find_many();

4.5.12 Ordering

Note that these methods **do not* escape their query parameters and so these should not be passed directly from user

input.*

Two methods are provided to add ORDER BY clauses to your query. These are order_by_desc and

order_by_asc, each of which takes a column name to sort by. The column names will be quoted.

<?php

$people = ORM::for_table('person')->order_by_asc('gender')->order_by_desc('name')->

˓→find_many();

If you want to order by something other than a column name, then use the order_by_expr method to add an

unquoted SQL expression as an ORDER BY clause.

4.5. Filtering results 19

Idiorm Documentation

<?php

$people = ORM::for_table('person')->order_by_expr('SOUNDEX(`name`)')->find_many();

4.6 Grouping

Note that this method **does not* escape it query parameter and so this should not by passed directly from user

input.*

To add a GROUP BY clause to your query, call the group_by method, passing in the column name. You can call this

method multiple times to add further columns.

<?php

$people = ORM::for_table('person')->where('gender', 'female')->group_by('name')->find_

˓→many();

It is also possible to GROUP BY a database expression:

<?php

$people = ORM::for_table('person')->where('gender', 'female')->group_by_expr("FROM_

˓→UNIXTIME(`time`, '%Y-%m')")->find_many();

4.7 Having

When using aggregate functions in combination with a GROUP BY you can use HAVING to ﬁlter based on those

values.

HAVING works in exactly the same way as all of the where

functions in Idiorm. Substitute where_ for having_

to make use of these functions.

For example:

<?php

$people = ORM::for_table('person')->group_by('name')->having_not_like('name', '%bob%

˓→')->find_many();

4.8 Result columns

By default, all columns in the SELECT statement are returned from your query. That is, calling:

<?php

$people = ORM::for_table('person')->find_many();

Will result in the query:

<?php

SELECT

FROM `person`;

The select method gives you control over which columns are returned. Call select multiple times to specify

columns to return or use `select_many <#shortcuts-for-specifying-many-columns>‘_ to specify many columns at

once.

20 Chapter 4. Querying

Idiorm Documentation

<?php

$people = ORM::for_table('person')->select('name')->select('age')->find_many();

Will result in the query:

<?php

SELECT `name`, `age` FROM `person`;

Optionally, you may also supply a second argument to select to specify an alias for the column:

<?php

$people = ORM::for_table('person')->select('name', 'person_name')->find_many();

Will result in the query:

<?php

SELECT `name` AS `person_name` FROM `person`;

Column names passed to select are quoted automatically, even if they contain table.column-style identiﬁers:

<?php

$people = ORM::for_table('person')->select('person.name', 'person_name')->find_many();

Will result in the query:

<?php

SELECT `person`.`name` AS `person_name` FROM `person`;

If you wish to override this behaviour (for example, to supply a database expression) you should instead use the

select_expr method. Again, this takes the alias as an optional second argument. You can specify multiple expres-

sions by calling select_expr multiple times or use `select_many_expr <#shortcuts-for-specifying-many-

columns>‘_ to specify many expressions at once.

<?php

// NOTE: For illustrative purposes only. To perform a count query, use the count()

˓→method.

$people_count = ORM::for_table('person')->select_expr('COUNT(

)', 'count')->find_

˓→many();

Will result in the query:

<?php

SELECT COUNT(

) AS `count` FROM `person`;

4.8.1 Shortcuts for specifying many columns

select_many and select_many_expr are very similar, but they allow you to specify more than one column at

once. For example:

<?php

$people = ORM::for_table('person')->select_many('name', 'age')->find_many();

Will result in the query:

4.8. Result columns 21

Idiorm Documentation

<?php

SELECT `name`, `age` FROM `person`;

To specify aliases you need to pass in an array (aliases are set as the key in an associative array):

<?php

$people = ORM::for_table('person')->select_many(array('first_name' => 'name'), 'age',

˓→'height')->find_many();

Will result in the query:

<?php

SELECT `name` AS `first_name`, `age`, `height` FROM `person`;

You can pass the the following styles into select_many and select_many_expr by mixing and matching arrays

and parameters:

<?php

select_many(array('alias' => 'column', 'column2', 'alias2' => 'column3'), 'column4',

˓→'column5')

select_many('column', 'column2', 'column3')

select_many(array('column', 'column2', 'column3'), 'column4', 'column5')

All the select methods can also be chained with each other so you could do the following to get a neat select query

including an expression:

<?php

$people = ORM::for_table('person')->select_many('name', 'age', 'height')->select_expr(

˓→'NOW()', 'timestamp')->find_many();

Will result in the query:

<?php

SELECT `name`, `age`, `height`, NOW() AS `timestamp` FROM `person`;

4.9 DISTINCT

To add a DISTINCT keyword before the list of result columns in your query, add a call to distinct() to your

query chain.

<?php

$distinct_names = ORM::for_table('person')->distinct()->select('name')->find_many();

This will result in the query:

<?php

SELECT DISTINCT `name` FROM `person`;

4.10 Joins

Idiorm has a family of methods for adding different types of JOINs to the queries it constructs:

Methods: join, inner_join, left_outer_join, right_outer_join, full_outer_join.

22 Chapter 4. Querying

Idiorm Documentation

Each of these methods takes the same set of arguments. The following description will use the basic join method as

an example, but the same applies to each method.

The ﬁrst two arguments are mandatory. The ﬁrst is the name of the table to join, and the second supplies the conditions

for the join. The recommended way to specify the conditions is as an array containing three components: the ﬁrst

column, the operator, and the second column. The table and column names will be automatically quoted. For example:

<?php

$results = ORM::for_table('person')->join('person_profile', array('person.id', '=',

˓→'person_profile.person_id'))->find_many();

It is also possible to specify the condition as a string, which will be inserted as-is into the query. However, in this case

the column names will not be escaped, and so this method should be used with caution.

<?php

// Not recommended because the join condition will not be escaped.

$results = ORM::for_table('person')->join('person_profile', 'person.id = person_

˓→profile.person_id')->find_many();

The join methods also take an optional third parameter, which is an alias for the table in the query. This is

useful if you wish to join the table to itself to create a hierarchical structure. In this case, it is best combined with the

table_alias method, which will add an alias to the main table associated with the ORM, and the select method

to control which columns get returned.

<?php

$results = ORM::for_table('person')

->table_alias('p1')

->select('p1.

->select('p2.name', 'parent_name')

->join('person', array('p1.parent', '=', 'p2.id'), 'p2')

->find_many();

4.10.1 Raw JOIN clauses

If you need to construct a more complex query, you can use the raw_join method to specify the SQL fragment for

the JOIN clause exactly. This method takes four required arguments: the string to add to the query, the conditions is

as an array containing three components: the ﬁrst column, the operator, and the second column, the table alias and

(optional) the parameters array. If parameters are supplied, the string should contain question mark characters (?) to

represent the values to be bound, and the parameter array should contain the values to be substituted into the string in

the correct order.

This method may be used in a method chain alongside other

_join methods as well as methods such as offset,

limit and order_by_

. The contents of the string you supply will be connected with preceding and following

JOIN clauses.

<?php

$people = ORM::for_table('person')

->raw_join(

'JOIN (SELECT

FROM role WHERE role.name = ?)',

array('person.role_id', '=', 'role.id'),

'role',

array('role' => 'janitor'))

->order_by_asc('person.name')

->find_many();

(continues on next page)

4.10. Joins 23

Idiorm Documentation

(continued from previous page)

// Creates SQL:

SELECT

FROM `person` JOIN (SELECT

FROM role WHERE role.name = 'janitor') `role`

˓→ON `person`.`role_id` = `role`.`id` ORDER BY `person`.`name` ASC

Note that this method only supports “question mark placeholder” syntax, and NOT “named placeholder” syntax. This

is because PDO does not allow queries that contain a mixture of placeholder types. Also, you should ensure that the

number of question mark placeholders in the string exactly matches the number of elements in the array.

If you require yet more ﬂexibility, you can manually specify the entire query. See Raw queries below.

4.11 Aggregate functions

There is support for MIN, AVG, MAX and SUM in addition to COUNT (documented earlier).

To return a minimum value of column, call the min() method.

<?php

$min = ORM::for_table('person')->min('height');

The other functions (AVG, MAX and SUM) work in exactly the same manner. Supply a column name to perform the

aggregate function on and it will return an integer.

4.12 Raw queries

If you need to perform more complex queries, you can completely specify the query to execute by using the

raw_query method. This method takes a string and optionally an array of parameters. The string can contain

placeholders, either in question mark or named placeholder syntax, which will be used to bind the parameters to the

query.

<?php

$people = ORM::for_table('person')->raw_query('SELECT p.

FROM person p JOIN role r

˓→ON p.role_id = r.id WHERE r.name = :role', array('role' => 'janitor'))->find_many();

The ORM class instance(s) returned will contain data for all the columns returned by the query. Note that you still must

call for_table to bind the instances to a particular table, even though there is nothing to stop you from specifying

a completely different table in the query. This is because if you wish to later called save, the ORM will need to know

which table to update.

Note: Using raw_query is advanced and possibly dangerous, and Idiorm does not make any attempt to protect

you from making errors when using this method. If you ﬁnd yourself calling raw_query often, you may have

misunderstood the purpose of using an ORM, or your application may be too complex for Idiorm. Consider using a

more full-featured database abstraction system.

4.12.1 Raw SQL execution using PDO

24 Chapter 4. Querying

Idiorm Documentation

Warning: By using this function you’re dropping down to PHPs PDO directly. Idiorm does not make any attempt

to protect you from making errors when using this method.

You’re essentially just using Idiorm to manage the connection and conﬁguration when you implement

raw_execute().

It can be handy, in some instances, to make use of the PDO instance underneath Idiorm to make advanced queries.

These can be things like dropping a table from the database that Idiorm doesn’t support and will not support in the

future. These are operations that fall outside the 80/20 philosophy of Idiorm. That said there is a lot of interest in this

function and quite a lot of support requests related to it.

This method directly maps to PDOStatement::execute() underneath so please familiarise yourself with it’s documen-

tation.

Dropping tables

This can be done very simply using raw_execute().

<?php

if (ORM::raw_execute('DROP TABLE my_table')) {

echo "Table dropped";

} else {

echo "Drop query failed";

}

Selecting rows

Warning: You really, should not be doing this, use Idiorm with raw_query() instead where possible.

Here is a simple query implemented using raw_execute() - note the call to ORM::get_last_statement()

as raw_execute() returns a boolean as per the PDOStatement::execute() underneath.

<?php

$res = ORM::raw_execute('SHOW TABLES');

$statement = ORM::get_last_statement();

$rows = array();

while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {

var_dump($row);

}

It is also worth noting that $statement is a PDOStatement instance so calling its fetch() method is the same

as if you had called against PDO without Idiorm.

4.12.2 Getting the PDO instance

Warning: By using this function you’re dropping down to PHPs PDO directly. Idiorm does not make any attempt

to protect you from making errors when using this method.

4.12. Raw queries 25

Idiorm Documentation

You’re essentially just using Idiorm to manage the connection and conﬁguration when you implement against

get_db().

If none of the preceeding methods suit your purposes then you can also get direct access to the PDO instance under-

neath Idiorm using ORM::get_db(). This will return a conﬁgured instance of PDO.

<?php

$pdo = ORM::get_db();

foreach($pdo->query('SHOW TABLES') as $row) {

var_dump($row);

}

26 Chapter 4. Querying

CHAPTER 5

Models

5.1 Getting data from objects

Once you’ve got a set of records (objects) back from a query, you can access properties on those objects (the values

stored in the columns in its corresponding table) in two ways: by using the get method, or simply by accessing the

property on the object directly:

<?php

$person = ORM::for_table('person')->find_one(5);

// The following two forms are equivalent

$name = $person->get('name');

$name = $person->name;

You can also get the all the data wrapped by an ORM instance using the as_array method. This will return an

associative array mapping column names (keys) to their values.

The as_array method takes column names as optional arguments. If one or more of these arguments is supplied,

only matching column names will be returned.

<?php

$person = ORM::for_table('person')->create();

$person->first_name = 'Fred';

$person->surname = 'Bloggs';

$person->age = 50;

// Returns array('first_name' => 'Fred', 'surname' => 'Bloggs', 'age' => 50)

$data = $person->as_array();

// Returns array('first_name' => 'Fred', 'age' => 50)

$data = $person->as_array('first_name', 'age');

Idiorm Documentation

5.2 Updating records

To update the database, change one or more of the properties of the object, then call the save method to commit the

changes to the database. Again, you can change the values of the object’s properties either by using the set method or

by setting the value of the property directly. By using the set method it is also possible to update multiple properties

at once, by passing in an associative array:

<?php

$person = ORM::for_table('person')->find_one(5);

// The following two forms are equivalent

$person->set('name', 'Bob Smith');

$person->age = 20;

// This is equivalent to the above two assignments

$person->set(array(

'name' => 'Bob Smith',

'age' => 20

));

// Syncronise the object with the database

$person->save();

5.2.1 Properties containing expressions

It is possible to set properties on the model that contain database expressions using the set_expr method.

<?php

$person = ORM::for_table('person')->find_one(5);

$person->set('name', 'Bob Smith');

$person->age = 20;

$person->set_expr('updated', 'NOW()');

$person->save();

The updated column’s value will be inserted into query in its raw form therefore allowing the database to execute

any functions referenced - such as NOW() in this case.

5.3 Creating new records

To add a new record, you need to ﬁrst create an “empty” object instance. You then set values on the object as normal,

and save it.

<?php

$person = ORM::for_table('person')->create();

$person->name = 'Joe Bloggs';

$person->age = 40;

$person->save();

After the object has been saved, you can call its id() method to ﬁnd the autogenerated primary key value that the

database assigned to it.

28 Chapter 5. Models

Idiorm Documentation

5.3.1 Properties containing expressions

It is possible to set properties on the model that contain database expressions using the set_expr method.

<?php

$person = ORM::for_table('person')->create();

$person->set('name', 'Bob Smith');

$person->age = 20;

$person->set_expr('added', 'NOW()');

$person->save();

The added column’s value will be inserted into query in its raw form therefore allowing the database to execute any

functions referenced - such as NOW() in this case.

5.4 Checking whether a property has been modiﬁed

To check whether a property has been changed since the object was created (or last saved), call the is_dirty method:

<?php

$name_has_changed = $person->is_dirty('name'); // Returns true or false

5.5 Deleting records

To delete an object from the database, simply call its delete method.

<?php

$person = ORM::for_table('person')->find_one(5);

$person->delete();

To delete more than one object from the database, build a query:

<?php

$person = ORM::for_table('person')

->where_equal('zipcode', 55555)

->delete_many();

5.4. Checking whether a property has been modiﬁed 29

Idiorm Documentation

30 Chapter 5. Models

CHAPTER 6

Transactions

Idiorm doesn’t supply any extra methods to deal with transactions, but it’s very easy to use PDO’s built-in methods:

<?php

// Start a transaction

ORM::get_db()->beginTransaction();

// Commit a transaction

ORM::get_db()->commit();

// Roll back a transaction

ORM::get_db()->rollBack();

For more details, see the PDO documentation on Transactions.

Idiorm Documentation

32 Chapter 6. Transactions

CHAPTER 7

Multiple Connections

Idiorm now works with multiple conections. Most of the static functions work with an optional connection name as

an extra parameter. For the ORM::configure method, this means that when passing connection strings for a new

connection, the second parameter, which is typically omitted, should be null. In all cases, if a connection name is

not provided, it defaults to ORM::DEFAULT_CONNECTION.

When chaining, once for_table() has been used in the chain, remaining calls in the chain use the correct connec-

tion.

<?php

// Default connection

ORM::configure('sqlite:./example.db');

// A named connection, where 'remote' is an arbitrary key name

ORM::configure('mysql:host=localhost;dbname=my_database', null, 'remote');

ORM::configure('username', 'database_user', 'remote');

ORM::configure('password', 'top_secret', 'remote');

// Using default connection

$person = ORM::for_table('person')->find_one(5);

// Using default connection, explicitly

$person = ORM::for_table('person', ORM::DEFAULT_CONNECTION)->find_one(5);

// Using named connection

$person = ORM::for_table('different_person', 'remote')->find_one(5);

7.1 Supported Methods

In each of these cases, the $connection_name parameter is optional, and is an arbitrary key identifying the named

connection.

• ORM::configure($key, $value, $connection_name)

Idiorm Documentation

• ORM::for_table($table_name, $connection_name)

• ORM::set_db($pdo, $connection_name)

• ORM::get_db($connection_name)

• ORM::raw_execute($query, $parameters, $connection_name)

• ORM::get_last_query($connection_name)

• ORM::get_query_log($connection_name)

Of these methods, only ORM::get_last_query($connection_name) does not fallback to the default con-

nection when no connection name is passed. Instead, passing no connection name (or null) returns the most recent

query on any connection.

<?php

// Using default connection, explicitly

$person = ORM::for_table('person')->find_one(5);

// Using named connection

$person = ORM::for_table('different_person', 'remote')->find_one(5);

// Last query on

any

connection

ORM::get_last_query(); // returns query on 'different_person' using 'remote'

// returns query on 'person' using default by passing in the connection name

ORM::get_last_query(ORM::DEFAULT_CONNECTION);

7.1.1 Notes

• There is no support for joins across connections

• Multiple connections do not share conﬁguration settings. This means if one connection has logging

set to true and the other does not, only queries from the logged connection will be available via

ORM::get_last_query() and ORM::get_query_log().

• A new method has been added, ORM::get_connection_names(), which returns an array of connection

names.

• Caching should work with multiple connections (remember to turn caching on for each connection), but the unit

tests are not robust. Please report any errors.

34 Chapter 7. Multiple Connections

CHAPTER 8

Indices and tables

• genindex

• modindex

• search