[Dbix-class] Help needed to get a much needed feature into DBIC (low barrier to entry)

[Dami Laurent (PJ)]

> -----Message d'origine-----
> De : Peter Rabbitson [mailto:rabbit+dbic at rabbit.us]
> Envoyé : jeudi 9 janvier 2014 07:55
> À : dbix-class at lists.scsys.co.uk
> Objet : [Dbix-class] Help needed to get a much needed feature into DBIC
> (low barrier to entry)
> 
> Greetings dear lurkers, your mad recursive algorithm fu is needed ;)
> 
> There is a long-standing design/spec for proper custom join
> conditions/options specified on the fly via search() directly in your
> join/prefetch attributes. 
> ...

Hi Peter & all,

Just for info (this might be a source of inspiration :-) ) : I had a similar problem in the design of DBIx::DataModel. From a programming point of view, there  are two clearly distinct notions :
a) deciding which tables to join (relying on regular relationships)
b) adding some special conditions to the "ON" clause
Usually in the program we want to do those two steps at different places, but the SQL syntax wants everything together, so there is a conflict.

So what I did in DBIx::DataModel is really a kind of hack but it it works quite smoothly in our applications : 
- the join() method accumulates information for the regular join
- a new operator "-where_on" accumulates information for the additional join conditions
- just before generating SQL, we merge information from both sources and pass the result to SQL::Abstract::More.

The only point I'm not totally happy with is that the "-where_on" operator needs to receive the SQL table name to which the condition will be attached; this is too low-level and should be improved.

I guess that a similar approach would be feasible in DBIC : accumulate some "where_on" information while chaining resultsets, and assemble the whole thing within SQLMaker.

If anybody is interested, the doc is in https://metacpan.org/pod/DBIx::DataModel::Doc::Reference#where_on-where_on_criteria 
And the source code in
https://metacpan.org/source/DAMI/DBIx-DataModel-2.42/lib/DBIx/DataModel/Statement.pm 

Cheers, Laurent Dami