the line
orderby = wrapper.order_by_clause = sql.ClauseList(*map(
wrapper.corresponding_column, select.order_by_clause.clauses))
should most be
orderby = sql.ClauseList(*map(wrapper.corresponding_column,
select.order_by_clause.clauses))
not exactly sure why i did that...
new diff attached
On 15/07/07, lei you <[EMAIL PROTECTED]> wrote:
>
> what is sql generated for the proper fix?
>
> attached is a patch that attempts the wrapper solution. (seems to work for
> both union/non-union selects)
>
> it basically does the following.
>
> wrap the original select (be it union or not) in _msorg
>
> if the original select has an order_by_clause,
> adapt the order_by_clause for use in _msorg
> remove the order_by_clause from the original select,
> then perform effectively
> select _ms.[columns involved]
> from (select _msorg.*, row_number() over ([adapted orderby_clause]) as
> _msrn
> from _msorg) as _ms
> where ...
>
> otherwise, the original select does not have an order_by_clause,
> perform
> select _ms.[columns involved]
> from (select _msorg.*, row_number() over (_msnull) as _msrn
> from _msorg, (select null as _msnull) as __msnull) as _ms
> where ...
>
> what other potential pitfall may there be?
>
> (using 0.4.0 doesn't seem to be an option for me,
> my current setup is tesla 0.2.4 on (elixir + sqlalchemy 0.39 + pylons
> 0.9.6),
> last time i tried sa 0.4, things didnt seem to want to work out,
> can't exactly remember what's broken (most probably elixir)
>
>
> On 15/07/07, Michael Bayer <[EMAIL PROTECTED]> wrote:
> >
> >
> >
> >
> > On Jul 14, 11:15 pm, "lei you" <[EMAIL PROTECTED] > wrote:
> > > # TODO: put a real copy-container on Select and copy, or somehow make
> > this
> > > # not modify the Select statement
> > >
> > > In the above comments, are we referring to a solution where the
> > original
> > > select statement is wrap inside another select statement where the
> > actual
> > > row_number function is applied?
> > >
> >
> > the proper fix for this is present in the 0.4 branch of SQLAlchemy.
> > I'd favor deferring the MS-SQL solution to 0.4 where it will be easier
> > to implement.
> >
> >
> > > >
> >
>
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
796,799c796,799
< if select.limit:
< s += "TOP %s " % (select.limit,)
< if select.offset:
< raise exceptions.InvalidRequestError('MSSQL does not support
LIMIT with an offset')
---
> # if select.limit:
> # s += "TOP %s " % (select.limit,)
> # if select.offset:
> # raise exceptions.InvalidRequestError('MSSQL does not support
> LIMIT with an offset')
842,848c842,884
< def visit_select(self, select):
< # label function calls, so they return a name in cursor.description
< for i,c in enumerate(select._raw_columns):
< if isinstance(c, sql._Function):
< select._raw_columns[i] = c.label(c.name + "_" +
hex(random.randint(0, 65535))[2:])
<
< super(MSSQLCompiler, self).visit_select(select)
---
> def visit_select(self, select):
> # label function calls, so they return a name in cursor.description
>
> for i,c in enumerate(select._raw_columns):
> if isinstance(c, sql._Function):
> select._raw_columns[i] = c.label(c.name + "_" +
> hex(random.randint(0, 65535))[2:])
>
> super(MSSQLCompiler, self).visit_select(select)
> self._limit_offset_interpolate(select)
>
> def visit_compound_select(self, select):
> super(MSSQLCompiler, self).visit_compound_select(select)
> self._limit_offset_interpolate(select)
>
> def _limit_offset_interpolate(self, select):
>
> if not hasattr(select, '_mssql_visit') and (select.limit is not None
> or select.offset is not None):
> select._mssql_visit = True
>
> if hasattr(select, "order_by_clause") and select.order_by_clause
> and select.order_by_clause.clauses:
> wrapper = select.alias("_msorg").select()
> # taming the original orderby clause
> orderby = sql.ClauseList(*map(wrapper.corresponding_column,
> select.order_by_clause.clauses))
> # remove the order_by_clause from original select
> select.order_by_clause = sql.ClauseList()
> else:
> # in case where no orderby is specified, use a dummy table
> orderby = "_msnull"
> wrapper = sql.select([select.alias('_msorg'),
> sql.literal_column("NULL").label(orderby).select().alias('__msnull')])
>
> wrapper.append_column(sql.literal_column("ROW_NUMBER() OVER
> (ORDER BY %s)" % orderby).label("_msrn"))
> limited = sql.select([c for c in wrapper.alias('_ms').c if c.key
> != '_msrn' and c.key != '_msnull'])
> offset = select.offset or 0
> limit = select.limit + offset
>
> if offset:
> limited.append_whereclause("_msrn > %d" % offset)
>
> if limit:
> limited.append_whereclause("_msrn <= %d" % limit)
>
> self.traverse(limited)
> self.strings[select] = self.strings[limited]
> self.froms[select] = self.froms[limited]
