Models are an abstraction for interfacing with SQL tables via the DB service. Models should be stored in

app/model/

and can be generated via the Disco CLI.

Models extend the Disco\classes\Model class.

For the sake of the examples we will work with these two fictional tables.

 

Band Table

band_id

name
genre

year_formed

Album Table

album_id

band_id

album_name

release_date

number_of_tracks

Creating models from our tables:

class Band extends Disco\classes\Model {

    public $table='band';
    public $ids='band_id';

}//Band

class Album extends Disco\classes\Model {

    public $table='album';
    public $ids=Array('band_id','album_id');

}//Album

You need to configure your DB settings in app/config/config.php to work with Models.

So what we did above was create two classes called Band and Album that extend Disco\classes\Model. They have two important variables that need to be defined:

  • $table- this should be set to the name of the table that the Model should work with
  • $ids- this should list the names of the primary keys on the table

Basic Examples #

// Instantiate the models
$Band  = new Band;
$Album = new Album;



// -----------------------------------------
// Selecting from the models
// -----------------------------------------

// The select method accepts a string or an array of fields to select
// The data method executes the query and returns a \PDOStatement of the results
$bands = $Band->select('name,genre,year_formed')->data();

// Iterate the results
while($band = $bands->fetch()){
    // do something with $band
}

// You can also call asArray to get all the results in array format
$bands = $Band->select(['name','genre','year_formed'])->asArray();

//iterate the results
foreach($bands as $k => $band){
    // do something with $band
}

// You can include aliases
$albums = $Album->select('release_date AS album_release')->data();



// -----------------------------------------
// Create through the models
// -----------------------------------------

// Pass an array of field/value pairs to the insert 
$bandId = $Band->insert(['name' => 'The Nerds', 'genre' => 'disco', 'year_formed' => 2008]);

// Or pass a string with `?` placeholders 
// and a single value or an array of values to be bound
$Band->insert('name=?,genre=?,year_formed=?',['The Nerds','disco', 2008]);



// -----------------------------------------
// Update through the models
// -----------------------------------------

// Pass an array of field/value pairs to the update method 
// Or pass a string with `?` placeholders 
// and a single value or an array of values to be bound
// Use the where method to specify which record should be updated 
// and the finalize method to execute the query
$Album->update(['album_name' => 'The Smartest Nerds'])
    ->where('album_name="The Nerds"')
    ->finalize();



// -----------------------------------------
// Delete through the models
// -----------------------------------------

// Pass an array of field/value pairs to the delete method 
// Or pass a string with `?` placeholders 
// and a single value or an array of values to be bound
$Band->delete(['id' => 1]);

Where Conditions #

Using the where() method and its friends you can specify how to read and update data from your tables in all sorts of ways. Check out the API for a complete list of methods, as we will only touch on the most widely used ones here.

// Standard where condition
// When an array is passed it is assumed that:
// - `AND` between multiple conditions
// - `=` as the comparator for the comparisson
$query = $Band->select('name')
    ->where(['genre' => 'disco', 'year_formed' => 2012])
    ->complile();

echo $query; // `SELECT name FROM band WHERE genre="disco" AND year_formed=2012`


// You can use the where to perform more custom patterns by passing a 
// string and an array
$query = $Band->select('name')
    ->where('(genre=? OR genre=?) AND year_formed < ? AND year_formed > ?)', ['disco', 'indie',  2012, 2008])
    ->complile();

echo $query; // `SELECT name FROM band WHERE (genre="disco" OR genre="indie") AND year_formed<2012 AND year_formed>2008`


// You can chain wheres together to achieve the same effect
$query = $Band->select('name')
    ->where(['genre' => 'disco'])
    ->where('year_formed > ?', 2002)
    ->where('year_formed < ?', 2008)
    ->compile();

echo $query; // `SELECT name FROM band WHERE genre="Disco" AND year_formed > 2002 AND year_formed < 2008`


// Where or condition
// When an array is passed it is assumed that:
// - `OR` between multiple conditions
// - `=` as the comparator for the comparisson
$query = $Band->select('name')
    ->whereOr(['year_formed' => 2011, 'year_formed' => 2012])
    ->complile();

echo $query; // `SELECT name FROM band WHERE year_formed=2011 OR year_formed=2012`



// Where not condition
// When an array is passed it is assumed that:
// - `AND` between multiple conditions
// - `<>` as the comparator for the comparisson
$query = $Band->select('name')
    ->whereNot(['year_formed' => 2011, 'year_formed' => 2012])
    ->complile();

echo $query; // `SELECT name FROM band WHERE year_formed<>2011 AND year_formed<>2012`


// Where not or condition
// When an array is passed it is assumed that:
// - `OR` between multiple conditions
// - `<>` as the comparator for the comparisson
$query = $Band->select('name')
    ->whereNotOr(['year_formed' => 2011, 'year_formed' => 2012])
    ->complile();

echo $query; // `SELECT name FROM band WHERE year_formed<>2011 OR year_formed<>2012`


// Where in condition
// When an array is passed it is assumed that:
// - `IN` as the comparator for the comparisson
$query = $Band->select('name')
    ->whereIn('year_formed', [2011,2012])
    ->complile();

echo $query; // `SELECT name FROM band WHERE year_formed IN (2011,2012)` 


// Where like condition
// When an array is passed it is assumed that:
// - `AND` between multiple conditions
// - `LIKE` as the comparator for the comparisson
$query = $Band->select('name')
    ->whereLike(['genre' => '%hip%'])
    ->complile();

echo $query; // `SELECT name FROM band WHERE genre LIKE '%hip%'` 

Limit Conditions #

// Use the limit method to limit and paginate result sets
$data = $Album->select('album_name')
    ->limit(10)
    ->data();

var_dump($data); // A PDOStatement with 10 albums


// Paginate the result set by passing two arguements, `start` and `limit`
$data = $Album->select('album_name')
    ->limit(50,15)
    ->data();

var_dump($data); // A PDOStatement with 15 albums in positions 50-65

Order Conditions #

// Use the orderBy method to sort results
$data = $Album->select('album_name')
    ->order('release_date')
    ->data();


// Specify ASC (default) (least to greatest) or DESC (greatest to least) to sort 
$query = $Album->select('album_name')
    ->order('release_date DESC')
    ->limit(10)
    ->compile();

echo $query; // `SELECT album_name FROM album ORDER BY release_date DESC LIMIT 10`

Joins & Aliases #

What good would the models be without the ability to perform joins?

The models offer 3 types of joins:

  • join() - INNER JOIN
  • ljoin() - LEFT JOIN
  • rjoin() - RIGHT JOIN

First lets look at aliasing:

// Use the alias() method to alias a model
$data = $Band->alias('b')
    ->select('
        b.band_name,
        b.year_formed
    ')
    ->where('b.year_formed < ?', 2012)
    ->data()

Now lets look at joining the Bands with their Albums:

$query = $Band->alias('b')
    ->select('
        b.band_name,
        b.year_formed,
        a.album_name,
        a.release_date AS album_released_date
    ')
    ->join('Album AS a', 'a.band_id = b.band_id')
    ->where('b.year_formed < ?', 2012)
    ->compile()

echo $query; 
// SELECT b.band_name, b.year_formed, a.album_name, a.release_date AS album_released_date 
// FROM band AS b
// INNER JOIN album AS a ON a.band_id=b.band_id
// WHERE b.year_formed < 2012


// You can bind data just like where conditions on your join conditions
->join('Album AS a', 'a.album_id = ? AND a.release_date < ?', [10, '2012/02/02']);

Real World Example #

// Lets put it all together to execute a real world query

$query = $Band->alias('b')
    ->select('
        b.band_name AS band,
        b.year_formed,
        a.album_name,
        a.release_date AS album_released_date
    ')
    ->join('Album AS a', 'a.band_id = b.band_id AND a.release_date IS NOT NULL AND b.genre = ?', 'indie')
    ->where('b.year_formed < ?', 2012)
    ->order('a.release_date DESC')
    ->limit(5)
    ->compile();

echo $query; 
// SELECT b.band_name AS band, b.year_formed, a.album_name, a.release_date AS album_released_date 
// FROM band AS b
// INNER JOIN album AS a ON a.band_id=b.band_id AND a.release_date IS NOT NULL AND b.genre="indie"
// WHERE b.year_formed < 2012
// ORDER BY a.release_date DESC
// LIMIT 5