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.
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.