Dear Monks, I am currently porting a webapp to the wonderfull Catalyst MVC framework. I also decided to port the original (very clumsy) DBI code to use Class::DBI/Class::DBI::Sweet instead, i like it very much, get the job done and fast ! My problem is the following, this particular application use a old schema that was not designed by me and that i cannot change. One of the function require me to use a very large query with multiples LEFT JOIN: 

select
  event.sid, event.cid, event.timestamp, signature.sig_name, iphdr.ip_
+src, iphdr.ip_dst,
  icmphdr.icmp_type, icmphdr.icmp_code, icmphdr.icmp_csum, icmphdr.icm
+p_id,
 icmphdr.icmp_seq,
  udphdr.udp_sport, udphdr.udp_dport, udphdr.udp_len, udphdr.udp_csum,
  tcphdr.tcp_sport, tcphdr.tcp_dport, tcphdr.tcp_seq, tcphdr.tcp_ack, 
+tcphdr.tcp_off,
  tcphdr.tcp_res, tcphdr.tcp_flags, tcphdr.tcp_win, tcphdr.tcp_csum, t
+cphdr.tcp_urp,
  sensor.hostname, sensor.interface, data.data_payload
from event
  where event.sid = 1
  left join signature on signature.sig_id = event.signature
  left join iphdr on iphdr.sid = event.sid and iphdr.cid = event.cid
  left join icmphdr on icmphdr.sid = event.sid and icmphdr.cid = event
+.cid
  left join udphdr on udphdr.sid = event.sid and udphdr.cid = event.ci
+d
  left join tcphdr on tcphdr.sid = event.sid and tcphdr.cid = event.ci
+d
  left join sensor on sensor.sid = event.sid
  left join data on data.sid = event.sid and data.cid = event.cid
order by
  timestamp desc, event.sid desc, event.cid desc

[download]

the WHERE part come from a form and can have numerous options set by the user. I used to do this with straight DBI + regex +placeholders and it was a pain in the ass to manage. Now i would like to do it in Class::DBI instead. I came across the following recipe on the CDBI wiki: http://wiki.class-dbi.com/index.cgi?UsingJoins http://wiki.class-dbi.com/index.cgi?DirectlyExecuteSql I undertand i can do this with SQL::Abstract (SQL::Abstract::Limit in my particular case since i need paging). Anyone can give me some tips, advices, best practices ? I am also wondering how can i integrate Data::Page to work with this query ? Is that possible ? Thanks a lot ! PS: Pardon for my not-so-perfect english

Edit: g0n - added code tags, linkified links

In reply to Class::DBI and Query with JOIN'S + Paging support by Anonymous Monk

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.