How to deal with aggregated columns like sum and count?

[saifraider]

I have a table Timepass which has two columns user_name and Income.

username --- income
saif---1000
saif---2000
zaib--1000
saif---1000
zaib---500

I want datatable output as
username -- sum -- count
saif----4000 -- 3
zaib----1500 -- 2

My server side code is....

columns = [
ColumnDT(Timepass.user_name),
ColumnDT(literal_column("'sum'")),
ColumnDT(literal_column("'count'"))
]
results = db.session.query(Timepass.user_name,func.sum(Timepass.income),func.count(Timepass.user_name)).group_by(Timepass.user_name)
params = request.args.to_dict()
rowTable = DataTables(params, results, columns)
return jsonify(rowTable.output_result())

I get an error when I click the sum or count column on frontend datatable.

$(document).ready(function(){
var table = $('#example').DataTable({
"processing": true,
"serverSide": true,
"ajax": "{{ url_for('home.data') }}",
"columns":[
{ data: "0" },
{ data: "1" },
{ data: "2" }
]
});

Name	Sum	count

Can you please help?

[tdamsma]

You can actually pass the queries in the columns as follows:

columns = [
  ColumnDT(Timepass.user_name),
  ColumnDT(func.sum(Timepass.user_name).label('Sum')),
  ColumnDT(func.count(Timepass.user_name).label('Count'))
]
query = db.session.query().select_from(Timepass).group_by(Timepass.user_name)

I think this will solve the issues

[saifraider]

It is not working.

So I should provide Timepass.user_name to both sum and count function instead of Timepass.sum and Timepass.count?

Also it is showing InvalidRequestError: Query contains no columns with which to SELECT from

Also can you specify the column names to be selected?

Can you provide a small working demo for aggregate functions in sqlalchemy-datatables?

[Pegase745]

@saifraider I added a working example (tested on SQLite and Postgres) that returns the sum(incomes) of users.
What's important here to notice is that I set global_search=False to the column definition of the sum, or else the SQLAlchemy query will add the aggregate function in the WHERE clause in order to make the field searchable and this raises an error.
For now it can be a quick solution for you until I take time to find a more transparent solution.

[saifraider]

    columns = [
        ColumnDT(Timepass.user_name),
        ColumnDT(func.sum(Timepass.income).label('Sum'), global_search=False),
        ColumnDT(func.count(Timepass.user_name).label('Count'), global_search=False)
   ]
query = session.query(Timepass.user_name, func.sum(Timepass.income).label('Sum'),
                      func.count(Timepass.user_name).label('Count')).group_by(Timepass.user_name)`
print(query[0])
params = request.args.to_dict()
rowTable = DataTables(params, query, columns)
print((rowTable.output_result()))
return jsonify(rowTable.output_result())`

I Used the following code and it produced correct results and sorting is also working fine. Thank you for the global_search=False parameter.

[Pegase745]

Great, you're welcome !

[tdamsma]

Perhaps we should also add some details on how searching works with aggregated columns in SQL, as this has confused users before. You technically can do this if you really want to, though I doubt you should. For an implementation with a CTE, see here: #83. The main issue is this ruins your performance for large tables.

[Pegase745]

I am not very familiar with CTE, but I agree that my solution is for last resort. I guess we should find a way to detect an aggregated column and unable filtering

[tdamsma]

I don't think we should autodetect use of aggregated columns (I doubt this is even feasible). If you use sqlalchemy-datatables you need a basic understanding of relational databases, there is just no way around that (Just listened to an interview with Mike Bayer where he explains why SQLAlchemy is a leaky abstraction by design).

But as the question has come up twice, so we can try to put more info in the docs, and suggest proper workarounds (either disable searching or pre-aggregate with a CTE)

[Pegase745]

Then we'll add more docs to our TODO list 👍
But first, I'll fix once and for all the recurring failing tests.