SQL Server Window Functions – Do ORMs Provide Interfaces?

ormsql serverwindow functions

Many of my friends as well as previous workplaces emphasized use of ORMs over working with SQL directly. I've been exposed to Laravals ORM (Eloquent), Silverstripe's ORM, Active record, and Entity Framework (kinda).

Now I'm writing SQL code that uses the OVER clause quite frequently and wondering if that were possible from typical ORMs as mentioned above. Do ORMs make the OVER clause accessible – or are there any ORMS that do that?

If so, would those ORMs make accessible all the OVER clause's aruguments (which I guess would be database specific)?

Best Answer

Not really.

While some ORMs may very well be able to utilize windowing functions you yourself write none of them will be able to generate window functions. ORMs map data to data. They are an Object Oriented to relational data-translation layer. Window functions are a data-processing feature. It's a declarative way to process a result set, they run in the last-step of result-set generation, docs:

If the query contains any window functions, these functions are evaluated after any grouping, aggregation, and HAVING filtering is performed. That is, if the query uses any aggregates, GROUP BY, or HAVING, then the rows seen by the window functions are the group rows instead of the original table rows from FROM/WHERE.

At the point a Window Function works, the authors of an ORM may as well map the syntax into their own result-set processing features.

Related Solutions

Postgresql – Working of window functions and idea window size for window function

The elementary difference is that window functions are applied to all rows in a result set to compute additional columns after the rest of the result set has been determined. No row is dropped. They are available since PostgreSQL 8.4.

The LIMIT and OFFSET clauses of the SELECT command on the other hand do not compute additional columns. They just pick a certain "window" of rows from the result set (in cooperation with the ORDER BY clause) and discard the rest. Have been there like for ever.

While certain tasks can be tackled with either of these tools, they are very different in nature.

For your simple task

sorting data on date and then bring the latest data first

you don't need either of them. Just add:

ORDER BY date_col DESC

According to your comment, you would need:

SELECT col1, col2
FROM   tbl
ORDER  BY date_col DESC
LIMIT  100   -- 100 latest rows
OFFSET 0;    -- just noise, but may be easier to code

Retrieve more:

...
LIMIT  100
OFFSET 100;  -- next page of 100 rows ...

Be sure to have an index on date_col in either case!

MySQL and window functions

MySQL does not support Window Functions(*). There is what we call "a poor man's window function" in the form of GROUP_CONCAT().

There are plenty of tricks using GROUP_CONCAT to emulate window functions. They are not as pretty (syntactically) and are sometimes too limited. I've written a few. See my blog post complaining about the missing window functions, and linking to various solutions based on GROUP_CONCAT.

In particular, Selecting a specific non aggregated column data in GROUP BY and SQL: selecting top N records per group, another solution might be of interest to you and could give you a kick start.

Things you should note about GROUP_CONCAT():

Can use DISTINCT
Can use ORDER BY ... ASC/DESC
Can set SEPARATOR
As any aggregation function - it discards NULL values; plenty tricks on that.

(*) Support for Window Functions has been added in MySQL 8

Best Answer

Not really.

Related Solutions

Postgresql – Working of window functions and idea window size for window function

MySQL and window functions

Related Question