Personally, I haven't seen such a module in the DBIx namespace, but I'd really like such a module to allow for better performance in Class::DBI, as there, the joins on multiple tables are implemented on the Perl side of things. This approach is good, as it allows for huge flexibility, but as 99% of my use cases can be handled on the SQL side of things, I'd really like to declare the paths through my schema and have Perl figure out how to get from one table to the next via joins.

Consider looking at how SQL::AbstractSearch does things, but in any case, such a module would be very fine in my eyes.

I know you have your API laid out already, but here are some ideas how I'd like to use the module to declare joins in a Class::DBI setting:

__PACKAGE__->has_many_direct( abcds => [ a => 'b.ab' => 'c.bc' => 'd.c
+d' ] );

[download]

or, maybe by declaring the "fast path" options outside of the "has_many" section would be easier, as then all generated queries could use it:

package A;
__PACKAGE__->direct_join( b => 'b.ab' );
package B;
__PACKAGE__->direct_join( c => 'c.bc' );
# ... and so on

[download]

but that "solution" has the drawback of only allowing/suggesting one path through the table schema, where you might have more than one, say, a movie, having connections to a person via both, the roles and the directors.

In reply to Re: RFC - A multi table SQL Builder by Corion
in thread RFC - A multi table SQL Builder by dragonchild

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.