Hi,
I am using union from DBIx::Class::Helper::ResultSet::SetOperations, but
am struggling a little with the syntax. I'm hoping that someone can help.
I have three individual queries that need to be returned, and the rows
from each SELECT are to be returned one after the other. The DBMS is
MySQL, and I need to structure the query as per the examples shown at
the end of this page:
http://dev.mysql.com/doc/refman/5.1/en/union.html
I have used the '+as' attribute so that I may easily identify the rows
from each SELECT via 'get_column'.
I also need to place a LIMIT on the last two queries, and this is where
I am having difficulty. My code is as follows:
###
my $rs1 = $rs->search({
arrival => { '<=' => $now }, departure => { '>=' => $now }
}, {
'+select' => [\'"1" AS sort_col'],
'+as' => ['sort_col'],
columns => [qw( guest_id arrival departure )],
});
my $rs2 = $rs->search({
arrival => { '>' => $now }
}, {
'+select' => [\'"2" AS sort_col'],
'+as' => ['sort_col'],
columns => [qw( guest_id arrival departure )],
rows => 2,
});
my $rs3 = $rs->search({
arrival => { '>' => $due }
}, {
'+select' => [\'"3" AS sort_col'],
'+as' => ['sort_col'],
columns => [qw( guest_id arrival departure )],
rows => 2,
});
my $result = $rs1->union([ $rs2, $rs3 ])->search( undef, {
order_by => [qw( sort_col arrival )],
});
###
The problem I have encountered is that the individual SELECT statements
are not enclosed in parentheses, so LIMIT is being applied to the
overall result, and not the individual SELECTs. The SQL from the above
code is:
###
SELECT me.guest_id, me.arrival, me.departure, me.sort_col FROM (
SELECT me.guest_id, me.arrival, me.departure, "1" AS sort_col
FROM booking me WHERE ( (
arrival <= ? AND departure >= ?
) )
UNION
SELECT me.guest_id, me.arrival, me.departure, "2" AS sort_col
FROM booking me WHERE (
arrival > ?
) LIMIT 2
UNION
SELECT me.guest_id, me.arrival, me.departure, "3" AS sort_col
FROM booking me WHERE (
arrival > ?
) LIMIT 2
) me
ORDER BY sort_col, arrival
###
If I add parentheses around each SELECT, the query returns the expected
results:
###
SELECT me.guest_id, me.arrival, me.departure, me.sort_col FROM ( (
SELECT me.guest_id, me.arrival, me.departure, "1" AS sort_col
FROM booking me WHERE ( (
arrival <= ? AND departure >= ?
) )
) UNION (
SELECT me.guest_id, me.arrival, me.departure, "2" AS sort_col
FROM booking me WHERE (
arrival > ?
) LIMIT 2
) UNION (
SELECT me.guest_id, me.arrival, me.departure, "3" AS sort_col
FROM booking me WHERE (
arrival > ?
) LIMIT 2
) ) me
ORDER BY sort_col, arrival
###
Is it a 'feature' of MySQL that it requires the additional parentheses?
Is there a way for me to add the parentheses to my search?
Thanks,
Mike
_______________________________________________
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]
