[Dbix-class] Naming columns in nested joins

[Matt S Trout]

Brandon Black wrote:
> On 5/24/06, Matt S Trout <dbix-class at trout.me.uk> wrote:
>> The DBIC search syntax is effectively an SQL abstract syntax tree; if
>> you want to maintain total abstraction for client applications, adding
>> (and documenting :) a custom resultset class with additional search
>> methods that hide these implementation details is the way forwards.
>>
> 
> I was thinking more about what you said the other day, re: creating
> views for all complex queries, and I think that bears on this as well.
>  Perhaps anytime one finds oneself making a very complex query in raw
> SQL or as a hugely convoluted ->search(), it is best to go back to the
> DB level and CREATE VIEW with that query, and just have the view be
> another simple DBIC source that one can run simple queries on.  Or at
> the very least, it's something to strongly consider in each such case.
> 
> With some vendors/situations/queries, this may of course be suboptimal
> for performance, but it's cleaner, and in some cases probably actually
> faster (as I would imagine at least some of the vendors
> pre-parse/prepare/plan the select that makes up the view on the server
> side permanently).

Right. The knee point for this for DBIC tends to be further on than for 
CDBI and RDBO because DBIx::Class supports rather more of SQL, but just 
because you *can* do stuff in perl space doesn't mean it's a good idea; 
sometimes it's better to just let the RDBMS do the Relational Management 
bits (shockingly) and bolt your application code onto the front of that.

Something I'm very interested in doing once we have subselects is to 
allow the specification of a subselect as a ResultSource, with a view 
(oh gods) to being able to deploy it to the database if you've got one 
that will support it and handle it perl-side if you haven't (though in a 
few years once mysql <4 has been phased out of production it probably 
won't matter so much *crosses fingers*)