[Dbix-class] Hi All, First Question

[Hartmaier Alexander]

Hi!

I've done versioning with dbic too, but I decided not to store the version number and the datetime because that would be redundant.

In my case every request (table name is fw_request for the example, I have three different request tables) has an id_fw_request (the database primary key), a datetime field (req_datetime) and a req_id (number which is filled by the catalyst app, either from an oracle sequence if it's a new request or with the value from the current request if a new version is requested).
I made a view with all requests ordered by req_id and req_datetime and use the rank function to generate the version number:

For me (Oracle 10g) the view query looks like this:
SELECT id_fw_request, RANK() OVER (PARTITION BY req_id ORDER BY req_datetime) AS version
FROM nacadm.fw_request

Then I have another view which gives me the datetime of the latest version per req_id:
SELECT req_id, MAX(req_datetime) req_datetime_latest
FROM nacadm.fw_request
GROUP BY req_id

Both views are used in a normal dbic table class and joined as needed to the request table class (from package NAC::Model::DBIC::Table::FW_Request):

__PACKAGE__->has_one(    view_version       => 'NAC::Model::DBIC::Table::View_FW_Request_Version', undef, { proxy => [qw/ version /] });
__PACKAGE__->has_one(    view_latest        => 'NAC::Model::DBIC::Table::View_FW_Request_Latest', {
    'foreign.req_id'                => 'self.req_id',
    'foreign.req_datetime_latest'   => 'self.req_datetime',
    },
    {
        proxy => [qw/ req_datetime_latest /],
    }
);

If you join and/or prefetch view_latest, you will only get the latest version per req_id, because the join is defined as a full join only the latest versions will be returned, because of the proxying you can directly access 'version' from the request.

If you have more questions write to the list or join us at irc.perl.org #dbix-class.

-Alex


> -----Original Message-----
> From: dbix-class-bounces at lists.rawmode.org [mailto:dbix-class-
> bounces at lists.rawmode.org] On Behalf Of tiziano at e-tip.net
> Sent: Tuesday, October 17, 2006 7:08 PM
> To: dbix-class at lists.rawmode.org
> Subject: [Dbix-class] Hi All, First Question
> 
> Hi All,
> 've got a table, called estimate,containing 4 fields : id, ref,
> revision,
> status.
> id is the primary key,
> ref is the number of the estimate in format (number/year)
> revision is a int that specifies the revision of the estimate.
> Now... in table can be more than one rows with same ref but several
> revision numbers
> I've done something like resultset({ %search },{ group_by => [qw /ref
> /]}
> but in some cases this give me back the row with the lowest id for that
> group.
> Example:
> 1,1/2006,0,0  <--- this is the row returned by the query
> 2,1/2006,1,0
> 3,1/2006,3,0
> 4,1/2006,4,0
> 
> but... i need the one with id = 4
> Any help on doing it?
> Thanks in advance
> Tiziano
> 
> 
> 
> 
> _______________________________________________
> List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
> Wiki: http://dbix-class.shadowcatsystems.co.uk/
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
> Searchable Archive: http://www.mail-archive.com/dbix-
> class at lists.rawmode.org/

*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
T-Systems Austria GesmbH   Rennweg 97-99, 1030 Wien
Handelsgericht Wien, FN 79340b
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
Notice: This e-mail contains information that is confidential and may be privileged.
If you are not the intended recipient, please notify the sender and then delete this e-mail immediately.
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*