Hi!

I am trying to convert an old pure SQL application to using DBIx::class/SQL::abstract. I am running into some problems for two types of cases.

The application is basically a single query where a number of tables are joined multiple times (selecting a number of protein domain rows, as it were) and a lot of conditions are applied to the resulting join.

In some cases, there are SQL WHERE conditions on the form

... table1.variable - table2.variable < (integer value) ... (all joined together with AND)

In DBIx::class, I suppose what I want is a huge hash holding all of these which will eventually go into a resultset->search () call.

I suppose it should look something like

%searchConditions = {

'table1.variable' => {'<', '(integer value) - table2.variable'}

};

[download]


but can I just embed arithmetics like that into the second part of the condition and expect it to be properly processed?

My second question concerns how to represent multiple AND conditions involving the same table field.

Suppose I have the SQL

... table1.variable - table2.variable < (integer value 1) AND 
table1.variable - table3.variable < (integer value 2)...

[download]


I feel it should translate to something like

%searchConditions = {

'table1.variable' => {'<', '(integer value 1) - table2.variable'},
'table1.variable' => {'<', '(integer value 2) - table3.variable'}

};

[download]


since things should go in a hash to join them by AND logic, but this does not seem like a legal hash since there are keys appearing more than once. Should it be something like:

%searchConditions = {

'table1.variable' => {'-and' => [{'<', '(integer value 1) - table2.var
+iable'}, {'<', '(integer value 2) - table3.variable'}]}

};

[download]


I apologize beforehand if this is illegible, silly or full of code suggestions that are syntactically wrong. However, I am days or weeks away from actually being able to test the code, so if possible, I thought I should ask people who actually know the syntax, to see if I could get it right from the beginning...

Grateful for help,

Yours sincerely,

Kristoffer Forslund

In reply to Arithmetic SQL operation in DBIx::class? by Analysis

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.