NAME

SQL::Abstract::Plugin::WindowFunctions - Window Function support for SQLA2!

SYNOPSIS

# pass this to an SQLA select list
{ -agg => {
   row_number => [],
    -over => {
      order_by     => { -desc => 'age' },
      partition_by => [ 'name', 'job' ]
    },
    -filter => { employed => 1, salary => { '>' => 9000 } }
  }
}
# row_number() FILTER (WHERE employed = ? AND salary > ?) OVER (PARTITION BY name, job ORDER BY age DESC)
# [1, 9000]

# You can use a window name in the -over definition
# to pass in a window clause in DBIC (this is a thing, you know), you need to use a bang override
$rs->search(undef, {
  columns => [{ # just the shortest way to specify select columns
    # note the hashref-ref; this is how we enable SQLA2 handling for select columns
    that_count => \{ -agg => {
      count => ['*'],
      -over => 'some_complex_window'
    }, -as => 'that_count' }
  }],   
  '!window' => [
    parent_window => {
      order_by => [qw/column1 column2/, {-asc => 'staircase'}],
    },
    some_complex_window => {
      base => 'parent_window',
      partition_by => ['things', 'stuff'],
      frame => 'rows between 1 preceding and 7 following',
    }
  ]
})
# SELECT count(*) OVER some_complex_window AS that_count
# FROM rs me
# WINDOW parent_window AS (ORDER BY column1, columns2, staircase ASC)
#        some_complex_window AS (parent_window PARTITION BY things, stuff ASC rows between 1 preceding and 7 following)
#

DESCRIPTION

This is a work in progress to support advanced window (and aggregate) functions in SQLA2.

EXPERIMENTAL

Using with DBIx::Class

In order to use this with DBIx::Class, you simply need to apply the DBIC-SQLA2 plugin, and then your SQLMaker will support this syntax!

Just some notes: in order to use the new -agg node in a select list in DBIC, you must pass it as a hashref-ref in order to activate the SQLA2 handling.

In order to pass in a window clause, you set it as an RS attribute prefixed with a '!' so that it gets rendered.

New Syntax

-agg node

The main entry point for the new handling is the -agg node. This takes two possible options, -filter and -over. The remaining key in the hash is assumed to be the name of the function being called.

-filter node

This is what generates the FILTER clause for the function call. It parses the arguments passed in as if they were being passed to a WHERE clause for the query.

-over node

This node handles the definition of the actual window. It takes a hashref of 0-4 named keys, or a plain string.

In the event that you pass a string, it renders as the name of a window from the WINDOW clause (see below for more details).

If it's a hashref, then the following keys are processed:

base

This is the parent window. It is a named window from the WINDOW clause, and you can define modifications in this window. Make sure to check if your DB actually supports this, and under what circumstances.

order_by

This is the order_by for the window. It gets parsed like the ORDER BY clause of a SELECT statment, meaning that you can use the special ordering form { -desc => 'column_name' }.

partition_by

This defines the "grouping" for your window function. It is parsed as any other list of columns names, so you should have roughly infinite power here.

frame

This defines the frame for the window. The syntax is so specific that there are no helpers, the string you pass here gets rendered directly. This may change in the future, of course.

WINDOW clauses

As shown in the synopsis, you define windows in the WINDOW clause of a SELECT by passing an array (b/c order matters) of pairs of name/window definition. You can be more explicit and pass an array of hashrefs with the keys -name and -definition.

The definition is processed as an -over node, so see above for details.