python - Filter elements by optional lists of related elements

Question

I have a feeling that I've made things more complex than they need to be - this can't possibly be such a rare case. It seems to me that it should be possible - or perhaps that I'm doing something fundamentally wrong.

The issue at hand is this: I've declared a database element, Element, which consists of about 10 many-to-many relations with other elements, one of which is Tag.

I want to enable the user of my application to filter Element by all of these relations, some of them or none of them. Say the user wants to see only Elements which are related to a certain Tag.

To make things even more difficult, the function that will carry out this objective is called from a graphql API, meaning it will recieve ID's instead of ORM objects.

I'm trying to build a resolver in my Python Flask project, using SQLAlchemy, which will provide an interface like so:

# graphql request
query getElements {
  getElements(tags:[2, 3] people:[8, 13]) {
    id
  }
}

# graphql response
{
  "data": {
    "getElements": [
      {
        "id": "2"
      },
      {
        "id": "3"
      },
      {
        "id": "8"
      }
    ]
  }
}

I imagine the resolver would look something like this simplified pseudo-code, but I can't for the life of me figure out how to pull it off:

def get_elements(tags=None, people=None):
  args = {'tags' : tags, 'people' : people}
  if any(args):
    data_elements = DataElement.query.filter_by(this in args) # this is the tricky bit - for each of DataElements related elements, I want to check if its ID is given in the corresponding argument
  else:
    data_elements = DataElement.query.all()
  
  return data_elements

Here's a peek at the simplified database model, as requested. DataElement holds a lot of relations like this, and it works perfectly:

class DataElement(db.Model):
  __tablename__ = 'DataElement'
  id = db.Column(db.Integer, primary_key=True)
  tags = db.relationship('Tag', secondary=DataElementTag, back_populates='data_elements')

class Tag(db.Model):
  __tablename__ = 'Tag'
  id = db.Column(db.Integer, primary_key=True)
  data_elements = db.relationship('DataElement', secondary=DataElementTag, back_populates='tags')

DataElementTag = db.Table('DataElementTag',
  db.Column('id', db.Integer, primary_key=True),
  db.Column('data_element_id', db.Integer, db.ForeignKey('DataElement.id')),
  db.Column('tag_id', db.Integer, db.ForeignKey('Tag.id'))
)

Please, ORM wizards and python freaks, I call upon thee!

标签： pythonsqlsqlalchemyormflask-sqlalchemy

Answer 1

I've solved it in a rather clunky manner. I suppose there must be a more elegant way to pull this off, and am still holding out for better answers.

I ended up looping over all the given arguments and using eval() (not on user input, don't worry) to get the corresponding database model. From there, I was able to grab the DataElement object with the many-to-many relationship. My final solutions looks like this:

args = {
        'status' : status,
        'person' : people,
        'tag' : tags,
        'event' : events,
        'location' : locations,
        'group' : groups,
        'year' : year
    } # dictionary for args for easier data handling

if any(args.values()):
        final = [] # will contain elements matching criteria

        for key, value in args.items():
            if value:
                model = eval(key.capitalize()) # get ORM model from dictionary key name (eval used on hardcoded string, hence safe)
                for id in value:
                    filter_element = model.query.filter_by(id=id).one_or_none() # get the element in question from db
                    if filter_element:
                        elements = filter_element.data_elements # get data_elements linked to element in question
                        for element in elements:
                            if not element in final: # to avoid duplicates
                                final.append(element)

        return final