[Dbix-class] Twiddling SELECT lists and WHERE clauses

dorian taylor dorian.taylor.lists at gmail.com
Mon Sep 12 08:56:09 GMT 2011


Thanks Peter, I'll try my best:

I want to create a pivot table (matrix, spreadsheet, whatever) using
an aggregate. The aggregate function is different depending on what I
want in the table cells (e.g. count the primary key, but sum and avg
for certain quantitative values). The data come from three different
tables which represent a normalized depiction of a set of records,
i.e. the primary keys are the same for each but they don't necessarily
match up perfectly.

Anyway, the pivot table itself isn't the problem, but rather is
translating the parameters (Catalyst path args, natch) that select the
correct table to start from. Consider:

/function/datum/y-axis/x-axis?filter=foo

The function is the aggregate function (e.g. count, sum), the datum is
the column to be aggregated, y-axis is which (db) column value to put
into the rows, x-axis is which to make the columns (think
Data::Table::pivot). The filter keys correspond to columns/expressions
in the WHERE clause.

So the problem is that the datum may not always be in the same table,
which means there is no guarantee that the columns referred to by
either the axes or the filter will be ambiguous, collide, or even be
resolvable.

So what (it seems like) I need to be able to do is know which tables
the columns (referred to by the axes and the filter) are in so I can
properly qualify them.

On Sat, Sep 10, 2011 at 8:13 AM, Peter Rabbitson <rabbit+dbic at rabbit.us> wrote:
> BOn Fri, Sep 09, 2011 at 01:33:44PM -0700, dorian taylor wrote:
>> Hey guys,
>>
>> I have a set of queries that do virtually the same thing over three
>> different initial result sources. Elements of the WHERE clause are
>> present in each of them, but sometimes I need to refer to me.colname,
>> sometimes relname.colname. The same goes for the SELECT list.
>
> Show your actual use case, as the answer will differ depending on which
> part exactly you want to streamline/abstract away.
>
>> My instinct is to do some sort of thing which reads the join structure
>> and figures out from the point of view of the initial position which
>> columns to rename 'me.' and which to rename 'relname.'.
>
> Don't do that, it is much much much harder than you can possibly imagine.
> A naive implementation will appear to work until it stops doing so.
>
>> I figure
>> though that there must be some existing facility for at least part of
>> this.
>
> There is an internal facility to manage the from spec, however it is
> still under very heavy development, thus not suitable for general API
> consumption.
>
>> I see for example that there are +select and +as operators, as
>> well as the merging of WHERE clauses when searches on result sets are
>> called in succession.
>
> The where condition merging is stateless, and so is the select spec
> (the +as operator does not influence the SQL generation, it is there to
> deal with parsing the returned result).
>
>> I suppose I could just fully qualify the
>> underlying table names rather than use the generated aliases, but I'm
>> concerned about side effects.
>
> You can not do this, as most engines do not allow you to refer to the
> original table name once you aliased it to something else.
>
> Bottom line - show us what you want to do so that a correct answer can
> be delivered :)
>
> Cheers!
>
> _______________________________________________
> 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
>



-- 
Dorian Taylor
http://doriantaylor.com/



More information about the DBIx-Class mailing list