[FIXED] SQLAlchemy returning additional columns with paginate() doesn't work

Issue

I have the following 3 models:

class User(db.Model):
    __tablename__ = "tbl_users"
    id = db.Column(db.BigInteger, primary_key=True)
    type_id = db.Column(db.Integer, db.ForeignKey("tbl_user_type.id"))
    first_name = db.Column(db.String, nullable=False)
    last_name = db.Column(db.String, nullable=False)
    email = db.Column(db.String, nullable=False, unique=True)
    password = db.Column(db.String, nullable=False)
    user_logs = db.relationship("DashboardLog", backref="log_owner")

class DashboardLog(db.Model):
    __tablename__ = "tbl_logs_dashboard"
    id = db.Column(db.BigInteger, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey("tbl_users.id"))
    event_type = db.Column(db.Integer, db.ForeignKey("tbl_dashboard_event_types.id"))
    table_name = db.Column(db.String)
    search_key = db.Column(db.String)
    event_time = db.Column(db.DateTime)

class DashboardEventType(db.Model):
    __tablename__ = "tbl_dashboard_event_types"
    id = db.Column(db.BigInteger, primary_key=True)
    type_name = db.Column(db.String)
    logs = db.relationship("DashboardLog", backref="event_type_id")

I join them and after the following operations I get a result:

logs = model.query.order_by(time_attr.desc()).join(User).join(DashboardEventType).paginate(page=page, per_page=per_page)

data = []

    for log in logs.items:
      log = log.__dict__
      print(log)
      log = {k:v for k,v in log.items() if k != "_sa_instance_state"}
      #print(log)
      #print(log.__dict__, file=sys.stderr)
      data.append(log)
return data

Here's the output to this query and operation:

{
            "event_time": "2022-09-22 13:45:48",
            "event_type": 6,
            "id": 13,
            "search_key": null,
            "table_name": null,
            "user_id": 3
        },
        {
            "event_time": "2022-09-22 13:18:57",
            "event_type": 1,
            "id": 12,
            "search_key": null,
            "table_name": null,
            "user_id": 3
        },...

What I'd like to do however, is instead of getting the user_id and event_type fields, just using these to find matching rows and then getting different and more descriptive fields from the foreign tables. Here's the raw SQL version of what I want to achieve:

SELECT
tbl_logs_dashboard.table_name AS tbl_logs_dashboard_table_name, 
tbl_logs_dashboard.search_key AS tbl_logs_dashboard_search_key, 
tbl_logs_dashboard.event_time AS tbl_logs_dashboard_event_time,
tbl_users.email,
tbl_dashboard_event_types.type_name
FROM tbl_logs_dashboard JOIN tbl_users ON tbl_users.id = tbl_logs_dashboard.user_id 
JOIN tbl_dashboard_event_types ON tbl_dashboard_event_types.id = tbl_logs_dashboard.event_type 
ORDER BY tbl_logs_dashboard.event_time DESC

I tried to define the columns that I want in the sqlalchemy query like this:

logs = model.query(User.email, DashboardEventType.type_n).order_by(time_attr.desc()).join(User).join(DashboardEventType).paginate(page=page, per_page=per_page)

But then I get a "BaseQuery object is not callable" error.

EDIT: I was able to achieve what I wanted with the followint sqlalchemy query:

model.query.order_by(time_attr.desc())
        .join(User)
        .join(DashboardEventType)
        .add_columns(User.email, DashboardEventType.type_name)
        .paginate(page=page, per_page=per_page)
        ) 

Solution

The following sqlalchemy achieves the wanted result:

model.query.order_by(time_attr.desc())
        .join(User)
        .join(DashboardEventType)
        .add_columns(User.email, DashboardEventType.type_name)
        .paginate(page=page, per_page=per_page)
        ) 

Answered By - m.yagmur