etc/having-cases.sql

   1
   2 -- CASE 1
   3 SELECT users.name, SUM(commission) AS total
   4   FROM commissions
   5     INNER JOIN users ON ( commissions.recipient_id = users.id )
   6   WHERE commissions.entry_date > '2007-01-01'
   7   GROUP BY commissions.recipient_id
   8     HAVING total > 500
   9   ORDER BY total DESC;
  10
  11 order_by { $_->total }
  12   select { $_->users->name, [ total => sum($_->aggregates->commission) ] }
  13    where { sum($_->aggregates->commission) > 500 }
  14 group_by { $_->commissions->recipient_id }
  15     join { $_->users->id == $_->commissions->recipient_id }
  16       [ users => expr { $_->users  } ],
  17       [ commission => expr { $_->commissions } ];
  18
  19 my $total = [ -sum => [ -name => 'commission' ] ];
  20
  21 [
  22   -select,
  23   [
  24     -list,
  25     [ -name => qw(users name) ],
  26     $total
  27   [
  28     -where,
  29     [ '>', $total, [ -value, 500 ] ],
  30     [
  31       -group_by,
  32       [ -name, qw(commissions recipient_id) ],
  33       [
  34         -where,
  35         [ '>', [ -name, qw(commissions entry_date) ], [ -value, '2007-01-01' ] ],
  36         [
  37           -join,
  38           ...
  39       ],
  40     ],
  41   ],
  42   ],
  43 ]
  44
  45 -- CASE 2
  46 SELECT users.name, aggregates.total FROM (
  47        SELECT recipient_id, SUM(commission) AS total
  48        FROM commissions
  49        WHERE commissions.entry_date > '2007-01-01'
  50        GROUP BY commissions.recipient_id
  51        HAVING total > 500
  52    ) AS aggregates
  53 INNER JOIN users ON(aggregates.recipient_id = users.id)
  54 ORDER BY aggregates.total DESC;
  55
  56 order_by { $_->aggregates->total }
  57   select { $_->users->name, $_->aggregates->total }
  58     join { $_->users->id == $_->aggregates->recipient_id }
  59       [ users => expr { $_->users  } ],
  60       [ aggregates =>
  61           expr {
  62               select { $_->recipient_id, [ total => sum($_->commission) ] }
  63                where { sum($_->commission) > 500 }
  64             group_by { $_->recipient_id }
  65                where { $_->entry_date > '2007-01-01' }
  66                 expr { $_->commissions }
  67           }
  68       ];
  69
  70
  71 -- CASE 3
  72 SELECT users.name, aggregates.total FROM (
  73        SELECT recipient_id, SUM(commission) AS total
  74        FROM commissions
  75        WHERE commissions.entry_date > '2007-01-01'
  76        GROUP BY commissions.recipient_id
  77    ) AS aggregates
  78 INNER JOIN users ON(aggregates.recipient_id = users.id)
  79 WHERE aggregates.total > 500
  80 ORDER BY aggregates.total DESC
  81
  82
  83 order_by { $_->aggregates->total }
  84   select { $_->users->name, $_->aggregates->total }
  85    where { $_->aggregates->total > 500 }
  86     join { $_->users->id == $_->aggregates->recipient_id }
  87       [ users => expr { $_->users  } ],
  88       [ aggregates =>
  89           expr {
  90               select { $_->recipient_id, [ total => sum($_->commission) ] }
  91             group_by { $_->recipient_id }
  92                where { $_->entry_date > '2007-01-01' }
  93                 expr { $_->commissions }
  94           }
  95       ];