Active Record dbprefix... sort of

[eluser]tonanbarbarian[/eluser]
Coming from a background of developing for Mambo and Joomla! CMSes for the last few years I find myself liking the way that when writing queries for these frameworks (if you can call them frameworks) you could prefix your table names in the query and it would be automatically converted.

So you would write something like this

Code:

SELECT * from #__users

and the #__ would always be converted to whatever prefix was setup in the config

Code:

SELECT * FROM mos_users

for example.

Now I know that if I am using active record I can set the config to

Code:

$db['default']['dbprefix'] = 'mos_';

and it would do the same thing

There are a couple of issues I have with the way it is being done is CI

Firstly there is some complex regex having to be done to try to find situations where it cannot detect the table name correctly or where it has to prefix the table names in where clauses etc
If CI used a similar method to that detailed above it would be much easier to code for.

Secondly unlike the above example I find myself wanting to be able to replace multiple prefixes.

The thing is I am looking to build some code that can make my apps work in any integrated environment. If any other PHP web app needs some functionality I want to be able to code something in CI to handle it.

Lets say you want a Refer Friend system on a site (not what I am actually working on), then the CI code should be able to integrate into the Session and User information for the existing PHP app and work with it by using some simple libraries that are loaded for the existing app.
But to keep things seperated a bit I want to be able to store data in my own tables with their own prefix.
So the default app might be using mus_user as its table prefix but I want my table to be my_referers
The issue with the active record dbprefix is that there is only one of them.

I have tried to look into ways to extend the db classes but cannot see any way to do it because of the way they are loaded, with the exception of creating my own database loader (which I may have to do)

But I do think it might be a nice feature in future version of CI to be able to specify an array of replacements something like this

config/database.php

Code:

$db['default']['dbreplace'] = array('#1#_'=>'mos_','#2#_'=>'my_','#3#_'=>'os_');

and have the active record replace the keys with the values in the resulting queries before they are run
This way you would create a query something like follows

Code:

$this->db->select('#1#_users.username, #2#_products.name, #3#_orders.amount');
$this->db->from('#1#_users');
$this->db->join('#2#_products', '#1#_users.id=#2#_products.manufacturer_id');
$this->db->join('#3#_orders', '#2#_products.id=#3#_orders.product_id');

which would produce something like

Code:

SELECT mos_users.username, my_products.name, os_orders.amount
FROM mos_users
JOIN my_products ON mos_users.id=my_products.manufacturer_id
JOIN os_orders ON my_products.id=os_orders.product_id

So while it forces you to write your queries using replacement tags such as #_ etc in them it makes it much more flexible.

In the mean time I guess I will write my own database loader so that I can extend the active record class and provide this feature myself

[eluser]xwero[/eluser]
As an alternative you could use prefix variables in your model.

Code:

class Mymodel extends Model
{
   var $pf;
  
   function Mymodel()
   {
     parent::Model();
     $this->pf = array('mos_','my_','os_');
   }

   function query()
   {
      $this->db->select($this->pf[0].'users.username, '.$this->pf[1].'products.name, '.$this->pf[2].'orders.amount');
      $this->db->from($this->pf[0].'users');
      $this->db->join($this->pf[1].'products', $this->pf[0].'users.id='.$this->pf[0].'products.manufacturer_id');
      $this->db->join($this->pf[2].'orders', $this->pf[1]'products.id='.$this->pf[2].'orders.product_id');
   }

}

This means less processing. But for a query like in the example i would use table aliases which would make the query more readable.

Code:

$this->db->select('t1.username, t2.name, t3.amount');
      $this->db->from($this->pf[0].'users t1');
      $this->db->join($this->pf[1].'products t2', 't2.id=t1.manufacturer_id');
      $this->db->join($this->pf[2].'orders t3', 't2.id=t3.product_id');

If the prefixes are site wide you can add your own config file with the prefixes.