Documentation

Query manager

The Query manager is the extension of the CodeIgniter Query builder.

Here are some examples:

Example 1: simple select with one result

    $qm = new Query_manager();
    $qm->select('id AS article:id, created_at AS article:created_at')
       ->from('article')
       ->where('id', 1);

    $qr = $qm->get();
    $row = $qr->row();
    $qm->convert_row($row);
    var_dump($row->{'article:id'});
    var_dump($row->{'article:created_at'});
                

Example 2: simple select with multiple results

    $qm = new Query_manager();
    $qm->select('id AS article:id, created_at AS article:created_at')
       ->from('article')
       ->where_in('id', [1, 5, 8]);

    $qr = $qm->get();
    foreach ($qr->result() as $row) {
        $qm->convert_row($row);
        var_dump($row->{'article:id'});
        var_dump($row->{'article:created_at'});
    }
                

Example 3: select with 'join' and 'alias'

    $qm = new Query_manager();
    $qm->select('alias_article.id AS alias_article:id, article_tag.created_at AS article_tag:created_at')
       ->from('article AS alias_article')
       ->join('article_tag', 'article_tag.article_id = alias_article.id')
       ->join('tag AS alias_tag', 'alias_tag.id = article_tag.tag_id')
       ->where('alias_tag.content', 'tag_2')>
    $qr = $qm->get();

    foreach ($qr->result() as $row) {
        $qm->convert_row($row);
        var_dump($row->{'alias_article:id'});
        var_dump($row->{'article_tag:created_at'});
    }
                

Example 4: insert

    $qm = new Query_manager();
    $qm->table('comment')
       ->set('content', 'The content of the comment')
       ->set('article_id', 1)
       ->set('person_id', 1)
       ->set('created_at', Pgsql_timestamptz::create_from_format('Y-m-d|', '2019-04-03'))
       ->insert();
                

Example 5: update

    $qm = new Query_manager();
    $qm->table('comment')
       ->where('created_at', Pgsql_timestamptz::create_from_format('Y-m-d|', '2019-04-03'))
       ->set('content', 'The new content of the comment')
       ->update();
                

Example 6: delete

    $qm = new Query_manager();
    $qm->table('comment')
       ->where('created_at', Pgsql_timestamptz::create_from_format('Y-m-d|', '2019-04-03'))
       ->delete();
                

The method 'convert_row()' converts each item of the row given the array $config['artefact_data_conv'] in ./application/config/config_artefact.php.
Each item must have the format 'alias_name:field_name' (or 'table_name:field_name' if there is no alias).
If you want the method 'convert_row()' not to convert an item, you have to write ':item_name'.
Example: $qm->select('article_tag.created_at AS :created_at')

All the functions available in the CodeIgniter Query builder are also available in the Query manager (where(), group_start(), like() ...).

The Query manager automatically converts the data given the array $config['artefact_data_conv'] in ./application/config/config_artefact.php.

All these functions also exist in a 'simple_' version which means that no conversion is processed (simple_where(), simple_like() ...).

The function insert() accepts a parameter $with_insert_id (default value is FALSE).
If this parameter is set to TRUE, the return value is the insert id.

The functions update(), replace() and delete() accept a parameter $with_affected_rows (default value is FALSE).
If this parameter is set to TRUE, the return value is the number of affected rows.

The function reset() resets the Query_manager instance.

Databubble

A Databubble instance contains a set of Model instances and Association instances.

The Databubbles_warehouse library manages the Databubble instances.

Example 1:

    $CI =& get_instance();
    $main_databubble = $CI->databubbles_warehouse->create_databubble('main');
                

There are two user fonctions:

• insert_model($model_short_name, $data)
  Insert in the database the model whose name is $model_short_name and data are $data
  Add the created instance in the current databubble
  
  

  Example 1:

      $paragraph = $main_databubble->insert_model(
          'Paragraph',
          array(
              'content'   => 'The content of the paragraph',
              'position'  => 2,
              'article'   => $article,
          )
      );
                          

  The inserted Model is a Paragraph.
  
  'content' and 'position' are some basic properties.
  'article' is an association property.
  
  If a property is the 'one' side of a one-to-many association property (or one-to-one association property), you have to provide a Model instance.
  
  If a property is the 'many' side of a one-to-many association property, you have to provide an array of Model instances.
  
  If a property is a many-to-many association property, you have to provide an array of data set that corresponds to the related Association class
  
  

  Example 2:

      $tag = $main_databubble->insert_model(
          'Tag',
          array(
              'content'   => 'tag_3',
              'articles'  => array(
                  array(
                      'article'     => $article_1,
                      'created_at'  => new Pgsql_timestamptz(),
                  ),
                  array(
                      'article'     => $article_2,
                      'created_at'  => new Pgsql_timestamptz(),
                  ),
              ),
          )
      );
                          

• delete_model($model_instance)
  Delete the model instance $model_instance from the database and from the current databubble
  
  

  Example 1:

      $main_databubble->delete_model($paragraph);
                          

  The deleted Model is the Paragraph $paragraph.

Finder

The goal of the Finder class is to retrieve data from the database, build the Model instances and Association instances, and put them in a Databubble instance.

Example 1: Get the article whose id is 1

    $finder = new Finder('Article AS[alias_article]');
    $finder->where('alias_article:id', 1);
    $article = $finder->first();
                

Example 2: Get the article whose id is 1 (the short version)

    $article = Article::find(1);
                

It also works with an array of ids

    $articles = Article::find([1, 5, 8]);
                

Example 3: Get the article whose id is 1, specifying the Databubble instance

    $CI =& get_instance();

    $main_databubble = $CI->databubbles_warehouse->create_databubble('main');

    $finder = new Finder('Article AS[alias_article]');
    $finder->where('alias_article:id', 1);
    $article = $finder->first($main_databubble);
                

Example 4: Get the article whose id is 1, specifying the Databubble instance (the short version)

    $CI =& get_instance();

    $main_databubble = $CI->databubbles_warehouse->create_databubble('main');

    $article = Article::find(1, $main_databubble);
                

Example 5: Get all the articles

    $articles = Article::all();
                

Example 6: Get all the articles, specifying the Databubble instance

    $CI =& get_instance();

    $main_databubble = $CI->databubbles_warehouse->create_databubble('main');

    $articles = Article::all($main_databubble);
                

Example 7: Get the paragraphs whose position is 2, using alias

    $finder = new Finder('Paragraph AS[alias_paragraph]');
    $finder->where('alias_paragraph:position', 2);
    $paragraphs = $finder->get();
                

Example 8: Get the root folders (i.e., folders that have no parent folder), using alias and recursivity

Folder is a Model which has a recursive property 'subfolders' (the opposite property is 'parent_folder').
The recursive field in the database is 'parent_id'.
The definition of the association in the array $config['artefact_mapping_associations'] of the file ./application/config/config_artefact.php is:

    array(
        'associates' => array(
            array(
                'model'      => 'Folder',
                'property'   => 'subfolders',
                'dimension'  => 'many',
            ),
            array(
                'model'      => 'Folder',
                'property'   => 'parent_folder',
                'dimension'  => 'one',
                'field'      => 'parent_id',
            ),
        ),
    ),
                

And the code:

    $folder_table_depth = Folder::get_table_depth('subfolders');
    // equivalent to: $folder_table_depth = $this->db->get_table_depth('folder', 'parent_id');

    $finder = new Finder('Folder AS[alias_folder] REC[subfolders|' . $folder_table_depth . ']');
    $finder->where('alias_folder:parent_id', null);
    $folders = $finder->get();
                

Example 9: Order the results, limit the number of Model instances and set an offset

    $finder = new Finder('Article AS[alias_article]');
    $finder->order_by('alias_article:created_at', 'DESC')
           ->limit(5)
           ->offset(2);
    $articles = $finder->get();
                

Example 10: Get the articles with the author

    $finder = new Finder('Article');
    $finder->with('author');
    $articles = $finder->get();
                

Example 11: Get the articles with the author and the comments

    $finder = new Finder('Article');
    $finder->with(array(
        'author',
        'comments',
    ));
    $articles = $finder->get();
                

Example 12: Get the articles with the author and the comments and the author of each comment

    $finder = new Finder('Article');
    $finder->with(array(
        'author',
        'comments' => 'person',
    ));
    $articles = $finder->get();
                

Example 13: Get the articles with a subset of the comments

    $finder = new Finder('Article');
    $finder->with('comments AS[alias_comment]');
    $finder->like('alias_comment:content', 'match');
    $articles = $finder->get();
                

Example 14: Get the articles (with a many-to-many Association)

'Person' is the abstract class of 'Author'.
The property 'comments' is in 'Person' and not in 'Author', this is why we have to write:
'author' => array('.<Person:comments'),

If it were the other way around, we would have written:
'person' => array('.>Author:comments'),

'tags' is a many-to-many association property.
'alias_tag' is the alias for the Model 'Tag'.
'alias_article_tag' is the alias for the Association 'Article_Tag'.

    $finder = new Finder('Article');
    $finder->with(array(
        'author' => array('.<Person:comments'),
        'tags AS[model:alias_tag, association:alias_article_tag]',
    ));
    $finder->like('alias_tag:content', 'match', 'after');
    $finder->where(
        'alias_article_tag:created_at >=',
        Pgsql_timestamptz::create_from_format('Y-m-d|', '2019-01-01')
    );
    $articles = $finder->get();
                

Example 15: Get the authors (with a many-to-many Association that links three Models)

    $finder = new Finder('Author');
    $finder->with(array(
        '.<Person:orders AS[models:[Discount:alias_discount, Folder:alias_folder],
            association:alias_discount_folder_person] REC[Folder:subfolders|2]' => 'Folder:articles',
    ));
    $finder->where('alias_discount:start_date >=', Pgsql_date::create_from_format('Y-m-d', '2019-01-01'));
    $finder->like('alias_folder:name', 'match');
    $finder->where(
        'alias_discount_folder_person:created_at >=',
        Pgsql_timestamptz::create_from_format('Y-m-d|', '2019-01-01')
    );
    $authors = $finder->get();
                

Model

To create a Model class, click here.

The functions set(), add() and remove() are for the basic properties.

The functions set_assoc(), add_assoc() and remove_assoc() are for the association properties.

Contrary to the functions set_assoc(), add_assoc() and remove_assoc(), the functions set(), add() and remove() require a save() to process the update.

Example 1

    $author = Author::find(1);
    $author->set('password', 'test1234');
    $author->set('is_admin', false);
    $author->save();
                

Example 2

    $author = Author::find(1);
    $author->add('phones', '44.44.44.44.44')
           ->save();
                

Example 3

    $author = Author::find(1);
    $author->remove('phones', '33.33.33.33.33')
           ->save();
                

Example 4

    $new_title = $main_databubble->insert_model(
        'Title',
        array(
            'content' => 'The content of the new title',
        )
    );

    $article->set_assoc('title', $new_title);
                

Example 5

    $new_title = $main_databubble->insert_model(
        'Title',
        array(
            'content' => 'The content of the new title',
        )
    );

    $new_title->set_assoc('article', $article, false);
    // In this example, a save() is required because in the previous line,
    // we set the third parameter $auto_commit to false.
    $new_title->save();
                

Example 6

    $comment = $main_databubble->insert_model(
        'Comment',
        array(
            'content'     => 'The content of the comment',
            'person'      => $person,
            'created_at'  => new Pgsql_timestamptz(),
        )
    );

    $article->add_assoc('comments', $comment);
                

Example 7: many-to-many Association

    $article->add_assoc(
        'tags',
        array(
            'tag'         => $tag,
            'created_at'  => new Pgsql_timestamptz(),
        )
    );
                

Example 8

    $article->remove_assoc('comments', $comment);
                

Example 9: many-to-many Association

    $article->remove_assoc(
        'tags',
        array(
            'tag' => $tag,
        )
    );
                

Example 10: many-to-many Association that links three Models (Discount, Folder and Person)

    $discount->remove_assoc(
        'orders',
        array(
            'folder'  => $folder,
            'person'  => $person,
        )
    );
                

Association

To create an Association class, click here.

Regarding the functions, it is identical to the Model, except that only the functions set(), add() and remove() are available.

Enum model

An Enum model is a special type of Model.
It is similar to the 'ENUM' field type in a database.
So, the data of an Enum model are immutable, except when you upgrade the application.

An Enum model always has at least two properties: 'id' and 'name'.

The property 'name' refers to an internal name, so it is recommended not to use spaces or special characters.
If you want to store a name that will be displayed, you can add an extra field (for example 'displayed_name').

Instead of using the 'ENUM' field type, you should use the Enum_model concept because it is more flexible and scalable (you can easily add a new property).

You can find a full example here.
In this example, the Enum model 'Status' has an extra property 'color'.

An Enum model property (for example the property 'status' in the Model Article) is considered as a basic property (and not as an association property), so it is automatically retrieved from the database (no need to specify something in the function with()) and it is recommended not to suffix the related database field with '_id'.