Elegant Models: Built in Database Query Builder

• Introduction
• Retrieving Results
  • Retrieving All Rows From A Table
• Selects
• Relations
  • Many to Many
  • One to Many
  • One to One
• Where Clauses
  • Simple Where Clauses
  • And Statements
  • Or Statements
• Join Clauses
  • Join and Inner Join
  • Left Join and Right Join
  • Full Join
  • Cross Join
• Pagination

Introduction

Elegant models have a built in database query builder that provides a convenient, fluent interface to creating and running database queries within models. It can be used to perform most database operations in your application and works on all supported database systems.

The Elegant query builder uses PDO parameter binding to protect your application against SQL injection attacks. There is no need to clean strings being passed as bindings.

Retrieving Results

Retrieving All Rows From A Table

You may use the all method in order to request all rows and all columns within a Model's database table. The all method in QueryBuilder returns a proper query string for the Model to execute the query.

$books = $this->all();

Selects

Specifying A Select Clause within Get Parameter

Of course, you may not always want to select all columns from a database table. You can specify what columns you want in the get() method as done in the following code snippet below:

$books = $this->get(array('author_name', 'description', 'title'));

Relations

Many To Many

Many-to-many relations are slightly more complicated than oneToOne and oneToMany relationships. An example of such a relationship is a book with many authors, where the authors are also shared by other books. For example, many books may have the author of "Stephen King". To define this relationship, three database tables are needed: books, authors, and author_book. The author_book table is derived from the alphabetical order of the related model names, and contains the book_id and author_id columns.

<?php
include_once("Elegant/Model.php");
class Book extends Model {
    public function __construct() {  
        parent::__construct($this);
    }

    public function getBooksAuthors()
    {
        $foreign_table_name        = 'authors';
        $foreign_table_primary_key = 'author_id';
        $junction_table_name       = 'books_authors';
        $primary_table_name        = 'books';
        $primary_table_primary_key = 'book_id';
        
        return $this->manyToMany( $foreign_table_name, $junction_table_name, 
                            $foreign_table_primary_key, $foreign_table_primary_key)->get();
    }

}
?>

One To Many

A "one-to-many" relationship is used to define relationships where a single model owns any amount of other models. For example, a customer may have placed many orders. Like all other Elegant relationships, one-to-many relationships are defined by placing a function on your Elegant model:

<?php
include_once("Elegant/Model.php");
class Customer extends Model {
	public function __construct() {
            parent::__construct($this);
	}

	public function getOrders() {
            $foreign_table = 'orders';
            $primary_key   = 'id';
            $foreign_key   = 'customer_id';
            /**
             *  Get the orders associated with the customer.
             */
            return $this->oneToMany($foreign_table, $primary_key, $foreign_key)->get();
        }
}
?>

One To One

A one-to-one relationship is a very basic relation. For example, a Book model might be associated with one Genre. To define this relationship, we place a genre method on the Book model. The genre method should call the oneToOne method and return its result:

<?php
include_once("Elegant/Model.php");
class Book extends Model {
	public function __construct() {
            parent::__construct($this);
	}

	public function genre() {
            /**
             * Get the genre associated with the book.
             */
            return $result = $this->oneToOne('genres','genre_id','id')->get();   
	}
}

Where Clauses

Simple Where Clauses

You may use the where method on a query builder instance to add where clauses to the query. The most basic call to where requires three arguments. The first argument is the name of the column. The second argument is an operator, which can be any of the database's supported operators. Finally, the third argument is the value to evaluate against the column.

<?php
include_once("Elegant/Model.php");
class Book extends Model {

    public function __construct() {  
        $this->table_name = 'books';
        parent::__construct($this);
    }

    public function getBook($title) {
        return $this->where('title', '=', $title)->get();
    }
}
?>

And Statements

You may chain where constraints together with one or more calls to where in order to add AND clauses to the query:

$order_model = new Order();
$order_model->where('customer_id','=',1)
            ->where('amount', '>', 100)
            ->where('amount', '<', 400)
            ->get();
/**
 *  This is similar to the sql query:
 *      SELECT * FROM orders WHERE customer_id = 1 AND amount > 100 AND amount < 400;
 */

Or Statements

You may chain where constraints together with one or more calls to orWhere in order to add OR clauses to the query. The orWhere method accepts the same arguments as the where method:

$order_model = new Order();
$order_model->where('customer_id','=',1)
            ->orWhere('amount', '=', 125)
            ->orWhere('amount', '>', 400)
            ->get();
/**
 *  This is similar to the sql query:
 *      SELECT * FROM orders WHERE customer_id = 1 OR amount = 125 OR amount > 400;
 */

Join Clauses

Join and Inner Join

Inner join and join will result in a table which is based on the realtion of two common columns

$customer = new Customer();
$ft = 'orders';
$pk = 'Customers.CustomerID';
$op = '=';
$fk = 'Orders.CustomerID';
$results = $customer->innerJoin($ft)->on($pk,$op,$fk)->get();

$customer = new Customer();
$ft = 'orders';
$pk = 'Customers.CustomerID';
$op = '=';
$fk = 'Orders.CustomerID';
$results = $customer->join($ft)->on($pk,$op,$fk)->get();

Left Join and Right Join

A Left Join return all records from the left table matched with records from the right table and vice versa

$customer = new Customer();
$ft = 'orders';
$pk = 'Customers.CustomerID';
$op = '=';
$fk = 'Orders.CustomerID';
$results = $customer->leftJoin($ft)->on($pk,$op,$fk)->get();

$customer = new Customer();
$ft = 'orders';
$pk = 'Customers.CustomerID';
$op = '=';
$fk = 'Orders.CustomerID';
$results = $customer->rightJoin($ft)->on($pk,$op,$fk)->get();

Full Join

A Full Join return all records whether there is a match in the left or right

$customer = new Customer();
$ft = 'orders';
$pk = 'Customers.CustomerID';
$op = '=';
$fk = 'Orders.CustomerID';
$results = $customer->fullJoin($ft,$pk,$op,$fk)->get();

Cross Join

A Cross Join will return the cartesian product of two tables result in the number of rows from left table to be multiplied by the number of rows in the right table

$customer = new Customer();
$ft = 'orders';
$results = $customer->crossJoin($ft)->get();

Pagination

The paginate function will return an array of data indexed at page 1 to the last page which can be accessed through the key 'last_page' . It can be combined at the end of a clause.

session_start();
$_SESSION['product-row-count'] = 5;
$_SESSION['product-order-by'] = 'ProductID';
$_SESSION['product-order-desc'] = FALSE;
$_SESSION['page'] = 1;
$p = $this->product_model
           ->orderBy($_SESSION['product-order-by'], $_SESSION['product-order-desc'])
           ->paginate(5);
session_write_close();
$products = $p[1];
$products_last_page = $p['last_page'];