[Dbix-class] Union and Limit

[Mike Raynham]

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