Is there a better way (one-to-one relations)?
Posted by dcheslow on 2008-10-10 16:49
I'm new to IgnitedRecord, but familiar with ORM and ActiveRecord patterns.
I track created/lastedited/deleted information for all my db records. In proper 4th normal form, I have moved all this info into it's own table called 'metadata.' I use globally unique keys so the key to the metadata is the same as the key to whatever data it is tracking. e.g if a user has a uniqueid of 1234, then the key to the metadata for this user record is also 1234. The metadata table contains 6 fields, three timestamps and three userid's corresponding to 'create','lastedited', and 'deleted.'
Since there is a precise 1-to-1 relationship between db records and metadata records, I'd like a find() query to return the metadata right along with the real data.
I even want to take it a bit further and link (back) to the user table and find the name of the user who created/lastedited/deleted.
For example, I'd like userrecord->find(1234) to return:
user_id
name
...
metadata_id
created_ts
createdbyid
lastedited_ts
lasteditedbyid
deleted_ts
deletedbyid
createdbyuser
lasteditedbyuser
deletedbyuser
The only way that I have found (so far) to do this is to use a post_hook as follows:
function postfind($id, &$object)
{
$object->__data=array_merge($object->get_data(),
$object->related('metadata')
->select('metadata.*')
->select('C.name AS createdbyuser')
->select('L.name AS lasteditedbyuser')
->select('D.name AS deletedbyuser')
->join('user C','createdbyid = C.userid','LEFT OUTER')
->join('user D','deletedbyid = D.userid','LEFT OUTER')
->join('user L','lasteditedbyid = L.userid','LEFT OUTER')->get()->__data);
}
But that makes get_data() stop working (I have to read the private __data member directly... which is obviously a no-no).
So... it works... but it's not very elegant and it relies on private data. Any suggestions for how to improve it?
Thanks,
=dave=
I track created/lastedited/deleted information for all my db records. In proper 4th normal form, I have moved all this info into it's own table called 'metadata.' I use globally unique keys so the key to the metadata is the same as the key to whatever data it is tracking. e.g if a user has a uniqueid of 1234, then the key to the metadata for this user record is also 1234. The metadata table contains 6 fields, three timestamps and three userid's corresponding to 'create','lastedited', and 'deleted.'
Since there is a precise 1-to-1 relationship between db records and metadata records, I'd like a find() query to return the metadata right along with the real data.
I even want to take it a bit further and link (back) to the user table and find the name of the user who created/lastedited/deleted.
For example, I'd like userrecord->find(1234) to return:
user_id
name
...
metadata_id
created_ts
createdbyid
lastedited_ts
lasteditedbyid
deleted_ts
deletedbyid
createdbyuser
lasteditedbyuser
deletedbyuser
The only way that I have found (so far) to do this is to use a post_hook as follows:
function postfind($id, &$object)
{
$object->__data=array_merge($object->get_data(),
$object->related('metadata')
->select('metadata.*')
->select('C.name AS createdbyuser')
->select('L.name AS lasteditedbyuser')
->select('D.name AS deletedbyuser')
->join('user C','createdbyid = C.userid','LEFT OUTER')
->join('user D','deletedbyid = D.userid','LEFT OUTER')
->join('user L','lasteditedbyid = L.userid','LEFT OUTER')->get()->__data);
}
But that makes get_data() stop working (I have to read the private __data member directly... which is obviously a no-no).
So... it works... but it's not very elegant and it relies on private data. Any suggestions for how to improve it?
Thanks,
=dave=
Home / Developer API / Tour / Get a Project - Solutions for Bug & Issue Tracking, Collaboration Tools, Subversion Hosting, Git Hosting
Ignitedrecord is powered by Assembla.
3 Comments
By m4rw3r on 2008-10-12 02:20
This wrapper method should use CI's Active Record methods to include the other columns (technically it will be IgnitedQuery, but who cares?) or use the join_related() method (in your case it should probably be the first option).
Example with your join code:
if you don't need the user names of the users who have manipulated the record, the code will be very simple:
But be aware that join_related() prefixes the columns from the metadata table with "metadata_". But in the first function we call select('metadata.*'), which also fetches them without prefixes.
I'll probably need to add a global hook, so people easily can add this kind of functionality to all fetch methods without needing to copy and paste too much.
By Anonymous on 2009-07-14 00:41
By Anonymous on 2009-07-20 18:10