sqlalchemy/sqlmodel: return total count of all rows from multiple table joins?-CodePudding

I'm joining multiple tables and returning all the columns fine. I want to now return the total count of all rows

select(Transaction.id, func.count(Transaction.id)).label('count_1'))
        .join(TransactionType).join(xxx).group_by(Transaction.id)

which returns an SQL like:

SELECT transaction.id, count(transaction.id) AS count_1 
FROM transaction INNER JOIN xxx ON xxx.`xId` = transaction.`xId` INNER JOIN 
transaction_type ON transaction_type.id = transaction.`transaction_type` GROUP BY transaction.id

However, what this does is just return count_1 as 1 for every single row. I was expecting it to return count of all the rows. If I don't use group_by then it will complain but I suspect this is why this is happening.

How can I get the total row count in SQLalchemy/SQLmodel ?

CodePudding user response：

I think it's because of typo in parenthesis closing. The label should be for the func.count as follows.

select(Transaction.id, func.count(Transaction.id).label('count_1')) # look where .label is applied.
    .join(TransactionType).join(xxx).group_by(Transaction.id)

CodePudding user response：

ended up just grabbing the length of len(session.exec(sql).all())