in reply to RFC - A multi table SQL Builder

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.

Replies are listed 'Best First'.
Re^2: RFC - A multi table SQL Builder
by dragonchild (Archbishop) on Oct 18, 2004 at 12:50 UTC
    The API isn't set in stone at all. Frankly, what I really have is an engine and I'm kinda looking for someone to tell me what existing module I should graft my engine into. Maybe Class::DBI is the correct place to offer this as a patch to. It seems to be the most well-used RDBMS distro behind DBI/DBD::* ...

    As for only allowing one path through the table schema ... that's actually been a problem I've run into. Behind the scenes, I build a graph of the schema, then walk the graph to find the correct connections. Let's say you have the following schema: 

    movie:
    id       integer
    name     varchar
    director integer foreign key director.id

director:
    id     integer
    person integer foreign key person.id
    salary float

roles:
    movie  integer foreign key movie.id
    person integer foreign key person.id
    type   enum ('lead', 'supporting', 'bit')

person:
    id   integer
    name varchar

    [download]

    Now, let's say you want to get the names of the director and lead for a given movie. If I were hand-building the SQL, I would do the following: 

    SELECT d_p.name AS director_name
      ,l_p.name AS lead_name
  FROM movie m
  JOIN director d ON (d.id = m.director)
  JOIN person d_p ON (d.person = person.id)
  JOIN roles r ON (r.movie = movie.id)
  JOIN person l_p ON (r.person = person.id)
 WHERE movie.name = ?
   AND r.type = 'lead'

    [download]

    Right now, the engine I have isn't smart enough to realize that I need to use the person table twice. Part of the problem there is the API. I don't know how to allow the user to specify "Join to person through director and alias it as d_p" as well as "Join to person through roles and alias it as l_p".

    *ponders*

    Maybe, the aliases are specified as part of the schema description. So, if you use a given alias for a table, it's joined through one path and if you use the other, a second join (like above) is made through the other path. I think I can code this up, but I'd really love to have a good set of requirements ... (Hint!)

    Being right, does not endow the right to be rude; politeness costs nothing.
    Being unknowing, is not the same as being stupid.
    Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence.
    Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.

[reply]
[d/l]
[select]

In Section Meditations