Generic DAO

1Presentation

As you could quickly see in the introduction, Temma is able to automatically create DAOs. The simplest case is when you create a controller that needs to access only one table in the database.
It is then sufficient that this controller has a protected attribute named $_temmaAutoDao, set to the boolean value true. true.

If we take the example given in the introduction: 

class Article extends \Temma\Web\Controller {
    // tell that the DAO must be created automatically
    protected $_temmaAutoDao = true;
}

By doing this, Temma will create an object of type \Temma\Dao\Dao, which will be automatically configured to facilitate the manipulation of data stored in a table whose name is identical to that of the controller (article).
This object will be available via the $this->_dao attribute.

2Advanced configuration

2.1General principle

By default, DAOs assume several things:

  • The connection to the database is configured with a data source named db.
  • If the cache is accessible (configured with a data source named cache), it is used to speed up data access.
  • The table is in the database on which the connection is opened.
  • The name of the table matches the name of the controller.
  • The field containing the primary key is called id.
  • All the fields in the table must be retrieved when accessed.
  • When we get the fields from the table, we get the names as they are in the database.

To be able to configure the operation of the DAO (deactivate the cache, specify a different database or table name, rename the fields, ...), it is advisable to write a custom DAO object, which can contain the specific information of which you need.

2.2Specific configuration

However, if you are in the situation where a controller needs to be able to manipulate data by finely configuring the behavior of the DAO, but you do not want to create a custom DAO object, it is possible to provide the parameters directly in the controller, by filling in an associative array.
Note that this technique remains limited and should especially not be used in the event that the same table is accessed by several different controllers.

Here is an example of use with specific settings: 

class Article extends \Temma\Web\Controller {
    protected $_temmaAutoDao = [
        'cache'  => false,
        'base'   => 'cms',
        'table'  => 'content',
        'id'     => 'cid',
        'fields' => [
            'cid'     => 'contentId',
                         'title',
                         'login',
            'content' => 'text',
            'date'    => 'creationDate',
                         'status',
        ],
    ];
}
  • Line 2: Definition of the CAD configuration table.
  • Line 3: Disable the cache.
  • Line 4: Definition of the name of the database containing the table.
  • Line 5: Definition of the name of the table.
  • Line 6: Definition of the name of the field containing the primary key.
  • Lines 7 to 14: Definition of an associative array containing the list of fields to be retrieved from the database. If the fields need to be renamed, all you have to do is declare an associative pair whose key is the name of the field in the table, and the associated value is the name as it should be renamed.

The parameters are independent, you do not have to redefine all of them.

3Basic operations

The \Temma\Dao objects offer 6 basic methods:

  • count(): Counts the number of elements in a table.
  • get(): Returns all the information about an element of the table.
  • search(): Search for entries.
  • create(): Add a new element in the table.
  • remove(): Remove one or more elements.
  • update(): Updates one or more records.

These methods are explained in detail below, but first we'll see how the search criteria and sort criteria are constructed, which can be used in some of these methods.

4Search criteria

DAOs provide a mechanism for easily composing search filters. For that, you have to create a criterion, which can be passed as a parameter of certain methods.

The simplest type of criterion is an associative array whose keys correspond to table fields, and whose values correspond to those that will be searched for in the selected rows.

Alternatively, you can create a criterion using the criteria() method, which creates an object of type \Temma\Dao\Criteria.

Then you can combine calls with the following methods:

  • equal(): a field is worth a certain value (possibility of giving a list of possible values)
  • different(): a field is not worth a certain value (possibility of giving a list of possible values)
  • like(): a text field checks a search expression
  • notLike(): a text field does not match a search expression
  • is(): a boolean field is set to "true"
  • isNot(): a boolen field is set to "false"
  • lessThan(): the value of a numeric field is less than a given value
  • greaterThan(): the value of a numeric field is greater than a given value
  • lessOrEqualTo(): the value of a numeric field is less than or equal to a given value
  • greaterOrEqualTo(): the value of a numeric field is greater than or equal to a given value

There are aliases, to simplify writing:

  • has(): equivalent to is()
  • hasNot(): equivalent to isNot()
  • eq(): equivalent to equal()
  • ne(): equivalent to different()
  • lt(): equivalent to lessThan()
  • gt(): equivalent to greaterThan()
  • le(): equivalent to lessOrEqualTo()
  • ge(): equivalent to greaterOrEqualTo()

By default, the criteria are combined using Boolean "AND" operators, which leads to the creation of a data filtering system: only data that meets all the conditions is retrieved. It is possible to combine all the criteria according to the "OR" operator by passing the string "or" as a parameter of the criteria() method.
It is also possible to combine the criteria with Boolean operators thanks to the and() and or() methods, which each take a new criterion object as a parameter.

4.1Example of an associative array

// deletes entries whose category is "article"
// and visibility is "hidden"
$criteria = [
    'category'   => 'article',
    'visibility' => 'hidden',
];
$this->_dao->remove($criteria);

4.2Example equal() and is()

// search for entries which email address equals to "tom@tom.com"
// AND which "free" boolean is true
$critera = $this->_dao->criteria()
           ->equal('email', 'tom@tom.com')
           ->is('free');
$users = $this->_dao->search($criteria);
  • Line 3: Creation of the criterion object.
  • Line 4: Addition of an equality criterion. The email field must have the value tom@tom.com.
  • Line 5: Addition of an equality criterion on a boolean. The free field must be set to "true".
  • Line 6: The criterion object is used with the search() method of the DAO to retrieve the elements corresponding to the criterion.

4.3Example greaterThan() and lessThan()

// search for entries with an age greater than 12
// AND less than 20
$criteria = $this->_dao->criteria()
            ->greaterThan('age', 12)
            ->lessThan('age', 20);
  • Line 3: Creation of the criterion object.
  • Line 4: Addition of a comparison criterion. The age field must be strictly greater than 12.
  • Line 5: Addition of a comparison criterion. The age field must be strictly less than 20.

4.4Example or(), like() and different()

// searches for entries whose email is from Gmail
// OR whose name is not that of a Google creator
$criteria = $this->_dao->criteria('or')
            ->like('email', '%@gmail.com')
            ->different('name', ['Sergey', 'Larry']);
  • Line 3: Creation of the criterion object, by specifying that the criteria will be associated by "OR" operators (and not "AND" as by default).
  • Line 4: Addition of a comparison criterion. The email field must end with the string "@gmail.com".
  • Line 5: Addition of a comparison criterion. The name field must not contain the value "Sergey" or "Larry".

4.5Boolean logic example

// searches for entries where:
// the email is "john@john.com" or "bob@bob.com",
// AND whose age is less than or equal to 12
// OR strictly greater than 24
$criteria = $this->_dao->criteria()
            ->equal('email', ['john@john.com', 'bob@bob.com'])
            ->and_(
                   $this->_dao->criteria('or')
                   ->lessOrEqualTo('age', 12)
                   ->greaterThan('age', 24)
            );
  • Line 5: Creation of the criterion object.
  • Line 6: Adding an equality criterion, providing a list of values.
  • Line 7: Addition of a Boolean operator "AND", which contains a subset of criteria.
  • Line 8: Creation of the subset of criteria. We specify that the different criteria of this set will be linked by “OR” operators.
  • Line 9: Addition of a comparison criterion. The age field must contain a value less than or equal to 12.
  • Line 10: Addition of a comparison criterion. The age field must be strictly greater than 24.

5Sorting criteria

The search() method is likely to return several items, which you may want to sort in a certain order.

It is possible to sort in 3 different ways:

  1. By providing the name of a field, which will perform an ascending sort on the values of that field. If the field name begins with a hyphen, a descending sort will be performed.
  2. By transmitting an array containing the names of the fields on which the sort must be performed. By default, sorting is ascending (from smallest value to largest), but it is possible to specify a descending sort by putting a dash in front of the field name. It's also possible to use an associative array whose key is the field name and value is the string desc.
  3. By providing the false boolean value, to obtain a random sort.
// sorting by date of birth, ascending
$sort = 'birthday';

// sorting by date of birth, descending
$sort = '-birthday';

// sort by date of birth (ascending)
// and the number of points (descending)
$sort = ['birthday', '-points'];

// equivalent to the previous one
$sort = [
    'birthday',
    'points' => 'desc'
];

// equivalent to the previous one
$sort = [
    'birthday' => 'asc',
    'points'   => 'desc'
];

// random sort
$sort = false;

6Methods

6.1count()

count(null|array|\Temma\Dao\Criteria $criteria=null) : int

If this method is called without parameters, it returns the total number of elements in the table.

If it is called with a criterion object or array as a parameter, it returns the number of elements which correspond to the criteria.

Example of use:

// get the total number of elements in the table
$nbr = $this->_dao->count();

// get the number of users named "Bob" (array)
$nbr = $this->_dao->count(['name' => 'Bob']);

// get the number of users named "Bob" (object)
$nbr = $this->_dao->count(
    $this->_dao->criteria()->equal('name', 'Bob')
);

6.2get()

get(int|string|array|\Temma\Dao\Criteria $id) : array

This method returns all the information about a record in the table, whose primary key identifier is passed as a parameter. The data is returned in an associative array, which lists tuples whose key is the name of the field.

A search criterion can be supplied as a parameter instead of a primary key. If this criterion retrieves several records, only the first will be returned.

If a list of fields was provided when the DAO was created, only the fields in question are returned. If this list included renaming the fields, the keys of the associative array are modified accordingly.

Example of use:

// get the information about the article with the identifier 12
$data = $this->_dao->get(12);

// display the title
print($data['title']);

search(null|array|\Temma\Dao\Criteria $criteria=null, null|false|string|array $sort=null,
       ?int $offset=null, ?int $limit=null) : array

This method is used to retrieve data from multiple rows in the table. It returns a list each element of which is an associative array (the content of which is identical to what the get() method returns, see above).
The first parameter is a search criterion, as explained earlier in this page. If it is not supplied, or passed to null, the method will take all rows from the table.
The second parameter contains sorting options, as explained earlier on this page.
The third parameter can contain the number of the first element to return (starting at zero).
The fourth parameter can contain the number of elements to return.

Example of use:

// search for the 5 most recent articles,
// among all those written since January 1, 2000
$articles = $this->_dao->search(
    $this->_dao->criteria()->greaterThan('date', '2011-01-01'),
    '-date',
    null,
    5
);

// display the titles of all the retrieved articles
foreach ($articles as $article)
    print($article['title']);

// search for 3 random articles
$articles = $this->_dao->search(null, false, null, 3);

6.4create()

create(array $data, mixed $safeData=null) : int

This method adds a new row to the table.
An associative array must be given as a parameter, containing key/value pairs corresponding to each field of the table.

The method returns the primary key identifier of the newly created item.

Example of use:

// creation of the new article
$id = $this->_dao->create([
    'title'   => 'Test title',
    'login'   => 'Bob',
    'date'    => date('c'),
    'content' => 'Texte...',
]);

// display the identifier of the new article
print($id);

The method can take an optional second parameter, which is used to avoid deadlocks caused by inserts that generate a key duplication.
The parameter can take as value:

  • null: (default value) The query generates an error if there is a key duplication.
  • true: All fields listed in the first parameter will be updated, using the values provided.
  • The name of a field: This field will be updated. If this field is listed in the first parameter, the associated value will be used; otherwise the field will keep the value it has in the database.
  • An associative array containing the fields that will be updated. The keys are the names of the fields, and the associated values will be used to update them.

6.5remove()

remove(null|int|string|array|\Temma\Dao\Criteria $criteria=null) : int

This method is used to delete records from the table.

If it is called without parameters, it erases all the elements.
If a numeric identifier is passed as a parameter, it will be used as the primary key of the element to be deleted.
If a search criterion is passed as a parameter, it is used to choose the elements that will be deleted.

This method returns the number of deleted lines.

Example of use:

// delete the article with the identifier 12
$this->_dao->remove(12);

// delete all articles written by Bob (array)
$this->_dao->remove(['login' => 'Bob']);

// delete all articles written by Bob (object)
$this->_dao->remove(
    $this->_dao->criteria()->equal('login', 'Bob')
);

6.6update()

update(null|int|string|array|\Temma\Dao\Criteria $criteria, array $data=[]) : int

With this method, you can modify the data saved in the table.

If the first parameter is set to null, all elements of the table will be modified.
If a numeric identifier or a string is passed as a parameter, it will be used as the primary key of the modified element.
If a search criterion is passed as a parameter, it will be used to select the elements that will be modified.

The second parameter must contain an associative array, containing key/value pairs corresponding to the fields to update (with a declaration identical to the create() method).

This method returns the number of modified lines.

Example of use:

// rename "Bob" to "Robert" in all his articles (array)
$this->_dao->update(
    ['login' => 'Bob'],
    ['login' => 'Robert']
);

// rename "Bob" to "Robert" in all his articles (object)
$this->_dao->update(
    $this->_dao->criteria()->equal('login', 'Bob'),
    ['login' => 'Robert']
);

7Cache management

Using the cache speeds up reads to the database. Unfortunately, this can have an undesirable effect: All identical queries will return identical results during the caching time. This can be problematic when you run queries that write identically but need to return different results (for example selections whose results are sorted randomly, or if you occasionally need to take up-to-date data).

It is therefore possible to temporarily disable the use of the cache by using the disableCache() method. Reactivation is done using the enableCache() method. These two methods return the instance of their DAO, unless they are given a parameter, which will then be returned.

Example of use:

// deactivate the cache
$this->_dao->disableCache();
// search
$result = $this->_dao->search();
// reactivate the cache
$this->_dao->enableCache();

// same as previous
$result = $this->_dao->disableCache()->search();
$this->_dao->enableCache();

// result identical to the previous ones
// execution order:
// 1. disableCache()
// 2. search(), which is written as a parameter to enableCache()
// 3. enableCache()
// in the end, the result of the search() is retrieved,
// because it is returned by enableCache()
$result = $this->_dao
          ->disableCache()
          ->enableCache($this->_dao->search());