Architecture: Active Record Query Helper

[eluser]Todd Lynch[/eluser]
I am in the process of building a small CMS (will be open source if I ever finish it!) using CodeIgniter along with Active Records.

There is a primary table that stores the main data for each piece of content, and then there are ancillary tables that store specific data about a given piece of content when needed.

For example, there is a metadata table that stores Keywords and Descriptions. Each piece of content could have 0 or 1 rows in the metadata table, depending on if it is needed for that piece of content.

Other ancillary tables store tags, categories, slideshows, etc... each ancillary table has a corresponding controller, model, and view that is used to manage it through an admin panel.

The admin section is working well.

I am now developing a framework for querying the data on the front end. I have no difficult manually writing the queries with Active Records that pull back the proper content. For example, I can write a query that returns all content that has a row in the tags table with a given tag, or all content that is in a given category. What I would like to do, is encapsulate the query building logic into the appropriate models.

I do not believe this is correct, but I would like to be able to do something along these lines:

Code:

$this->db->select();
$this->db->from("content");
$this->tags_model->has_tag("HELP", $this->db);
$this->category_model->has_primary_category("Code Igniter", $this->db);

This would be great because i could write functions like:

Code:

$this->tags_model->has_any_tag(array("help","technology"), $this->db);
$this->tags_model->has_all_tags(array("php","html"), $this->db);

In the model, and then they would always be available. The functions on the models know how to add all of the required joins and wheres to properly build the query. Can I pass $this->db around? Do I need to? Should I move up a level and extend CI_DB_active_record?

What is the proper architecture for a system like this?

Thanks is advance,
Todd Lynch

[eluser]CroNiX[/eluser]
It sounds like what you are wanting is an ORM, and there are several available for CI. I don't use any so I can't recommend any in particular. But DataMapper and GasORM are two that are actively discussed on the site that come to mind. They handle deeper relations than the regular CI Active Record (build on top of AR, I believe...)

[eluser]Todd Lynch[/eluser]
I could look into the ORMs more, but I would really like to avoid that level of abstraction. I have used doctrine in the past, and I think it is too much overhead and still would not work exactly like I want.

I was really hoping to just build a set of utility functions in each model that would make it easier to use active records.

At its simplest, I could have each model have a functions, such as:

$this->tag_model->get_join_string();

$this->tag_model->get_where_clause("param");

That would return the SQL for that clause and I could build the query using those clauses.

[eluser]CroNiX[/eluser]
So basically your own query builder on top of their query builder.

If I'm understanding, then you don't need to pass $this->db around. Its globally available to controllers/models (assuming it was loaded) and libraries (using get_instance()).
If in one method of a controller you do:

Code:

$this->db->where('item', $some_value);

and in another method you do:

Code:

$this->db->where('other_item', $other_value);

and run them both, they both build on the same query. Essentially the same as if you just did:

Code:

$this->db
  ->where('item', $some_value)
  ->where('other_item', $other_value);

So all you really need to pass around are the specific things like your param to get_where_clause(“param”)

You might also look into creating a base model (MY_Model) that has these common methods and then have your other models extend MY_Model (instead of CI_Model) with their own unique methods.

[eluser]Todd Lynch[/eluser]
Thank you, that is what I was looking for. I assumed it was the case. One last question:

<code>
$CI = & get_instance();
$CI->load->database();
$CI->db->select("content");
$some_other_class->add_stuff($id);
</code>

Then in another function, do I have to load the database again? Or do I just do this:

<code>
public function add_stuff($id) {
$CI = & get_instance();
$CI->db->where("somefield", $id);
}
</code>

Is this correct?

[eluser]CroNiX[/eluser]
You only need to use get_instance() when in a LIBRARY or HELPER. All that does is take everything already loaded in CI and assigns it to that variable so you can use it in your own library (since libraries don't extend anything in CI like a controller or model does, it doesn't have CI available within it unless you bring it in using get_instance()).

You only need to load the database once (like in /config/autoload.php). If you load it there, you don't have to load it anywhere else. I'd advise doing that if you are going to be using the db heavily.

In a library, I'd load CI in the construct so you don't have to do it in every method.

Code:

class Something {
  private $CI;  //local property for CI

  public function __construct()
  {
    $this->CI =& get_instance();  //assign CI to the CI property
  }

  public function something_else()
  {
    //get data from an already loaded model (loaded in a controller perhaps)
    return $this->CI->some_model->get_data();
  }

  public function other()
  {
    //load a model that hasn't been loaded yet and get some data
    $this->CI->load->model('other_model');
    return $this->CI->other_model->some_method();
  }
}

and then in your controller:

Code:

$this->load->library('something');  //load the library
$data = $this->something->other();  //access it
$data2 = $this->something->something_else();

[eluser]Aken[/eluser]
Or, you can add the __get() method and call get_instance() there (which is exactly what the base CI_Model does).