[Dbix-class] DBIx::Class subclass and multiple tables

[Andreas Pronakis]

This topic might have been previously covered but i don't seem to be able to find the solution, so apologies in advance if it's a repeated post.

Is it possible with DBIx::Class to have a class (sub-class) which properties are associated with two or more DB tables.

A generic example would be:


My::Class::User
My::Class::User::Admin
My::CLass::User::PowerUser

etc.

The parent class User has a table in DB (called user) that holds columns common to all User sub-classes, where User::Admin would also need to look in a second DB table (called user_admin) and add the extra columns to the User::Admin object.  User::PowerUser would also have it's own extra table (call it user_poweruser).

Both sub-classes will need to look into the user table to get the common columns and then add the extra ones from their respective tables.

I can not use Database VIEWS as they are not supported in Mysql 4.0

I don't mind if I have to instantiate a User object first that will then inflate (re-bless) itself to the appropriate sub-class (although it does sound a bit back to front).

My main objectives are:
1. All this to happen with a single hit to the DB (one SQL statement)
2. DBIx::Class search(), find() methods will still work as normal i.e.
    my $admin_rs = $schema->resultset('Admin')->find(1);
    will return me User::Admin object with it's properties being the combined columns of the user and user_admin DB tables.

I understand that essentially I am looking for a join between two tables, but i needed it on all methods, i.e. new(), find(), search(), any relationships etc.  Do i need to override these to get the required result? or DBIx::Class is not the right tool for the specific job?


Any ideas/clues or even better code examples ;-) would be much appreciated

Andreas