Active Record

• Query Builder
• Dynamic Finders
• Create
• Read
• Update
• Delete

Basic CRUD

CRUD defined by Wikipedia:

CRUD is the day to day task for every developers. Writing queries and running against the database may be painful at some point of time for developers, where Cygnite Framework takes your pain and provides you convenient way of running database queries with the ActiveRecord and Query builder. Cygnite Framework makes your Create, Read, Write, Delete operations extremely simple and expressive.

---

Create or Save Records into database

This is where Cygnite makes your job so simple, it follows activerecord style. Every model class act as a database table object. Save records into a table using instance of your model. Here we register a new user for our blog by simply instantiating a object of Users model and finally save it into a table.

 use Apps\Models\Users;

 $user = new Users();
 $user->first_name = 'Sanjoy';
 $user->last_name = 'Dey';
 $user->email_address = '[email protected]';
 $user->gender = 'Male';
 $user->save();

The above code is equivalent of writing.

  #sql => INSERT INTO `users` (first_name, last_name, email_address, gender) VALUES('Sanjoy', 'Dey', '[email protected]', 'Male');

Isn't much easier to create a record into the database.

We recommend you to have auto increment primary field name as 'id'.

###Getting Last Inserted Id

Get the last inserted id by your user object "$user->id;";

Model Events

Cygnite model has various model events, model events are allowing you to hook various functionality. Model events will get triggered before and after database manipulation.

Following methods are triggered by model

 beforeCreate, afterCreate, beforeUpdate, afterUpdate, beforeSelect, afterSelect, 
 beforeDelete, afterDelete etc.

When you try to save a new record into database beforeCreate() will trigger by activerecord and afterCreate() will be triggered after saving record into database. Similarly other methods are called by Cygnite ActiveRecord.

Simply you need to write a method into your model as mentioned above, Cygnite ActiveRecord knows when to trigger those events.

---

Read or Select From Table

Retrieving rows from a table is very easy and simple by using Cygnite Dynamic finders and Query builder.

Query Builder

Select a particular collumn or all collumns of the table with single line of code.

 $data = $this->select('first_name,last_name,email_address')->findAll();
 or
 $data = $this->select('*')->findAll();

Using Where Condition

Single where method does multiple tasks. For example.

Using Single Where condition

  $this->where('name', 'Sanjoy', '=');
  #sql => WHERE `name` = 'Sanjoy';

Using multiple where conditions with AND

  //Where with AND conditions
  $where = array(
               'name =' => 'Sanjoy',
               'id >'  =>  '4'
         );
  $this->where($where);
  #sql => WHERE `name` = 'Sanjoy' AND `id` > 4;

  $whereAnd = array(
                  'entry_date LIKE'  => '%2013-08-23%',
                  'name ='           => 'Sanjoy'            
             );
  $this->where($whereAnd);
    
  $whereLike = array(
              'name LIKE' => '%Cygnite',
              'comment LIKE'    => '%ORM%'            
           );
  

  $whereDate = array(
              'entry_date >=' => '2013-08-24 05:00:00',
              'entry_date <=' => date('Y-m-d'),            
           );
  $this->where($whereDate);
  #sql => WHERE `entry_date` >= '2013-08-24 05:00:00' AND `entry_date` <= CURDATE();

Using Where In

  $whereIn = array(
              'name IN' => '#"Cygnite","Orm","ActiveRecord"',            
             );

  $this->where($whereIn);
  #sql => WHERE `name` IN ('Cygnite', 'ORM', 'ActiveRecord');

[Note: Limitation - You cannot single condition as an array format into the where method. It will give you unknown result.]

Using Order By

 $this->orderBy('id','DESC');

Using Group By

 $this->groupBy(array('name'));

Group By with multiple fields

 $this->groupBy(array('name','id'));

Limit Your Queries

 $this->limit(3);
 #sql => LIMIT 0,3

 $this->limit(1,3);
 #sql => LIMIT 1,3

All together in a single line

You can now execute queries in a single line of code, as shown below.

  $this->select("first_name, last_name, email_address")
       ->where($where)
       ->groupBy(array('name'))
       ->orderBy('id','DESC')
       ->limit(3)
       ->findAll();

Resultset types

By default Cygnite ActiveRecord will return you the results in the form of object. You can also get the results in different ways, simply by passing result type into findAll() method. Currently there are GROUP, BOTH, JSON, ASSOC, OBJ, COLUMN different types are supported by ActiveRecord.

For example $this->select('*')->findAll('JSON');

[Note: $this should be used only if you are building query inside in your model class. If you are trying to access the query builder from the controller then you need to create an model instance to access fluent query builder. $user->select("*")->findAll(); ]

---

Updating Record in a Table

We access model instance to update the records in a table. It's very simple than you think. It works similar as INSERT/CREATE works.

 use Apps\Models\Users;

 $user = Users::find(20);
 
 $user->first_name = 'Shane';
 $user->last_name = 'Watson';
 $user->email_address = '[email protected]';
 $user->gender = 'Male';
 $user->save();

You will still have last id echo $user->id; //20

---

Delete

Deleting a record from the Table

 use Apps\Models\Users;

 $user = new Users();
 $user->trash(23);

Deleting multiple records from the Table

 $user->trash(array(21,22,23,34), true);

We are still under the active development to bring much more features.