[Dbix-class] Dynamic/Flexible DBIC views

[Christian Lackas]

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/