SELECT DISTINCT

[Ruben Nielsen]

Any plans on adding SELECT DISTINCT? I see in the code, that there is a TODO comment in place.

For other missing features like Postgres specific functions and types, I've been able to add my own classes in a support library and use them, but there is no clear way to replace you Select definition with my own, without altering your source.

Best I can do for now is to create a subclass of your Select, add my desired functionality and then monkey-patch sql.From.select function like

class MySelect(sql.Select):

    # ...

def monkey_select(self, *args, **kwargs):

    return MySelect(args, from_=self, **kwargs)

sql.From.select = monkey_select

[Cédric Krier]

On 2016-09-14 00:10, Ruben Nielsen wrote:
> Any plans on adding SELECT DISTINCT? I see in the code, that there is a
> TODO comment in place.

Usually DISTINCT can be avoided by using a GROUP BY.
But it should not be too difficult to implement.

> For other missing features like Postgres specific functions and types, I've
> been able to add my own classes in a support library and use them, but
> there is no clear way to replace you Select definition with my own, without
> altering your source.
>
> Best I can do for now is to create a subclass of your Select, add my
> desired functionality and then monkey-patch sql.From.select function like
>
> class MySelect(sql.Select):
> # ...
>
> def monkey_select(self, *args, **kwargs):
> return MySelect(args, from_=self, **kwargs)
>
> sql.From.select = monkey_select

Yes this was not planned to support Select replacement.
Indeed what do you try to add?

--
Cédric Krier - B2CK SPRL
Email/Jabber: cedric...@b2ck.com
Tel: +32 472 54 46 59
Website: http://www.b2ck.com/

[Cédric Krier]

On 2016-09-14 10:35, Cédric Krier wrote:
> On 2016-09-14 00:10, Ruben Nielsen wrote:
> > Any plans on adding SELECT DISTINCT? I see in the code, that there is a
> > TODO comment in place.
>
> Usually DISTINCT can be avoided by using a GROUP BY.
> But it should not be too difficult to implement.

Here is: https://python-sql.tryton.org/bug53

[Ruben Nielsen]

I ended up creating my subclass of Select, copy/pasting the __init__ and __str__ methods and filled in my own functionality, and then monkey-patched it as described above.

It is by no means full featured, but it works alright for my use case. I've posted my implementation below for your viewing pleasure ;)

class SelectWithDistinct(sql.Select):
    """Select definition that allows for DISTINCT queries.

    DISTINCT is - unfortunately - not yet available in the python-sql module,
    so we implement this ourselves in this class, which can be monkey-patched
    into the python-sql library. Not ideal, but it is robust enough.

    The entry point for the monkey-patching would be in sql.From.select(..)
    This method needs to be replaced with one that uses this Select
    implementation instead of the default one.
    """
    __slots__ = ('_columns', 'distincts', '_where', '_group_by', '_having',
                 '_for_', 'from_')

    def __init__(self, columns, distincts=None, from_=None, where=None, group_by=None,
                 having=None, for_=None, **kwargs):
        self._columns = None
        self._where = None
        self._group_by = None
        self._having = None
        self._for_ = None
        super(SelectWithDistinct, self).__init__(columns, **kwargs)
        self.distincts = distincts
        self.columns = columns
        self.from_ = from_
        self.where = where
        self.group_by = group_by
        self.having = having
        self.for_ = for_

    def __str__(self):
        if (
            Flavor.get().limitstyle == 'rownum'
            and (self.limit is not None or self.offset is not None)
        ):
            return self._rownum(str)

        with AliasManager():
            from_ = str(self.from_)
            if self.columns:
                columns = ', '.join(map(self._format_column, self.columns))
            else:
                columns = '*'

            if self.distincts is not None:
                if len(self.distincts) == 0:
                    distinct = 'DISTINCT '
                else:
                    distinct = 'DISTINCT ON ({}) '.format(
                        ', '.join(map(self._format_column, self.distincts))
                    )
                columns = distinct + columns

            where = ''
            if self.where:
                where = ' WHERE ' + str(self.where)
            group_by = ''
            if self.group_by:
                group_by = ' GROUP BY ' + ', '.join(map(str, self.group_by))
            having = ''
            if self.having:
                having = ' HAVING ' + str(self.having)
            window = ''
            windows = [f.window for f in self._window_functions()]
            if windows:
                window = ' WINDOW ' + ', '.join(
                    '"%s" AS (%s)' % (w.alias, w) for w in windows)
            for_ = ''
            if self.for_ is not None:
                for_ = ' ' + ' '.join(map(str, self.for_))
            return (self._with_str()
                    + 'SELECT %s FROM %s' % (columns, from_)
                    + where + group_by + having + window + self._order_by_str
                    + self._limit_offset_str + for_)

# Monkey-patch the python-sql library with an implementation of Select
# that supports DISTINCT
def monkey_select(self, *args, **kwargs):
    return SelectWithDistinct(args, from_=self, **kwargs)
sql.From.select = monkey_select

[Cédric Krier]

On 2016-09-20 23:24, Ruben Nielsen wrote:
> I ended up creating my subclass of Select, copy/pasting the __init__ and
> __str__ methods and filled in my own functionality, and then monkey-patched
> it as described above.
>
> It is by no means full featured, but it works alright for my use case. I've
> posted my implementation below for your viewing pleasure ;)

I see it is only to add the distinct feature.
So it will be good to have your feedback on this patch as the
implementation is a little bit different:
https://python-sql.tryton.org/bug53


PS: Please do not top-post on this mailing list, see
http://groups.tryton.org/netiquette