[Dbix-class] Filtering a SELECT based on a calculation of two columns?

[Eden Cardim]

>>>>> "Chris" == Chris Cole <chris at compbio.dundee.ac.uk> writes:


    Chris> Well, for a start I don't understand the syntax. Why do you only escape the
    Chris> first '[' and not the last?

the backslash on "\[]" isn't an escape, it's creating a reference to an
anonymous array. That's the construct for passing arbitrary SQL and
providing the bind values for the placeholders you might have in that
SQL.

    Chris> Knowing when to have a hashref or a arrayref is a bit of
    Chris> nightmare in DBIx. Most times, I plump for one and if it
    Chris> doesn't work I go with the other.

    Chris> In the Cookbook example it's not clear where YEAR() and 'date_of_birth' come
    Chris> from. I very rarely work with dates and am unfamiliar with date manipulation
    Chris> functions. It would also be clearer to state that the 'plain_value' assignment
    Chris> is there to replace the placeholder. For example:

YEAR and date_of_birth are arbitrary functions/columns that you included
in a literal SQL snippet.


    Chris> "...the below will search for when the year of birth is 1979:
    Chris> $rs->search(\[ 'YEAR(date_of_birth) = 1979' ]);

    Chris> But, if you wish to use a variable in the comparison a different construct is
    Chris> required because there is no variable expansion within single quotes in perl:
    Chris> $year = 1979;
    Chris> $rs->search(\[ 'YEAR(date_of_birth) = ?', [ plain_value => $year ] ]);"

    Chris> The second example in the cookbook, seems wrong. There are placeholders for two
    Chris> variables ('name' and 'YEAR(date_of_birth)') in the example SQL, but the DBIx
    Chris> code already has 'Bob' specified as the 'name'.

That's because DBIC takes care of building the entire query for you. You
only have to worry about placeholders within the scope of the literal
SQL you're passing into ->search

    Chris> As per my original request, an example showing something other than equals would
    Chris> help in understanding how to deal with greater than or less than tests. Even
    Chris> now, I wouldn't know where to put a '<' in this complicated construct, without
    Chris> an example.

Does

--8<---------------cut here---------------start------------->8---

$rs->search(\[ 'YEAR(date_of_birth) < ?', $year ]);

--8<---------------cut here---------------end--------------->8---

work for you?

    Chris> The SQL::Abstract documentation is worse. I've no idea what 'bindtype' 'I' have
    Chris> set, seeing as /I/ am not using SQL::Abstract, but DBIx.

You only need to worry about those if you're using Oracle, AFAIK.

-- 
Eden Cardim
Software Engineer
Shadowcat Systems Ltd.
http://www.shadowcat.co.uk
http://blog.edencardim.com