[PonyORM-list] Separating projection and filter in Query?

[Alexander Kozlovsky]

Hi, Tomislav!

In your first example

    q = select(p.name for p in Person)
    q = q.filter(lambda p: p.age > 30)

The filter didn't work, because the argument "p" used in lambda refers to
the entire result of the first query, that is, to "p.name". Because of
this, an error is occurred: attribute "p.name" doesn't have sub-attribute
"age".

To fix this, we should refer to the iterator names of the original select.
It is possible if we write lambda without arguments as in the following
example:

    q = select(p.name for p in Person)
    q = q.filter(lambda: p.age > 30)

When lambda doesn't receive any arguments, it refers to the names used in
the original select, so it is possible to refine query with any filtering
criteria.

As per the second example:

    q2 = select(p.name for p in q1)

Pony doesn't support such queries right now, but we plan to add them later.


> Is it possible to separate a projection part from the filtering part of
the query?
> The use for this is that I could have the same filtering logic (possibly
complex, with quite a lot of predicates), but get different results (for
example a list of key/value for a dropdown list and a larger set of
attributes to show on some grid).

We can add a way to specify projection, but at first we should understand
what is the right API to do such projection. There are two options:

1) A programmer can specify the desired attributes when they write a query.
In this case a query returns entity objects, but internally they contains
specified attributes only. Other attributes behave as if they are lazy -
their values will be loaded by a separate query if you'll try to access
them.

Here is a possible example of such API:

    q = select(p for p in Person)
    q = q.filter(lambda p: p.age > 30)
    q = q.load_only(Person.name, Person.age)  # also primary key Person.id
is always loaded
    for person in q:
        print person.id, person.name, person.age
        print person.tel   # oops, this attribute is not loaded - separate
query will be sent to the database

2) Another option would be to have the map() query method which can
transform query result:

    q = select(p for p in Person)
    q = q.filter(lambda p: p.age > 30)
    q = q.map(lambda p: (p.name, p.age))
    for name, age in q:
        print name, age

In this case, you don't receive partially-loaded object from the query, but
instead you get attribute values. This way only the specified attributes
will be loaded from the database.

What do you think, which API option solves your task better?




On Thu, Aug 14, 2014 at 4:52 AM, Tomislav Tustonic <ttustonic at outlook.com>
wrote:

> Hello
>
>
>
> First, thanks for fixing a bug with the lambda expressions and closures.
> Also, to_dict method is quite handy.
>
> I'm continuing with my exploration of the querying in Pony orm, and I have
> a following question/problem:
>
> Is it possible to separate a projection part from the filtering part of
> the query?
>
> For example I'd like to find only the names of the people older than 30
> years, and have it all executed in the database query.
>
> Something like this:
>
>
>
> q = select(p.name for p in Person)   #
> q = q.filter(lambda p: p.age > 30)
>
>
>
> or, even better:
>
> q = select(p for p in Person)
>
> q1 = q.filter(lambda p: p.age > 30)
>
> q2= select(p.name for p in q1)
>
>
>
> none of these, obviously, don't work, but you get the idea.
>
> The closest I can get to this is to pick only the names from the q1, but
> in this case all the fields are retrieved from the database.
>
> The use for this is that I could have the same filtering logic (possibly
> complex, with quite a lot of predicates), but get different results (for
> example a list of key/value for a dropdown list and a larger set of
> attributes to show on some grid).
>
>
>
> So, is something like this possible, and if not, are you planning to add
> this feature?
>
>
>
> Thanks in advance,
>
> Tom
>
>
>
> _______________________________________________
> ponyorm-list mailing list
> ponyorm-list at ponyorm.com
> /ponyorm-list
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: </ponyorm-list/attachments/20140814/35cfa3fb/attachment.html>