Re: [Dbix-class] Dynamic/Flexible DBIC views
* Lasse Makholm la...@unity3d.com [141128 18:05]:
Dear Lasse,
thanks a lot for your help!
Your example works very well for me also, and a view factory makes my
live much easier when dealing with complicated queries.
One question, though. Do you have any idea why this works:
my @rows = $schema-resultset('Foo')-all;
however, using next results in an endless loop returning the first
entry over and over again:
while (my $row = $schema-resultset('Foo')-next) {
...
}
Thanks again,
Christian
--
Dr. Christian Lackas, Managing Partner
inviCRO, LLC -- In Imaging Yours
http://www.invicro.com/ http://www.spect-ct.com/
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Dynamic/Flexible DBIC views
Christian writes:
however, using next results in an endless loop returning the first
entry over and over again:
while (my $row = $schema-resultset('Foo')-next) {
...
}
You are creating a new resultset object in each iteration(!)
Try something like:
my $foos=$schema-resultset('Foo');
while (my $row=$foos-next) {
...
}
Best regards,
Adam
--
In the past we would do little things for love, but Adam Sjøgren
but things, big things required money. Now we can do a...@koldfront.dk
big things for love.
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Dynamic/Flexible DBIC views
* Darius Jokilehto dariusjokile...@yahoo.co.uk [141122 07:43]:
Dear Darius,
thanks for your input (David as well).
You can implement this by passing an identity instead of a value, i.e.
me.customer_id = me.customer_id. So either pass (untested):
{ -ident = 'me.customer_id' }
or if that doesn't work a scalar ref:
\'me.customer_id'
Granted, it's still not pretty, but it should work.
unfortunately, passing in literal parameters or even references to raw
SQL does not work with bind values. Which does make sense since the
whole point of bind values is that they are safely escaped values and
avoid having to check the input yourself.
Unfortunately, bind values seem the only way one can communicate with a
virtual views, which makes them a lot less flexible than they could be.
Will try to create views on the fly now, however, have not yet found out
how to do this (that is not having the Schema load them automatically
from pm files)...
Christian
--
Dr. Christian Lackas, Managing Partner
inviCRO, LLC -- In Imaging Yours
http://www.invicro.com/ http://www.spect-ct.com/
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Dynamic/Flexible DBIC views
On Fri, Nov 28, 2014 at 4:04 PM, Christian Lackas lac...@lackas.net wrote:
* Darius Jokilehto dariusjokile...@yahoo.co.uk [141122 07:43]:
Dear Darius,
thanks for your input (David as well).
You can implement this by passing an identity instead of a value, i.e.
me.customer_id = me.customer_id. So either pass (untested):
{ -ident = 'me.customer_id' }
or if that doesn't work a scalar ref:
\'me.customer_id'
Granted, it's still not pretty, but it should work.
unfortunately, passing in literal parameters or even references to raw
SQL does not work with bind values. Which does make sense since the
whole point of bind values is that they are safely escaped values and
avoid having to check the input yourself.
Unfortunately, bind values seem the only way one can communicate with a
virtual views, which makes them a lot less flexible than they could be.
Will try to create views on the fly now, however, have not yet found out
how to do this (that is not having the Schema load them automatically
from pm files)...
Interesting problem... As it turns out, adding a result source dynamically
is not that difficult. The following works for me:
use feature qw(say);
use Data::Dumper;
use Scalar::Util qw(blessed);
use Class::MOP;
my $schema = MyApp::Schema-connect(...);
my $meta_class = Class::MOP::Class-create_anon_class(
superclasses = [ qw(DBIx::Class::Core) ],
);
my $class = $meta_class-name;
(my $table = $class) =~ s/::/_/g;
$class-load_components(InflateColumn::DateTime);
$class-table_class('DBIx::Class::ResultSource::View');
$class-table($table);
$class-result_source_instance-is_virtual(1);
$class-result_source_instance-view_definition(q{
SELECT 42 as number, foo as string, NOW() as date
});
$class-add_columns(
number = { data_type = bigint, default_value = 0, is_nullable = 0
},
string = { data_type = varchar, is_nullable = 1, size = 255 },
date = { data_type = timestamp, datetime_undef_if_invalid = 1,
timezone = UTC },
);
$schema-register_source(Foo = $class-result_source_instance);
my $row = $schema-resultset('Foo')-first;
say blessed($row);
say blessed($row-date);
say Dumper({ $row-get_columns });
Creating a virtual view factory should mostly be a SMOP. Additionally,
with a bit of introspection, you could copy column and relationship
definitions from your normal result sources where it makes sense. This
would allow you to do something a la:
my $view = $schema-create_virtual_view(
ArtistAlbumCountView = q{ SELECT artist_id, COUNT(*) AS album_count FROM
album GROUP BY artist_id },
artist_id = 'artist.id', # copy column + rel info
album_count = { data_type = 'bigint', ... },
);
Which would presumably allow you to traverse relations as usual:
my $artist = $view-search-first-artist;
Some things to look out for would be leaking memory if you create a large
number of views. DBIx::Class implements unregister_schema() but I don't see
any supported way of destroying the result source instance.
Also, I'm not sure to which degree you can destroy a package/class and
avoid leaking memory over time. Things like having a pool of reusable class
names or hashing the SQL query and putting it in the class name to avoid
duplicating views might help here.
On the other hand if your workers only serve a limited number of requests
before being restarted, this may never be a real problem.
Good luck! :-)
/L
Christian
--
Dr. Christian Lackas, Managing Partner
inviCRO, LLC -- In Imaging Yours
http://www.invicro.com/ http://www.spect-ct.com/
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive:
http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Dynamic/Flexible DBIC views
On Friday, 21 November 2014, 15:35, Christian Lackas lac...@lackas.net
wrote:
[...]
Thus, just adding another bind value that overrules the search for a
specific customer.
And this quickly becomes quite complicated if I know also want to add an
optional 'customer.type = ?' to work on projects belonging to a set of
customers, and I don't have any idea how I could implement a
'me.customer_id in (?)' with a flexible number.
You can implement this by passing an identity instead of a value, i.e.
me.customer_id = me.customer_id. So either pass (untested):
{ -ident = 'me.customer_id' }
or if that doesn't work a scalar ref:
\'me.customer_id'
Granted, it's still not pretty, but it should work.
Darius
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
[Dbix-class] Dynamic/Flexible DBIC views
Hi Everybody,
tl;dr: what is the best way to dynamically create the definition of
a virtual view or create an entire virtual view on the fly?
Background:
To fetch aggregated data from the database, I recently started to add
virtual views to our schema (it quickly became to hard to express the
joins and subqueries within the constraints of DBIC).
That said, I often have a family of related queries, that produce the
same output, however, based on different input. E.g. I would like to
aggregate project information for one customer, a set of customers or
all customers. The aggregate information for instance is the
count/sum/min/max/etc of revenue per month.
If I am not mistaken then the user can only pass in bind values to the
view, right?
For instance I have something like this in the view:
__PACKAGE__-result_source_instance-view_definition(q[
select ... from projects me
inner join ( select ... ) join_name
where me.customer_id = ? and ...
]);
and then can get the desired information with
my $res = $view-search( {}, { bind = [ 42 ] } );
However, I cannot directly use this to get the same information for all
customers, right?
I already came up with this workaround, which gets me a little closer to
what I need, however, I don't really like it:
__PACKAGE__-result_source_instance-view_definition(q[
select ... from projects me
inner join ( select ... ) join_name
where (? or me.customer_id = ?) and ...
]);
my $res = $view-search( {}, { bind = [ 0, 42 ] } ); # customer 42
my $res = $view-search( {}, { bind = [ 1, 0 ] } ); # all customers
Thus, just adding another bind value that overrules the search for a
specific customer.
And this quickly becomes quite complicated if I know also want to add an
optional 'customer.type = ?' to work on projects belonging to a set of
customers, and I don't have any idea how I could implement a
'me.customer_id in (?)' with a flexible number.
So what is the best solution here? Can I somehow add parameters to the
view that then can be taken into account, or is it better to create the
view I need on the fly feeding in the definition I need (and how is that
done best)?
Thanks,
Christian
--
Dr. Christian Lackas, Managing Partner
inviCRO, LLC -- In Imaging Yours
http://www.invicro.com/ http://www.spect-ct.com/
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Dynamic/Flexible DBIC views
On Fri, Nov 21, 2014 at 08:38:33AM -0500, Christian Lackas wrote:
I have something like this in the view:
__PACKAGE__-result_source_instance-view_definition(q[
select ... from projects me
inner join ( select ... ) join_name
where me.customer_id = ? and ...
]);
and then can get the desired information with
my $res = $view-search( {}, { bind = [ 42 ] } );
[but bind values are icky]
When I had a similar problem a couple of years ago I was told that what
you're doing here is the best solution. If anyone has come up with
something better since, I'd love to know too.
--
David Cantrell | top google result for topless karaoke murders
Compromise: n: lowering my standards so you can meet them
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
