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

[Rob Kinyon]

Eden has already answered all your questions. I'll elaborate on a few details.

First detail: ->search( @args, $control ) should really be explicit
about the following facts:
1) $control is an optional hashref that affects the resulting query.
Things like rows, join, +columns, etc.
2) @args is a non-greedy list of scalars (strings, numbers, or
references to various things) that is passed (almost) untouched to
SQL::Abstract.

DBIC does *NOT* do its own SQL creation. Instead, it delegates
(almost) all of that to SQL::Abstract. This is why your point below
about DBIC using SQLA is incorrect. You are using SQLA. You just
aren't using it explicitly.

On Tue, Feb 22, 2011 at 04:42, Chris Cole <chris at compbio.dundee.ac.uk> wrote:
> Well, for a start I don't understand the syntax. Why do you only escape the
> first '[' and not the last? Knowing when to have a hashref or a arrayref is
> a bit of nightmare in DBIx. Most times, I plump for one and if it doesn't
> work I go with the other.

Not understanding basic Perl syntax is a problem. You need to fix
that. Because of the huge number of options SQL::Abstract has to
accommodate, nearly every single Perl syntax option has been used and
intermixed.

> In the Cookbook example it's not clear where YEAR() and 'date_of_birth' come
> from. I very rarely work with dates and am unfamiliar with date manipulation
> functions.

What do you suggest we do to better explain this? What kind of
preamble would be helpful to say "The following are database
functions." Write something up, please.

> It would also be clearer to state that the 'plain_value'
> assignment is there to replace the placeholder. For example:
>
> "...the below will search for when the year of birth is 1979:
> $rs->search(\[ 'YEAR(date_of_birth) = 1979' ]);
>
> But, if you wish to use a variable in the comparison a different construct
> is required because there is no variable expansion within single quotes in
> perl:
> $year = 1979;
> $rs->search(\[ 'YEAR(date_of_birth) = ?', [ plain_value => $year ] ]);"

This is the kind of misunderstanding we need someone to explain to us.
First off, it's not about no variable expansion in single quotes. It's
about using DBI placeholders. You need to look those up because they
are what protects you from SQL Injection attacks.

> The second example in the cookbook, seems wrong. There are placeholders for
> two variables ('name' and 'YEAR(date_of_birth)') in the example SQL, but the
> DBIx code already has 'Bob' specified as the 'name'.
>
> As per my original request, an example showing something other than equals
> would help in understanding how to deal with greater than or less than
> tests. Even now, I wouldn't know where to put a '<' in this complicated
> construct, without an example.

Would it help you to know that the following two constructs are equivalent:

    * $rs->search({ foo => 1 })
    * $rs->search({ foo => { '=' => 1 } })

The first is actually transformed into the second in the SQL::Abstract
code. Another term for this is syntactic sugar.

> The SQL::Abstract documentation is worse. I've no idea what 'bindtype' 'I'
> have set, seeing as /I/ am not using SQL::Abstract, but DBIx. I appreciate
> it's a more generic module than DBIx, but it's confusing to be directed to
> sub-SELECT documentation with no example SQL.

It's not a more generic module that DBIC. It's an integral part of
DBIC. So integral, in fact, that the DBIC devteam took over
maintainership of SQL::Abstract about 2 years ago. That said, it's not
part of the DBIC project because it is used by other projects. How can
we improve that documentation? Do you think we should split up the
documentation into a manual of sorts? What sections should it have?
What sort of order should they be in?

Thanks,
Rob