Firebird Documentation IndexFirebird 3.0 Developer's GuideDeveloping Web Applications with PHP and Firebird → Creating Models
Firebird Home Firebird Home Prev: Creating a ProjectFirebird Documentation IndexUp: Developing Web Applications with PHP and FirebirdNext: Transactions

Creating Models

Table of Contents

A Tool for Model-making
Invoice Items Model
How Laravel Manages Data

The Laravel framework supports the Eloquent ORM, an elegant and simple implementation of the ActiveRecord pattern for working with a database. Each table has a corresponding class model that works with it. Models enable the application to read data from tables and write data to a table. The model we are going to work with complies fully with the one illustrated earlier, at the beginning of the Database chapter.

A Tool for Model-making

To create a model for our customer entity, Laravel offers the artisan command that makes it relatively easy. This is the command for creating a model template:

  php artisan make:model Customer
        

We want to change the model so that it looks like this:

namespace App;

use Firebird\Eloquent\Model;

class Customer extends Model
{
  /**
   * Table associated with the model
   *
   * @var string
   */
  protected $table = 'CUSTOMER';

  /**
   * Primary key of the model
   *
   * @var string
   */
  protected $primaryKey = 'CUSTOMER_ID';
  /**
   * Our model does not have a timestamp
   *
   * @var bool
   */
  public $timestamps = false;

  /**
   * The name of the sequence for generating the primary key
   *
   * @var string
   */
  protected $sequence = 'GEN_CUSTOMER_ID';
}
        

Notice that we use the modified Firebird\Eloquent\Model model from the sim1984/laravel-firebird package as the basis. It allows us to use the sequence specified in the $sequence attribute to generate values for the primary key ID.

We create a model for products—Product—in the same way.

namespace App;

use Firebird\Eloquent\Model;

class Product extends Model
{
  /**
   * Table associated with the model
   *
   * @var string
   */
  protected $table = 'PRODUCT';

  /**
   * Primary key of the model
   *
   * @var string
   */
  protected $primaryKey = 'PRODUCT_ID';

  /**
   * Our model does not have a timestamp
   *
   * @var bool
   */
  public $timestamps = false;

  /**
   * The name of the sequence for generating the primary key
   *
   * @var string
   */
  protected $sequence = 'GEN_PRODUCT_ID';
}
        

Now, a model for the invoice header:

namespace App;

use Firebird\Eloquent\Model;

class Invoice extends Model {

  /**
   * Table associated with the model
   *
   * @var string
   */
  protected $table = 'INVOICE';

  /**
   * Primary key of the model
   *
   * @var string
   */
  protected $primaryKey = 'INVOICE_ID';

  /**
   * Our model does not have a timestamp
   *
   * @var bool
   */
  public $timestamps = false;

  /**
   * The name of the sequence for generating the primary key
   *
   * @var string
   */
  protected $sequence = 'GEN_INVOICE_ID';

  /**
   * Customer
   *
   * @return \App\Customer
   */
  public function customer() {
    return $this->belongsTo('App\Customer', 'CUSTOMER_ID');
  }
  /**
   * Invoice lines
   * @return \App\InvoiceLine[]
   */
  public function lines() {
    return $this->hasMany('App\InvoiceLine', 'INVOICE_ID');
  }

  /**
   * Payed
   */
  public function pay() {
    $connection = $this->getConnection();
    $attributes = $this->attributes;
    $connection->executeProcedure('SP_PAY_FOR_INOVICE',
                                  [$attributes['INVOICE_ID']]);
  }
}
        

You'll observe some additional functions in this model. The customer function returns the customer that relates to the invoice header via the CUSTOMER_ID field. The belongsTo method is used for establishing this relation. The name of the model class and the name of the relation field are passed to this method.

The function lines returns items from the invoice that are represented by a collection of InvoiceLine models, described later. To establish the one-to-many relation in the lines function, the name of the class model and the relation field are passed to the hasMany method.

You can find more details about specifying relations between entities in the Relationships section of the Laravel documentation.

The pay function performs payment of an invoice by calling the stored procedure SP_PAY_FOR_INVOICE, passing the identifier of the invoice header. The value of any field (model attribute) can be obtained from the attribute attribute. The executeProcedure method calls the stored procedure.

Note

This method is available only when the sim1984/laravel-firebird extension is used.

Invoice Items Model

Table of Contents

Operations

Now we are going to create a model for items in an invoice:

namespace App;

use Firebird\Eloquent\Model;
use Illuminate\Database\Eloquent\Builder;

class InvoiceLine extends Model {

  /**
   * Table associated with the model
   *
   * @var string
   */
  protected $table = 'INVOICE_LINE';

  /**
   * Primary key of the model
   *
   * @var string
   */
  protected $primaryKey = 'INVOICE_LINE_ID';

  /**
   * Our model does not have a timestamp
   *
   * @var bool
   */
  public $timestamps = false;

  /**
   * The name of the sequence for generating the primary key
   *
   * @var string
   */
  protected $sequence = 'GEN_INVOICE_LINE_ID';

  /**
   * Array of names of computed fields
   *
   * @var array
   */
  protected $appends = ['SUM_PRICE'];

  /**
   * Product
   *
   * @return \App\Product
   */
  public function product() {
    return $this->belongsTo('App\Product', 'PRODUCT_ID');
  }

  /**
   * Amount by item
   *
   * @return double
   */
  public function getSumPriceAttribute() {
    return $this->SALE_PRICE * $this->QUANTITY;
  }

  /**
   * Adding a model object to the database
   * Override this method, because in this case, we work with a stored procedure
   *
   * @param \Illuminate\Database\Eloquent\Builder $query
   * @param array $options
   * @return bool
   */
  protected function performInsert(Builder $query, array $options = []) {
    if ($this->fireModelEvent('creating') === false) {
      return false;
    }
    $connection = $this->getConnection();
    $attributes = $this->attributes;
    $connection->executeProcedure('SP_ADD_INVOICE_LINE', [
      $attributes['INVOICE_ID'],
      $attributes['PRODUCT_ID'],
      $attributes['QUANTITY']
    ]);
    // We will go ahead and set the exists property to true,
    // so that it is set when the created event is fired, just in case
    // the developer tries to update it during the event. This will allow
    // them to do so and run an update here.
    $this->exists = true;
    $this->wasRecentlyCreated = true;
    $this->fireModelEvent('created', false);
    return true;
  }

  /**
   * Saving changes to the current model instance in the database
   * Override this method, because in this case, we work with a stored procedure
   *
   * @param \Illuminate\Database\Eloquent\Builder $query
   * @param array $options
   * @return bool
   */
  protected function performUpdate(Builder $query, array $options = []) {
    $dirty = $this->getDirty();
    if (count($dirty) > 0) {
      // If the updating event returns false, we will cancel
      // the update operation so developers can hook Validation systems
      // into their models and cancel this operation if the model does
      // not pass validation. Otherwise, we update.
      if ($this->fireModelEvent('updating') === false) {
        return false;
      }
      $connection = $this->getConnection();
      $attributes = $this->attributes;
      $connection->executeProcedure('SP_EDIT_INVOICE_LINE', [
        $attributes['INVOICE_LINE_ID'],
        $attributes['QUANTITY']
      ]);
      $this->fireModelEvent('updated', false);
    }
  }

  /**
   * Deleting the current model instance from the database
   * Override this method, because in this case, we work with a stored procedure
   *
   * @return void
   */
  protected function performDeleteOnModel() {
    $connection = $this->getConnection();
    $attributes = $this->attributes;
    $connection->executeProcedure('SP_DELETE_INVOICE_LINE',
      [$attributes['INVOICE_LINE_ID']]);
  }
}
        

The product function in this model returns the product, actually the App/Product model that was specified as the invoice item. The relation is established through the PRODUCT_ID field by the belongsTo method.

The SumPrice is a calculated field, calculated by the function getSumPriceAttribute. For a calculated field to be available in the model, its name must be specified in the $appends array that stores the names of calculated fields.

Operations

In this model, we redefined the insert, update and delete operations so that they are performed through stored procedures. Along with performing the insert, update and delete operations, these stored procedures recalculate the total in the invoice header. We could have avoided doing that, but then we would have had to modify several models in one transaction. Later, we will examine how to do it that way.

How Laravel Manages Data

Table of Contents

Complex Models

Now let us talk a bit about how to work in Laravel with models for retrieving, inserting, updating and deleting data. Laravel uses the query constructor to manage data. The full description of the syntax and capabilities of this constructor is available at https://laravel.com/docs/5.2/queries. For example, you can execute the following query to retrieve all supplier rows:

$customers = DB::table('CUSTOMER')->get();
        

This query constructor is quite a powerful tool for building and executing SQL queries. You can also direct it to filter, sort and merge tables. For example:

DB::table('users')
  ->join('contacts', function ($join) {
    $join->on('users.id', '=', 'contacts.user_id')->orOn(...);
  })
  ->get()
        

Nevertheless, models are more convenient to work with. You can find the description of Eloquent ORM models and the syntax for querying them at https://laravel.com/docs/5.2/eloquent.

As an example, to retrieve all elements from the collection of customers would require executing the following query:

$customers = Customer::all();
        

This query will return the first 20 customers sorted alphabetically:

$customers = App\Customer::select()
           ->orderBy('name')
           ->take(20)
           ->get();
        

Complex Models

When a model is more complex, its relationships or relationship collections can be retrieved via dynamic attributes. The following query, for example, returns the items of the invoice that has the identifier 1:

$lines = Invoice::find(1)->lines;
          

Records are added by creating an instance of the model, initiating its attributes and saving the model using the save method:

$flight = new Flight;
$flight->name = $request->name;
$flight->save();
          

Updating a record involves finding it, accepting changes to the appropriate attributes and saving it with the save method:

$flight = App\Flight::find(1);
$flight->name = 'New Flight Name';
$flight->save();
To delete a record, involves finding it and calling the delete method.
$flight = App\Flight::find(1);
$flight->delete();
          

The destroy method allows a record to be deleted more rapidly by its key value, without needing to retrieve its instance:

App\Flight::destroy(1);
          

There are other ways of deleting records, for instance, “soft” deletion. You can read more about deletion methods at https://laravel.com/docs/5.2/eloquent#deleting-models.

Prev: Creating a ProjectFirebird Documentation IndexUp: Developing Web Applications with PHP and FirebirdNext: Transactions
Firebird Documentation IndexFirebird 3.0 Developer's GuideDeveloping Web Applications with PHP and Firebird → Creating Models