[Dbix-class] Re: Custom SQL

[Bill Moseley]

On Mon, Dec 14, 2009 at 8:58 AM, kevin montuori <montuori at gmail.com> wrote:

>
>  BG> It really frustrates me, when simple things become that difficult.
>
> this incantation works for me:

  package MyApp::Schema::MyApp::Result::AuthzPartial;
>
>   use strict;
>    use parent 'DBIx::Class::Core';
>
>   __PACKAGE__->table_class('DBIx::Class::ResultSource::View');
>    __PACKAGE__->table('NONE');
>   __PACKAGE__->add_columns( qw[ is_authz ] );
>
>   __PACKAGE__->result_source_instance->is_virtual(1);
>    __PACKAGE__->result_source_instance->view_definition
>      ( q[ SELECT has_some_authz(?, ?, ?, ?) AS is_authz ] );
>
>   1;
>


I've had mixed luck with using custom SQL.  The need does not come up often
but when it does I find it somewhat limiting.  So, I've been using Views in
the database and setting up a normal Result class for it which seems to work
better (with joins, etc.).

But, I'd like to get the virtual views working and I assume I'm just not
using it correctly.

My custom SQL is almost alway related to some table.  So, what I have tried
is the above but then have it inherit from another result class.  For
example in my "music" database I have custom SQL on the Cd table.  I have it
inherit from MyApp::Result::Cd and then I can call $foo->artist on the
result to fetch the artist object, for example, that is defined in the Cd
class.

package MyApp::Result::CustomCd;
use strict;
use warnings;
use base 'MyApp::Result::Cd';
__PACKAGE__->table_class('DBIx::Class::ResultSource::View');
__PACKAGE__->table( 'dummy' );  # complains without
__PACKAGE__->result_source_instance->is_virtual(1);
__PACKAGE__->result_source_instance->view_definition( <<'EOF' );
    SELECT * from cd where id < ?
EOF

1;

(well, in real life the SQL is a bit more involved...)

Is it expected that DBIx::Class uses a sub-query for custom SQL?

SELECT me.created_time, me.id, me.year, me.name, me.artist FROM (    SELECT
* from cd where id < ?
) me: '3'

I've also tried setting the result class like this:

use base 'MyApp::Result';
__PACKAGE__->resultset_attributes( { result_class =3D> 'MyApp::Result::Cd' }
);

But I must be using that incorrectly as that shows:

SELECT * FROM (    SELECT * from cd where id < ?
) me: '3'


I'm not convinced about having a separate class for each custom bit of SQL,
too.   Seems like defining the custom SQL inside the corrisponding class
would be more logical.

I agree, it does feel as if simple things are more difficult at times.



-- =

Bill Moseley
moseley at hank.org
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20091215/ddf=
e546a/attachment.htm