where clause appears to modify the query

[scott2b]

I am not sure if this is planned behavior, but I was not expecting it. My general thinking is that I ought to be able to chain clauses onto an existing query and issue different variations on a query ... that the query itself would not change unless I re-assign it.

e.g.

Given some query q (I happen to be using a select query. Not sure if this behavior is the same for objects):

If I do something like this:

r_filtered = await q.where(MyTable.somefield > someval)

And then I issue the query subsequently:

r_unfiltered = await q

My expectation is that the 2nd one will return all of the query results, but what I am seeing is that the where clause seems to have affected the query itself, which will now only return results where somefield > someval.

Is this by design? What is the best way to execute multiple variations of a query? Is there some way to copy the query state so I don't have to rebuild it from scratch?

Edit: I am seeing the same behavior for object queries.

[dantownsend]

Yeah, it's an interesting one.

Ideally this would be possible:

query_1 = Band.select()
query_2 = query_1.where(Band.name == 'Pythonistas')
query_3 = query_2.where(Band.popularity > 2000)

>>> await query_1
>>> await query_2
>>> await query_3

But at the moment, chaining the methods modifies the original query. query / query_2 / query_3 are all references to the same query object.

To change this behaviour, we would have to copy the query each time a method is called on it. The problem with this is it would incur a performance hit.

This is a work around (I found some edge cases where it doesn't work for complex queries, but it seems OK for simple queries):

import copy

query_1 = Band.select()
query_2 = copy.deepcopy(query_1).where(Band.name == 'Pythonistas')

Maybe what we should do is add a copy method to queries:

query_1 = Band.select()
query_2 = query_1.copy().where(Band.name == 'Pythonistas')
query_3 = query_2.copy().where(Band.popularity > 2000)

What do you think?

[scott2b]

I think it would be useful. Although if you have to explicitly copy it like this, I'm not sure if it buys you anything over a deep copy. Perhaps the copy method could be smarter in some way, but I don't know anything about the implementation details to really say.

For my part, I just decided to abstract the functionality into a build_query function and I just rebuild the query that way. It is probably slightly less efficient than doing a copy. Not sure, but I suspect it is not too much different.

[scott2b]

I am also not entirely convinced that the performance gain is worth it here. We are talking about simply building queries, not actually executing them. The bottleneck in an orm is always going to be executing the queries. Presumably all other chainable ORMs do some kind of thing where they return a copy of the original thing? This would also be true for example in a dataframe library which is going to be much much less memory efficient. I'm just not sure what kind of efficiency we are talking here. Building queries doesn't really seem like something that would require extreme shortcuts. But then, I've never built an ORM, so I am not really speaking from any real sense of experience.

[scott2b]

Finally, I do think this needs to be trumpeted quite loudly in the docs if it is not already. This strikes me as a bit non-idiomatic. Do other libraries do this kind of thing? I think the general expectation is that if a method returns something then it is not going to munge the original thing. If it did not return a query object then it would be more obvious ... but then I guess not chainable.

My concern here is that it seems if one is not aware of this behavior it could lead to some rather painfully subtle bugs. I sort of just got lucky in catching my where situation on this one.

[scott2b]

All that said ... I am not trying to come across as negative. Just thinking through the problem out loud. The copy method you propose does make for a clean API. I think that and just making sure that the query docs make it clear that the query object is modified -- those 2 things together seem reasonable to me. fwiw

[dantownsend]

Yeah, good points - at the very least it needs documenting, but ideally some new features implementing.