0

I have two classes in a Flask app joined on Users.taxonomy_id = Taxonomy.id as follows:

class User(db.Model):

  __tablename__ = 'users'
  id = db.Column(db.Integer, primary_key=True)

  # Data.
  taxonomy_id = db.Column(db.Integer, db.ForeignKey('taxonomy.id',
                        onupdate='CASCADE', ondelete='CASCADE'),
                        index=True, nullable=False)

  role = db.Column(db.Enum(*ROLE, name='role_types', native_enum=False),
                 index=True, nullable=False, server_default='member')
  active = db.Column('is_active', db.Boolean(), nullable=False,
                   server_default='1')
  username = db.Column(db.String(24), unique=True, index=True)
  organisation = db.Column(db.String(255), index=True)
  email = db.Column(db.String(255), index=True, nullable=False,
                  server_default='')
  password = db.Column(db.String(128), nullable=False, server_default='')

  def __init__(self, **kwargs):
    super(User, self).__init__(**kwargs)

class Taxonomy(db.Model):
  __tablename__ = 'taxonomy'
  id = db.Column(db.Integer, primary_key=True)

  # Relationships.
  user = db.relationship(User, uselist=False, backref='taxonomy',
                       passive_deletes=True)

  # Data.
  taxonomy = db.Column(db.String(80), index=True, nullable=False)
  title = db.Column(db.String(80), index=True, nullable=False)
  description = db.Column(db.String(500), index=True, nullable=False,
                        server_default='')
  parent = db.Column(db.Integer, nullable=False, default=0)
  member_count = db.Column(db.Integer, nullable=False, default=0)

  def __init__(self, **kwargs):
    super(Taxonomy, self).__init__(**kwargs)

And both work fine for queries run on them independently such as:

if form.validate_on_submit():
    current_user.username = request.form.get('username')
    current_user.save()

Or:

t = Taxonomy.query.filter(Taxonomy.id == current_user.taxonomy_id).first()

But I'm having trouble getting them to work as a joined query. I've been playing around for a while with different approaches and can't find anything definitive online that solves my problem. I want to create a joined query based on the classes and return the result. The following works but only returns data from the Users class and table rather than both the users and taxonomy classes:

records = User.query.join(
    Taxonomy, User.taxonomy_id == Taxonomy.id).filter(
    User.id == 102).first()

Can anybody please help?

N.Widds
  • 237
  • 1
  • 3
  • 12
  • Hello. Thanks for your response but I don't believe the question you've linked to as the same as mine. The example in the previous question is using classes that are not connected using backref and foreign keys. It's a different setup. Thanks. – N.Widds Nov 18 '19 at 23:10
  • Is your actual question then "how to eager/pre-load the `User.taxonomy` relationship"? The linked duplicates show how querying result tuples containing entities from both sides of a join works, which the current wording of the question seemed to be after. – Ilja Everilä Nov 19 '19 at 10:40

0 Answers0