Skip to content

Latest commit

 

History

History
621 lines (473 loc) · 19.9 KB

builder.md

File metadata and controls

621 lines (473 loc) · 19.9 KB
Raw

Database Query Extensions

All the native kohana classes for interation with the database (Database_Query_Builder_Select, Database_Query_Builder_Update, Database_Query_Builder_Delete), Database_Query_Builder_Insert have been updated to allow you to use jam models, and the knowledge of the associations to make it easier to construt queries. The respective classes are

  • Jam_Database_Query_Builder_Select
  • Jam_Database_Query_Builder_Update
  • Jam_Database_Query_Builder_Delete
  • Jam_Database_Query_Builder_Insert

These are quite long names so to ease up on the developer Jam follows kohana's example and has some helper methods to build those objects

  • Jam::select()
  • Jam::update()
  • Jam::delete()
  • Jam::insert()

These classes allow using models in you queries E.g.

Retrieving a Single Object

Jam provides several different ways of retrieving a single object.

Using a find Method

The simplest and most stright forward way to retrieve an object is with the find() method. It has 2 arguments - the first is the model name and the second is the id:

<?php
$client = Jam::find('client', 1);
?>

You can use a name key for the model if it has one defined as is the case with the client model

<?php
$client = Jam::find('client', 'Petrov');
?>

If the item has not been found, then find() will return NULL

<?php
$client = Jam::find('client');
$client; // FALSE
?>

Using the find_insist() Method

This is the same as find, but throws a Jam_Exception_Notfound exception if the model is not found. If you have a more complex query and want to get only one model out of it (the first one) then you s

Retrieving multiple models with specific ids

If you want to get objects with a specific primary_key, just use the find() pass an array of keys. It will restrict the search to those ids with a IN SQL clause. If you use a find_insist with multiple ids, then if any of the records is missing, it will throw a Jam_Exception_Notfound exception.

<?php
Jam::find('client', array(1, 2));                 // Jam_Collection: Model_Client(2)
Jam::find_insist('client', array(1, 2, 100));     // Throws Jam_Exception_Notfound
?>

Retriving multiple models with a query

Retrieving multiple objects is performed with a Jam::all() method call. It returns a Jam_Query_Builder_Collection object which is an iterator and behaves both like an array (you can do foreachand[]` on it) and like a Database_Query object where you can add query elemetns on it. It utalizes lazy loading so the query gets executed when you try to retrieve the data (e.g. foreach it).

If you want to inspect the query, generated by Jam_Query_Builder_Collection just cast it to a string

<?php
Jam::all('client')->where('title', '=', 'Patrik');    // SELECT clients.* FROM clients WHERE title = 'Patrick'
?>

Jam_Query_Builder_Collection query methods

Jam_Query_Builder_Collection has a lot of methods to help you abstract the SQL queries away

  • first()
  • first_insist()
  • where_key()
  • where()
  • where_close()
  • where_open()
  • or_where()
  • or_where_close()
  • or_where_open()
  • and_where()
  • and_where_close()
  • and_where_open()
  • having()
  • having_close()
  • having_open()
  • and_having()
  • and_having_close()
  • and_having_open()
  • or_having()
  • or_having_close()
  • or_having_open()
  • join()
  • on()
  • join_nested()
  • join_table()
  • distinct()
  • order_by()
  • group_by()
  • limit()
  • offset()
  • count_all()
  • count_with_subquery()
  • aggregate()

first()

If you want to get a the first result of the query as a Jam_model, use the first() method. It addds limit(1) to the query and returns the first record found as a Jam_Model, or NULL in case it was not found

<?php
Jam::all('client')->first();
Jam::all('client')->where('id', '=', 5)->first();
?>

first_insist()

The same as the first() method, but throws Jam_Exception_Notfound if nothing is found.

where_key()

If you want to limit the records to specific ids, then you can use the where_key() method. It uses the unique_key method to determine what column to use for the WHERE clasue - by default primary_key for numeric values and name_key for string values. However you can extend this method in the builder and have some other logic. This is explained below. You can also pass an array of ids.

<?php
Jam::all('client')->where_key(1)->first();                   // Model_Client(1)
Jam::all('client')->where_key('Pesho')->first();             // Model_Client(1)
Jam::all('client')->where_key(array(1, 2));                  // Jam_Query_Builder_Collection: Model_Client(2)
?>

where(), and_where()

This is the basic constraint method. It adds a WHERE SQL clause and requires 3 methods - column, operator and value:

<?php
Jam::all('client')->where('title', '=', 'Patrik');    // SELECT clients.* FROM clients WHERE title = 'Patrick'
Jam::all('client')->where('title', 'LIKE', 'Pat%');   // SELECT clients.* FROM clients WHERE title LIKE 'Pat%'
?>

where(), and_where() are the same method - where() is just a shortcut.

There are a lot of logic to help you in writing precise SQL queries in the where() functions. When you have ambiguous column names, you can use model names instead of tables. There are also some meta aliases for each model that are available as the name of the field. Also if you want use a SQL Function for the method you can just write it in uppercase, Jam will figure out the details.

<?php
Jam::all('client')->where('client.title', '=', 'Patrik');
// SELECT clients.* FROM clients WHERE clients.title = 'Patrick'

Jam::all('client')->where('client.:name_key', '=', 'Patrik');
// SELECT clients.* FROM clients WHERE clients.title = 'Patrick'

Jam::all('client')->where('client.:primary_key', '=', 10);
// SELECT clients.* FROM clients WHERE clients.id = 10

Jam::all('client')->where('SUBSTR(client.:name_key, 4)', '=', 'noon');
// SELECT clients.* FROM clients WHERE SUBSTR(clients.title, 4) = 'noon'
?>

The available meta aliases with Jam are

  • :primary_key - usually the table's id field
  • :name_key - the field named "name" if not otherwise specified in the initialize() method of the Model
  • :unique_key - the field that uniquely identifies the model based on the value

:unique_key is a bit more tricky. It tries to guess the field you need to search for based on the value itself. If it's a numeric value - then it will use :primary_key, but if it's a normal string, it will use the :name_key.

<?php
Jam::all('client')->where('client.:unique_key', '=', 10);
// SELECT clients.* FROM clients WHERE clients.id = 10

Jam::all('client')->where('client.:unique_key', '=', 'Patrik');
// SELECT clients.* FROM clients WHERE clients.name = 'Patrick'
?>

That in itself is quite neat. However, internally this method uses the unique_key() method of the Jam_Model that could be extended by your application for every Jam_Model. You can for example write a custom logic that searched by email if the value is an email or by IP if the value is by IP. This is all covered in Writing Models & Builders

For operators you can use all the SQL operators ('=', '!=', '>' ...). Special cases are "IN", "IS", "IS NOT", and "BETWEEN" operators

<?php
// IN with array
Jam::all('client')->where('client.id', 'IN', array(1, 2, 3));
// SELECT clients.* FROM clients WHERE clients.id IN array(1, 2, 3)

// IS and IS can use the NULL php constant to pass on NULL
Jam::all('client')->where('client.id', 'IS', NULL);
// SELECT clients.* FROM clients WHERE clients.id IS NULL

// IS and IS NOT can use the NULL php constant to pass on NULL
Jam::all('client')->where('client.id', 'IS NOT', NULL);
// SELECT clients.* FROM clients WHERE clients.id IS NOT NULL

// BETWEEN uses an array for the boundaries
Jam::all('client')->where('client.id', 'BETWEEN', array(1, 100));
// SELECT clients.* FROM clients WHERE clients.id BETWEEN 1 AND 100
?>

or_where()

When you want to add an OR SQL statement, you can use the or_where() method. What it does is simply adds your constraint with and SQL OR.

<?php
Jam::all('client')
	->where('title', '=', 'Patrik')
	->or_where('price', '<', 100);
// SELECT clients.* FROM clients WHERE title = 'Patrick' OR price < 100

Jam::all('client')
	->where('title', 'LIKE', '%Pat%')
	->where('price', '<', 100)
	->or_where('title', 'LIKE', 'Admin%');
// SELECT clients.* FROM clients WHERE title LIKE '%Pat%' AND price < 100 OR title LIKE 'Admin%'
?>

Be careful Using or_where() can lead to unexpected results. If you have builder with some constraints already in place - adding an OR statement will OR with all of them. So you should consider using where_open() and where_close() to enclose statements in brackets, if the result is not what you've expected.

where_open(), where_close(), and_where_close(), and_where_open(), or_where_open(), or_where_close()

When writing complex SQL queries, you often want to put statements in brackets to be more precise with your logic. To do this you use one of the *_open() and *_close() methods. where_open() is just a shortcut for and_where_open() and or_where_open() adds your whole logic block with an OR to the rest of the query.

In this example:

<?php
Jam::all('client')
	->where('title', 'LIKE', '%Pat%')
	->where('price', '<', 100)
	->or_where('title', 'LIKE', 'Admin%');
// SELECT clients.* FROM clients WHERE title = 'Patrick' OR price < 100
?>

If we wanted to change the logic of the query to say that

<?php
Jam::all('client')
	->where('title', 'LIKE', '%Pat%')
	->where_open()
		->where('price', '<', 100)
		->or_where('title', 'LIKE', 'Admin%');
	->where_close()
// SELECT clients.* FROM clients WHERE title LIKE '%Pat%' AND (price < 100 OR title LIKE 'Admin%')
?>

having(), and_having(), or_having(), having_open(), having_close(), and_having_close(), and_having_open(), or_having_open(), or_having_close()

When you want to add constraints to the HAVING SQL clause you can use the having() methods group. They act just like where() but the SQL appears inside HAVING

join(), on()

When you want to join other tables to the SQL, use join() methods. You can join by table name, model name or a table/model and alias. To add an ON statement to the JOIN, you must use the on() method. You can also use multiple on() clauses. Jam keeps track of the models that have been joined already and will not duplicate joins. If you want to join the same model twice with different conditions, you can use an array('model', 'alias') for the first argument. You can also perform different kinds of joins (LEFT, NATURAL ...) with the second argument of the join() method. You generally won't need to write the ->on() statements yourself as they are handled automatically using the logic from your associations

<?php
// Normal JOIN statements
Jam::all('client')->join('order')
// SELECT clients.* FROM clients JOIN orders ON orders.client_id = clients.id

// JOIN statement with alias
Jam::all('client')->join(array('order', 'purchase'))
// SELECT clients.* FROM clients JOIN orders as purchase ON purchase.client_id = clients.id

// LEFT JOIN statements
Jam::query('client', 'LEFT')->join('order')
// SELECT clients.* FROM clients JOIN orders ON orders.client_id = clients.id

// JOIN statement ON
Jam::query('client')->join('order')->on('order.name', '=', 'client.name');
// SELECT clients.* FROM clients JOIN orders ON orders.client_id = clients.id AND orders.name = clients.name
?>

join_nested()

When you want to create complex joins spaning several associations, you will have to use the join_nested() method - it returns a Database_Builder_Query_Join object allowing you to join to subsequent models. E.g.

<?php
class Model_Customer extends Jam_Model {

	public static function initialize(Jam_Meta $meta)
	{
		$meta->association('orders', Jam::association('hasmany'));
		// ...
	}
}

class Model_Order extends Jam_Model {

	public static function initialize(Jam_Meta $meta)
	{
		$meta->association('customer', Jam::association('belongsto'));
		$meta->association('suppliers', Jam::association('hasmany'));

		// ...
	}
}

class Model_Supplier extends Jam_Model {

	public static function initialize(Jam_Meta $meta)
	{
		$meta->association('order', Jam::association('belongsto'));
		$meta->association('addresses', Jam::association('hasmany'));

		// ...
	}
}
// Select all customers that have order, with suppliers and supliers with addresses
Jam::all('customer')
	->join_nested('orders')
		->join_nested('supliers')
			->join('addresses')
		->end()
	->end()
	->where('address.city', '=', 'London')
?>

join_nested() also supports aliases and join types. Also you can write more on statements for each nested join

<?php
Join::all('customer')
	->join_nested(array('orders', 'purchases'), 'LEFT')
		->on('purchases.name', '=', 'customer.name')
	->end()
?>

distinct()

Add DESTINCT to to the SQL SELECT

order_by()

Adding a ORDER BY statement is done with the order_by() method. The first argument is the column, the second is the direction.

When you chain order_by calls, they get added to the ORDER BY statement.

<?php
Jam::all('client')->order_by('title', 'DESC');
// SELECT clients.* FROM clients ORDER BY title DESC

Jam::all('client')->order_by('title', 'DESC')->->order_by('id', 'ASC');
// SELECT clients.* FROM clients ORDER BY title DESC, id ASC
?>

group_by()

Adding a group by statement is done with the group_by() method.

<?php
Jam::all('client')->group_by('title');
// SELECT clients.* FROM clients GROUP BY title

// Multiple columns
Jam::all('client')->group_by('title', 'id');
// SELECT clients.* FROM clients GROUP BY title, id
?>

limit(), offset()

To add LIMIT and OFFSET to your query - use limit() and offset() methods

<?php
Jam::all('client')->limit(10)->offest(100);
// SELECT clients.* FROM clients LIMIT 10 OFFSET 100
?>

select()

If you want to control which columns will be retrieved by the SQL -

  • $all->select('column');
  • $all->select(array('field', 'alias'));
  • $all->select('column', 'column2', array('field', 'alias'), ...));

except()

Clear a sertain part of the query.

<?php
$query = Jam::all('client')->order_by('id', 'DESC')->where('name', 'LIKE', 'Mat');
// SELECT clietns.* FROM clients WHERE name LIKE 'Mat' ORDER BY clients.id DESC

$query->except('order_by');
// SELECT clietns.* FROM clients WHERE name LIKE 'Mat'
?>

Clears all the query statements and starts building the query anew.

count_all(), count_with_subquery()

After you've built your query with the builder you have the option of querying the total number of records this query will return. This is done with the count_all() method. It constructs an SQL COUNT() query that matches your builder constraints and returns an integer.

<?php
Jam::all('client')->where('client.title', '=', 'Patrik')->count_all();
// SELECT COUNT(clients.*) FROM clients WHERE clients.title = 'Patrick'
?>

If you're using groupby or orderby results then the count_all results will probably not be accurate, so you can skip them by passing TRUE as furst argument.

<?php
Jam::all('client')->where('client.title', '=', 'Patrik')->group_by('title')->count_all(TRUE);
// SELECT COUNT(clients.*) FROM clients WHERE clients.title = 'Patrick'
?>

Also in very complex queries that simply cannot be unwravelled for a fast count query, you can use the count_with_subquery() method. What it does is makes the current query an inner query in a simple COUNT() SQL query, returning the accurate count. This however is significantly slower than a simple count() query so is not advised for very big tables.

<?php
Jam::all('client')->where('client.title', '=', 'Patrik')->count_with_subquery();
// SELECT count(*) as `total` FROM (SELECT COUNT(clients.*) FROM clients WHERE clients.title = 'Patrick')
?>

Update queries

You can perform update queries with Jam::update() query builder. It has most of the select methods and some methods for updating rows.

  • where_key()
  • where()
  • where_close()
  • where_open()
  • or_where()
  • or_where_close()
  • or_where_open()
  • and_where()
  • and_where_close()
  • and_where_open()
  • distinct()
  • order_by()
  • limit()
  • set()
  • value()

set()

The actual setting of the values is performed with the set() method you give it an array with key => value pairs that get recorded to the rows, matching the where condition.

<?php
Jam::update('client')->where('client.title', '=', 'Patrik')->set(array('title' => 'Maverik', 'logins' => 4))->execute();
// UPDATE clients SET title = 'Maverik', logins = 4 WHERE title = 'Patrik'
?>

value()

The same as set() but adds only one key => value pair. We are using a separate method like this, because it allows setting NULL values.

<?php
Jam::update('client')->where('client.title', '=', 'Patrik')->value('title', 'Maverik')->execute();
// UPDATE clients SET title = 'Maverik' WHERE title = 'Patrik'
?>

Insert queries

You can perform insert queries with Jam::insert() query builder. It has most of the select methods and some methods for inserting rows.

  • where_key()
  • where()
  • where_close()
  • where_open()
  • or_where()
  • or_where_close()
  • or_where_open()
  • and_where()
  • and_where_close()
  • and_where_open()
  • distinct()
  • order_by()
  • limit()

Inserting a single record

Just like you would do with Kohana database query builder. With Jam though you would pass the name of the model and get automatic field name resolving.

<?php
Jam::insert('client')
	->columns(array(
		'client.title',
		'username' => 'patrick76'
	))
	->values(array(
		'Patrik',
		'patrik76'
	))
	->execute()
// INSERT INTO clients VALUES (`title`, `username`) VALUES ("Patrik", "patrik76")
?>

You could also pass columns as a second argument to Jam::insert().

<?php
Jam::insert('client', array(
	'client.title',
	'username' => 'patrick76'
))
	->values(array(
		'Patrik',
		'patrik76'
	))
	->execute()
// INSERT INTO clients VALUES (`title`, `username`) VALUES ('Patrik', 'patrik76')
?>

You could pass any Database_Query (or Database_Query_Builder) instance to values(). This way you would insert values from a sub-query.

<?php
Jam::insert('client', array(
	'client.title',
	'username' => 'patrick76'
))
	->values(Jam::all('user')
		->where('type', '=', 'client')
		->select('name', 'username')
		->limit(1))
	->execute()
// INSERT INTO clients VALUES (`title`, `username`) VALUES ('Patrik', 'patrik76')
?>

Inserting multiple records

You can pass multiple arrays to values() to insert multiple rows.

<?php

// Insert multiple rows
Jam::insert('client', array('title', 'username'))
	->values(array(
		'Patrik',
		'patrick76'
	), array(
		'Martin',
		'martin87'
	))
	->execute();
// INSERT INTO clients(title, username) VALUES ('Patrik', 'patrik76'), ('Martin', 'martin87');
?>

Insert Select

You can also perform INSERT ... SELECT queries with Jam_Builder. This is a bit more tricky but here it goes:

<?php
Jam::insert('client')
	->columns('title', 'username')
	->select('order.name', 'order.id')
	->from('order')
	->where('order.price', '=', 1);
// INSERT INTO clients(title, username) SELECT orders.name, orders.id FROM orders WHERE orders.price = 1;
?>

Delete queries

You can perform delete queries with Jam::delete() query builder. It has most of the select methods and some methods for inserting rows.

Jam::delete('client')->where('client.title', 'LIKE', 'Pat%')->execute();
// DELETE FROM clients WHERE clients.title LIKE 'Pat%'

Adding unmapped data

Sometimes you want to store arbitrary data on the builder that will be passed on to future requests, usually used in behaviors. You can achieve this with the params() method. Its basically a getter / setter on an array data store - nothing more.

$builder = Jam::all('client');
$builder->params('some_value', 10);
$builder->params(array('other_value' => 40));

echo $builder->params('some_value');         // 10
echo $builder->params();                     // array('some_value' => 10, 'other_value' => 40)