Qvc_model Reference

Now can do data_obj->value(‘related.item’) notation. (not any more; use obj->related->item)

Complex Queries

Joins in the data object model

This query will left-join another table:

$query = $this->lang_str_model
->join('trans', 'left', "trans.lang='fr'")
->query();

foreach ($query as $string) {
    echo $string->str."\n";
    echo $string->trans->str."\n";
}

Example of a composite query

Sometimes you really need control over the structure of your query. Here is a good example. Note the “null, false” at the end of the where clause - both are required in this case.

$this
->where('employee_id', $work_session->employee_id)
->where("(`date` > '".$work_session->date."' OR (`date` = '".$work_session->date."' AND `seq` > ".$work_session->seq."))", null, false)
->order_by('date, seq')
->limit(1)
->get();

The above query produces this SELECT statement:

SELECT `work_session`.* FROM `work_session` WHERE `work_session`.`employee_id` = '1' AND (`date` = '2016-12-20' OR (`date` = '2016-12-20' AND `seq` > 1)) ORDER BY `date`, `seq` LIMIT 1;

Extended schema versions

Models that extend a base model can specify an extended schema version, like this:

<?php

class Message_schema extends Document_schema {
        public $ext_version = 1;
}

To delete the table during an upgrade, it is necessary to set $ext_version to -1. The default value is 0, which allows the table for the base model to exist.

Tips and Tricks

Moving a field from one table to another

In this scenario, we want to move a field from one table to another while copying the data. The problem is that in one upgrade cycle, one table wants to delete the field but possibly before the other table has had a chance to copy it. The only reliable way is to ensure that the new field is created first, then the data copied, and finally the old field deleted. The following procedure will do the trick. In the first schema class, do this:

function version_2_up() {
        $this->add('diem_rate', array(
                'type' => 'decimal',
                'size' => '6,2',
                'null' => true,
                'default' => null,
                'comment' => '',
        ));
        // With a schema change, need to re-construct the model
        ci()->employee_project_model->__construct();
        ci()->load->model('project_model');
        $proj_per_diem = ci()->project_model->where('per_diem_rate >', 0)->get_index('per_diem_rate');
        foreach (ci()->employee_project_model->where('employee_id', 1)->query() as $emp_proj) {
                if (isset($proj_per_diem[$emp_proj->project_id])) {
                        $emp_proj->diem_rate = $proj_per_diem[$emp_proj->project_id];
                        $emp_proj->hour_rate = null;
                        $emp_proj->store();
                }
        }
        return true;
}

For the other data model that is giving up the field, register a post-upgrade function:

function version_4_up() {
        $this->register_post_upgrade_func('version_4_post_upgrade');
        return true;
}

function version_4_post_upgrade() {
        $this->drop('per_diem_rate');
}