Using SQLalchemy distinct function on Flask DB class

Question

Is it possible to use the distinct function with out using the "session" class in sqlalchemy? Why or why not? The answer I found here:https://stackoverflow.com/a/35718413/10210394 seems like it should work but it doesn't work for me. See example below:

class Example(db.Model):
     title = db.Column(db.String(140))
     extra = db.Column(db.String(140))

e1 = Example(title='foo', extra='bar')
e2 = Example(title='hoo', extra='bar')

db.session.add_all([e1, e2])
db.session.commit()

test = []
for value in Example.query.distinct(Example.extra):
    test.append(value.extra)

print(len(test))
...2

The result should be 1 not 2. Looking at the docs for distinct(), I feel like this should work. What am I missing?

Why would the result be 1 here? There are two distinct values of `Example.extra`: `'bar'` and `'raa'`. BTW, this code does not run, it has errors. It would be nice to test the code before posting it here. — zvone, Aug 16 '18 at 07:26
@zvone sorry about that, I was copying the code over from my project and trying to simplify it as much as possible. It should be fixed now. I will definitely run it before I submit next time. The extra variables are supposed to be the same (Example.extra: 'bar' and 'bar') which still gives a result of 2. — Thomas Morrison, Aug 16 '18 at 17:02
You'll find the answer here: https://stackoverflow.com/questions/17223174/returning-distinct-rows-in-sqlalchemy-with-sqlite — Ilja Everilä, Aug 16 '18 at 17:50

Thomas Morrison · Answer 1 · 2018-08-19T19:09:29.913

6

I found several ways to accomplish what I wanted. Looking at this answer https://stackoverflow.com/a/17224056/10210394, in order to get the distinct values distinct() has to be called explicitly on the values. To do this in the class form one can use "with_entities"

Example.query.with_entities(Example.extra).distinct()

Also, in my case, a simple group_by also achieves the desired results(but it may not be the best/most portable solution as pointed out in the comments.)

 test = []
    for value in Example.query.group_by(Example.extra):
        test.append(value.extra)

 print (test)
 ....['bar']

edited Aug 19 '18 at 19:09

answered Aug 16 '18 at 20:56

Thomas Morrison

587
7
13

Please note that the GROUP BY method you're using produces indeterminate results for the other columns, or in other words it is not guaranteed which row you get as a result from a group, because `Example.extra` does not determine the other columns, or there's no functional dependency. Most SQL DBMS would outright reject that query. – Ilja Everilä Aug 17 '18 at 04:55
1

@IljaEverilä thanks for the heads up. I saw that you commented on the other question https://stackoverflow.com/a/17224056/10210394, about this issue. I also noticed that in that example the distinct(Tag.name) was not doing anything. In my case, I am not concerned about the indeterminate results, so would it still be acceptable to use that solution? Or would it be better to go with the first option to not risk being incompatible with other DBMS? – Thomas Morrison Aug 17 '18 at 20:30
In my opinion the `SELECT DISTINCT extra` approach is more correct, but in a pragmatic sense like in the counting case you don't get indeterminate results with the grouping, because you ignore the other result columns. But as you yourself mention in a way, it'd perhaps be better to learn to use the portable solution. – Ilja Everilä Aug 18 '18 at 05:45
Great thanks for all of your help in pointing me in the right direction! – Thomas Morrison Aug 19 '18 at 19:10

Using SQLalchemy distinct function on Flask DB class

1 Answers1