[PonyORM-list] Algebra of sets

Alexander Kozlovsky alexander.kozlovsky at gmail.com
Mon Oct 21 18:37:41 UTC 2013

Hi Vsevolod!

I think it is possible to implement this feature in Pony ORM with the
following API:

q1 = select(s for s in Student if ...)
q2 = select(s for s in Student if ...)
q3 = q1.union(q2)
q4 = q1.intersect(q2)
q5 = q1.except(q2)

The implementation will take 3-5 work days (or may be longer, because MySQL
has no direct support of INTERSECT and EXCEPT and requires to write a
workaround via joins), and currently we have more urgent tasks (such as
migration support), so I don't think this feature can be implemented during
this year. Please add this feature to our issue list on GitHub, and we'll
implement it when time permits.

By the way, I fear that some programmers may overuse such 'algebra' and as
a result construct inefficient queries. Let's consider the next query:


In many situations, the next equivalent query will be faster:

SELECT * FROM T1 WHERE X > 100 and Y < 200

So in case of implementing user-restricted document search, it may be more
efficient to implement it in the following way, via incremental filtering
of the same query:

def document_search(current_user, specified_keyword=None,
        part_of_title=None, document_type=None,
        min_size=None, max_size=None):

    query = select(d for d in Documents
        if d.owner == current_user or current_user in d.editors)

    if specified_keyword is not None:
        query.filter(lambda d: specified_keyword in d.keywords)

    if part_of_title is not None:
        query.filter(lambda d: part_of_title in d.title)

    if document_type is not None:
        query.filter(lambda d: d.type = document_type)

    if min_size is not None:
        query.filter(lambda d: d.size >= min_size)

    if max_size is not None:
        query.filter(lambda d: d.size <= max_size)

    return query

This way of filtering should works in the development version of Pony (it
was accidentally broken in the last release 0.4.8, but will be restored in
the upcoming release 0.4.9)

On Mon, Oct 21, 2013 at 2:27 PM, Vsevolod Novikov <nnseva at gmail.com> wrote:

> Hi All,
> It would be useful to implement some kind of 'algebra of sets':
> intersection, union, and subtraction of sets defined by two abstract
> queries.
> Let I am writing a subsystem which should accept some queries A and B and
> do something with it's intersection. Is it possible someway?
> The particular (simplified) usecase: Let we have Users, Groups, and
> Documents belonging to User (individual), or Group (shared).
> We have wrote a 'user access' subsystem which restricts access to
> Documents and returns some DocumentSet (query?) object (document_set_a)
> which refers to all Documents belonging to this User directly or through
> the Group.
> Also we have wrote some other subsystem, let it be 'document index', which
> returns other DocumentSet (query?) object (document_set_b) which refers to
> all Documents containing passed keywords.
> Now we want to write user-restricted document index: the user passes us
> keywords and wants to see all Documents belonging to him and containing
> these keywords. So we should return a DocumentSet which is an intersection
> of document_set_a and document_set_b.
> Regards,
> Vsevolod
> _______________________________________________
> ponyorm-list mailing list
> ponyorm-list at ponyorm.org
> /ponyorm-list
-------------- next part --------------
An HTML attachment was scrubbed...
URL: </ponyorm-list/attachments/20131021/a28ed81e/attachment.html>

More information about the ponyorm-list mailing list