MY_Model base CRUD |
[eluser]Mark Croxton[/eluser]
A basic limitation with many of the existing MY_Model classes that provide a base CRUD pattern are that they don't support multiple tables and joins. If you ever need to work with more than one table per model, and want some magical CRUD methods without the overhead or abstraction of an ORM, then this MY_Model might be for you. My_Model can handle joins between a primary table and one or more others, including self joins (using aliases). For more complex queries the 'primary' table the model operates on can be changed to allow joins between secondary tables and their related tables. The class is PHP 5 only. Most of the methods return $this and so are chainable. CIs Active Record methods can also be chained with the custom MY Model methods. Usage Place in your application/libraries folder and extend your models from it. Simple join example Let's say you have an 'offices' table and a 'countries' table. Offices: - id (primary key) - office name - city - country_iso (foreign key) Countries: - iso (primary key) - country_name The model Code: class Office extends MY_Model { In your controller you can now grab offices in a variety of ways using the get() method and it's variants (get_one, get_list, get_column, get_field) Code: // load the model You can also update, insert and delete your offices with ease: Code: // insert a new record Other notable methods accessible to the controller are count(), count_all(), get_insert_id(), get_num_rows() and get_affected_rows(). Chaining joins The get() method can handle any selecting task that operates on a single table with one or more tables joined to it (one-to-one or one-to-many). But what if you need to chain your joins (one-to-many or many-to-many)? Create a new method in your model: Code: public function get_users_in_group($group_id) Note that passing TRUE as the third parameter of join() causes the primary table the model operates on to be changed to the newly joined table. This is just a convenience; you can also explicitly set the primary table by starting your statement $this->with('my_table_object'). v1.0.1 - Updated 20 April 2010: * changed set() to with() to avoid confusion with $this->db->set() - make sure you update your models if you've used set() * made table alias handling more robust; takes account of CI's current inablity to use aliases with $this->db->delete() * $fields parameter of get() can now be a string, tables names will not be escaped v1.0.3 - Updated 19 May 2010: * select() and where() can now accept hard coded table aliases (table.column); the table specified will be automatically joined if required. * new functions: set_count() and get_count(), for multiple queries where you need to store the count of each query, optionally using a key. * get() can now be passed a third boolean argument to determine if the query will be initialised. * various other subtle improvements designed to make the class more robust when working with complex joins. * Moved to Github v1.0.4 - Updated 23 August 2010: * primary key default (if not specified when using load_table()) is now the first column in a table rather than 'id' Download or fork: http://github.com/croxton/MY_Model
[eluser]Mark Croxton[/eluser]
Self joins My_Model uses aliases to facilitate self joins. Let's say you want to generate a nested list of 'services' using the adjacency list model. Services may be nested to 3 levels. SQL Code: CREATE TABLE `services` ( Back to the model - after loading the services table we can create a couple aliases of it, s2 and s3; alias values are passed as the second parameter of load_table. The aliased tables will reuse the table structure of the services table, so we pass its fields as the third parameter of load_table(); Code: public function __construct()
[eluser]Mark Croxton[/eluser]
Caching My_Model looks up the structure of loaded tables using SHOW COLUMNS. This information is used to ensure that the data passed to INSERT, UPDATE, SELECT and WHERE maps to the fields of the table you are working with. If you'd like to cache the table column queries I would recommend using a general purpose caching library. I've included a function for working with caching which expects the caching class to have public methods 'get' and 'write' which accept a single key. Here's an example using the excellent MP Cache; assuming you've autoloaded mp_cache, and created a folder called 'table_structure' in your designated cache directory. In the model constructor: Code: public function __construct()
[eluser]thinkigniter[/eluser]
This looks very interesting. Thanks for sharing.
[eluser]flash_lover[/eluser]
Hi, I followed your tutorial to a T (well i believe so), but i keep getting, _________________________ Severity: Notice Message: Undefined variable: office_id Filename: controllers/datalinker.php Line Number: 24 _________________________________ which refers to -'id' => $office_id- line below. // get a single asset by id $office = $this->office->get_one( array( 'id' => $office_id, 'fields' => array('id', 'office_name', 'city', 'country_name') ) ); Maybe I missed something, any ideals? Thanks
[eluser]Mark Croxton[/eluser]
$office_id would be the id the office you want to retrieve So your controller might look like: Code: public function index($office_id=null)
[eluser]flash_lover[/eluser]
Thanks for the reply, now i get at parse error on this line $list = $this->office->get_list('id', 'office_name')); Could something in my database, be throwing it off? thanks
[eluser]Mark Croxton[/eluser]
You've got an extra bracket there for some reason: $list = $this->office->get_list(‘id’, ‘office_name’));
[eluser]Mark Croxton[/eluser]
Updated to version 1.0.1 - download from the first post. |
Welcome Guest, Not a member yet? Register Sign In |