0

i'm running into issues with the following, and I'm wondering if it is even possible.

I have a flask-admin adminview setup, with an extra form field which shows a dropdown based on a specific column (category) in the sql model. See code for clarification:

model:

class Item(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    name = db.Column(db.String(128), index = True)
    category = db.Column(db.String(16))

I have the extra form field in Flask-Admin as follows:

    form_extra_fields = {
    'category': QuerySelectField(
        label='Categories',
        query_factory = lambda: db.session.query(Item),
        get_label = 'category',
        )
    }

This all works fine except if there are duplicates in the category column, then the dropdown is populated with these duplicate values. Is it possible to remove those duplicates from dropdown, or at least only show the unique values?

gittert
  • 1,238
  • 2
  • 7
  • 15
  • I don't know how it fits in (thus a comment only) but what if you only select the distinct categories? something like `db.session.query(distinct(Item.category))` – DinoCoderSaurus Dec 22 '18 at 01:54
  • Hi, thanks for your input. Since my post I've tested all kinds of distinct formats, not your suggestion yet though. But unfortunately, it results in this error: sqlalchemy.orm.exc.UnmappedInstanceError: Class 'sqlalchemy.util._collections.result' is not mapped. As I understand from this error, the query factory cannot return columns. Might it be possible to remove duplicates in the get_label part? – gittert Dec 22 '18 at 09:21
  • This seems to give the result I'm looking for in sqlite database: db.session.query(Item).group_by(Item.category). On MySQL production database it returns an error: sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1055, "Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column – gittert Dec 22 '18 at 16:03
  • That seems to be related mysql setting "ONLY_FULL_GROUP_BY" as discovered [here](https://stackoverflow.com/questions/37951742/1055-expression-of-select-list-is-not-in-group-by-clause-and-contains-nonaggr) from internet search of error message. I hope that helps, or sends you on the right quest. I cannot offer help with MySQL. – DinoCoderSaurus Dec 22 '18 at 17:02
  • Yeah, looking into it. I understand why the error occurs, but looking at https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql/38551525#38551525, there should be a solution in changing the query. But I cannot find the sqlalchemy way of doing that. – gittert Dec 22 '18 at 17:23
  • Well, I fixed it by overriding QuerySelectField and its methods to prevent duplicates in the labels. I'm not sure if this is the best way, but the fix itself was actually quite easy to implement. – gittert Dec 24 '18 at 12:14
  • Perhaps create an answer to that effect (and accept it after the requisite waiting period)? Could be useful to to others. – DinoCoderSaurus Dec 24 '18 at 12:40

1 Answers1

3

Basically I solved this by overriding a class method in QuerySelectField class as follows, by appending unique labels to a list and check if every next label is in that list. I'm still thinking there should be a better way though...

def iter_choices(self):
    labels = []     
    if self.allow_blank:           
        yield ('__None', self.blank_text, self.data is None)        

    for pk, obj in self._get_object_list():      

    if self.get_label(obj) not in labels:                        

        labels.append(self.get_label(obj))                
        yield (pk, self.get_label(obj), obj == self.data)
gittert
  • 1,238
  • 2
  • 7
  • 15