sqlalchemy DataTables::"No data available in table" when using self-joined table - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: Web Scraping & Web Development (https://python-forum.io/forum-13.html) +--- Thread: sqlalchemy DataTables::"No data available in table" when using self-joined table (/thread-14282.html) |
sqlalchemy DataTables::"No data available in table" when using self-joined table - Asma - Nov-22-2018 Hello, I need to run a query with sqlalchemy on self-joined table, I tried to use alias as suggested by many forums but no data displayed. Please find below my code: models.py class Employee(db.Model): __tablename__ = 'employee' #self referenced table id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(200), nullable=False) surname = db.Column(db.String(200), nullable=False) email = db.Column(db.String(200), nullable=False) id_emp_sup = db.Column(db.Integer, db.ForeignKey("employee.id")) #emp_sup = db.relationship('Employee',backref = 'employee') #emp_sup = db.relationship('Employee', foreign_keys="[id_emp_sup]") id_structure = db.Column(db.Integer, db.ForeignKey("structure.id")) id_function = db.Column(db.Integer, db.ForeignKey("function.id")) def __init__(self, name, surname, email, id_emp_sup, id_structure): self.name = name self.surname = surname self.email = email self.id_emp_sup = id_emp_sup self.id_structure = id_structure self.id_function = id_function views.py Emp = db.aliased(Employee) columns = [ ColumnDT(Employee.name +' '+ Employee.surname), ColumnDT(Emp.name +' '+ Emp.surname) ] result = db.session.query().select_from(Employee).join(Emp) params = request.args.to_dict() rowTable = DataTables(params, result, columns) return jsonify(rowTable.output_result()) I want to display the full name of each employee with his manager (identified by id_emp_sup) by using sqlalchemy datatable. |