ORMer - Examples (back)

MySQL Table Schema Sqlite3 schema

create table user (
	id int(11) auto_increment,
	name varchar(255),
	email varchar(255),
	status_id int(11),
	foreign key (status_id) references status(id),
	primary key (id)
);
create table status (
	id int(11) auto_increment,
	name varchar(255),
	primary key (id)
);
create table orders (
	id int(11) auto_increment,
	date_created datetime,
	user_id int(11),
	total decimal(9,2),
	foreign key (user_id) references user(id),
	primary key (id)
);

insert into status (name) values('Active');
insert into status (name) values('Inactive');

insert into user (name,status_id) values('Jim Lansing', 1);
insert into user (name,status_id) values('Tim Tapper', 2);

insert into orders (user_id, total, date_created) values(1, 30.59, '2007-05-09 12:34:58');
insert into orders (user_id, total, date_created) values(1, 29.95, '2008-01-02 11:34:59');
insert into orders (user_id, total, date_created) values(1, 1098.23, '2007-12-02 04:56:53');
insert into orders (user_id, total, date_created) values(1, 3220.89, '2008-02-06 18:34:21');

Setup

// Include necessary files
include('dbFacile.php');
include('orm.php');

// Setup dbFacile first
$db = dbFacile::open('mysql', 'database', 'user', 'password', 'host');

// Tell ORMer which dbFacile to use
orm::$db = $db;

// Manually create relation mapping structure (auto-gen coming soon)
orm::$relationCache = array(
	'user' => array(
		'orders' => 'user.id = orders.user_id'
	),
	'orders' => array(
		'order_product' => 'orders.id = order_product.order_id'
	),
	'order_product' => array(
		'product' => 'order_product.product_id = product.id'
	)
);

// Create classes
class user extends orm {
	// due to quirks in PHP's OO design
	public static function find($query = null) {
		return orm::find(__CLASS__, $query);
	}
}
class orders extends orm {
	public static function find($query = null) {
		return orm::find(__CLASS__, $query);
	}
}
class order_product extends orm {
	public static function find($query = null) {
		return orm::find(__CLASS__, $query);
	}
}
class product extends orm {
	public static function find($query = null) {
		return orm::find(__CLASS__, $query);
	}
}

Finding

Find users with a specific email address.

// Pull user objects "where email='me@host.com'"
$users = user::find()
	->where('email=?')
	->parameters('me@host.com');

Advanced Finding

Color coding helps.

Match the colored portion of the SQL select statement with the method used for modifying it.

select * from users where email="me@host.com" group by email order by name asc limit 0, 30

select() - Accepts a string. Changes the fields that will be returned. Should prefix string with table name.
from() - Accepts a string. Pushes onto array of previously specified tables.
where() - Accepts a string. Pushes onto an array of previously specified where clauses. Joins them with "and".
group() - Accepts a string. Pushes onto an array of previously specified group by clauses. Joins then with ",".
order() - Accepts a string. Pushes onto an array of previously specified order by clauses. Joins then with ",".
limit() - Accepts a string. Sets the limit.

Extrapolate this to:

$users = user::find()
	->where('email=:e')
	->parameter('e', 'me@host.com')
	->group('email')
	->order('name asc')
	->limit('0, 30');

Finding Through Relation Chaining

Pull Products these users have ordered that begin with "a".

// Chain through to pull ordered products starting with "a"
// (using $users from above)
$products = $users->orders
	->order_product
	->product
	->where('product.name like "a%"');

// Loop over them and display their names
foreach($products as $product) {
    echo $product->name . '<br>';
}

You can use any of the following to customize a find() or access to a related record.