Mike Raynham wrote:
On 09/11/10 16:14, Peter Rabbitson wrote:
Mike Raynham wrote:
I have tested it, and it returns the results I would expect. I think
the confusion comes from the order in which the WHERE expression is
produced. If I were to write the SQL myself, without DBIx, I'd probably
It's DBIx::Class. DBIx is a namespace with hundreds of modules in it,
it is not fair to the respective authors to default DBIx to DBIx::Class.
If you want to abbreviate DBIC is common.
Please accept my apologies. I greatly appreciate all the work that has
gone into DBIx::Class and all the other modules in CPAN. I didn't mean
to cause offence and will use DBIC or DBIx::Class in future.
swap around the column names and supplied dates, like this:
SELECT * FROM my_table WHERE
($start_date >= start_date AND $start_date <= end_date) OR
($end_date >= start_date AND $end_date <= end_date)
That way makes more sense to me, but I don't think I can write it like
that using DBIx (unless I opt for creating SQL directly).
I don't see why not... ? Explain what do you perceive the holdup would
be.
It's not that I thought there would be a holdup, I just don't know how
to do it, or even if it is possible. I'm fairly new to perl, and very
new to the DBIx::Class, so have a lot to learn.
Given:
$rs->search({
start_date => { '<=', $start_date }
});
..the resulting SQL is along the lines of:
SELECT * FROM my_table WHERE start_date <= $start_date
I didn't realised that it was possible to switch the variable and table
column around to give this:
SELECT * FROM my_table WHERE $start_date => start_date
As you said that you didn't see why it wasn't possible, I've had another
look at the DBIx::Class::Manual::FAQ under ".. search with an SQL
function on the left hand side?". The "-nest" modifier may be the
answer, but I've not tried it yet.
-nest...? what...?
I still don't understand how this is different from anything you've showed
us in the thread so far. You said you want to do:
WHERE
($start_date >= start_date AND $start_date <= end_date)
OR
($end_date >= start_date AND $end_date <= end_date)
In SQLA it is assumed that the lhs is a column and the rhs is a bind value
(this is a *generalization*, there are all kinds of exceptions, but as a
generalization it will suffice)
So we rewrite this as:
(start_date <= $start_date AND end_date >= $start_date)
OR
(start_date <= $end_date AND end_date >= $end_date)
So then it boils down to:
[
{ start_date => { '<=', $start_date }, end_date => { '>=', $start_date } },
{ start_date => { '<=', $end_date }, end_date => { '>=', $end_date } },
]
That's it. No -nest, no braindamage.
Cheers
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[email protected]
