[Dbix-class] custom resultsource and subclasses

[Mitchell Elutovich]

*Louis Erickson:*
*>*You mean like DBIx::Class::DynamicSubclass? The only odd part is the
union of sql select, which sounds like a view to me.
I'll have to think about the possibility of just using a view instead of a
resultsource.

Yes I've used DynamicSubclass in which it is described (in the
Cookbook.pod#Dynamic_Sub-classing_DBIx::Class_proxy_classes) as:

|DBIx::Class classes are proxy classes, therefore some different techniques
need to be employed for more than basic subclassing. In this example we
have a single user table that carries a boolean bit for |admin. We would
like to give the admin users objects (DBIx::Class::Row) the same methods as
a regular user but also special admin only methods. It doesn't make sense
to create two separate proxy-class |files for this. We would be copying all
the user methods into the Admin class. There is a cleaner way to accomplish
this.
|
|Overriding the inflate_result method within the User proxy-class gives us
the effect we want. This method is called by DBIx::Class::ResultSet when
inflating a result from storage. So we grab the object being |returned,
inspect the values we are looking for, bless it if it's an admin object,
and then return it.

Which based on this, I believe my question boils down to if I use a custom
resultsource to select rows from the database, is DBIx::Class::ResultSet still
used and does it still call the inflate_result method.

Based on the following from the Cookbook it looks like the answer to the
questions is that it is using ResultSet and I presume it should be calling
inflate_result, but it will need testing(unless someone already knows this).

Arbitrary SQL through a custom
ResultSource<http://perl.mines-albi.fr/perl5.8.5/site_perl/5.8.5/DBIx/Class/Manual/Cookbook.html#arbitrary_sql_through_a_custom_resultsource>

Sometimes you have to run arbitrary SQL because your query is too complex
(e.g. it contains Unions, Sub-Selects, Stored Procedures, etc.) or has to
be optimized for your database in a special way, but you still want to get
the results as a the DBIx::Class::ResultSet
manpage<http://perl.mines-albi.fr/perl5.8.5/DBIx/Class/ResultSet.html>.
The recommended way to accomplish this is by defining a separate
ResultSource for your query. You can then inject complete SQL statements
using a scalar reference (this is a feature of the SQL::Abstract
manpage<http://perl.mines-albi.fr/perl5.8.5/SQL/Abstract.html>
).

Say you want to run a complex custom query on your user data, here's what
you have to add to your User class:

  package My::Schema::User;

  use base qw/DBIx::Class/;

  # ->load_components, ->table, ->add_columns, etc.

  # Make a new ResultSource based on the User class
  my $source = __PACKAGE__->result_source_instance();
  my $new_source = $source->new( $source );
  $new_source->source_name( 'UserFriendsComplex' );

  # Hand in your query as a scalar reference
  # It will be added as a sub-select after FROM,
  # so pay attention to the surrounding brackets!
  $new_source->name( \<<SQL );
  ( SELECT u.* FROM user u
  INNER JOIN user_friends f ON u.id = f.user_id
  WHERE f.friend_user_id = ?
  UNION
  SELECT u.* FROM user u
  INNER JOIN user_friends f ON u.id = f.friend_user_id
  WHERE f.user_id = ? )
  SQL

  # Finally, register your new ResultSource with your Schema
  My::Schema->register_source( 'UserFriendsComplex' => $new_source );

Next, you can execute your complex query using bind parameters like this:

  my $friends = [ $schema->resultset( 'UserFriendsComplex' )->search( {},
    {
      bind  => [ 12345, 12345 ]
    }
  ) ];

... and you'll get back a perfect L<DBIx::Class::ResultSet>.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20140528/b471631b/attachment.htm>