displaying custom column from sqlalchemy into datatable

[saifraider]

I have a front end datatable that has 6 columns( ID, first_name, last_name, value, full_name, multiply)

Suppose I have a model user and it has following data.

ID first_name last_name value 1 zack miller 10 2 ryan mint 20 5 jack thomas 30 4 john mac 10

I would like to have another two column generated on server side for the frontend datatable. 1)"full_name" which is first_name+"-"+last_name. 2)"multiply" which is ID*value

My datatable should be displayed as mentioned below:-

ID first_name last_name value full_name multiply 1 zack miller 10 zack-miller 10 2 ryan mint 20 ryan-mint 40 5 jack thomas 30 jack-thomas 150 4 john mac 10 john-mac 40

How should I define ColumnDT() of these two columns? How should I write my query in sqlalchemy which I can pass to DataTables() query parameter? (select user.id,user.first_name,user.last_name,user.value,user, user.first_name+"-"+user.last_name as 'full_name', user.ID+user.value as 'multiply' from user)

Thank you.

[Pegase745]

The easiest way for me would be declaring hybrid properties directly in your SQLAlchemy model.

# model.py
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.hybrid import hybrid_property

Base = declarative_base()

class MyModel(Base):
    __tablename__ = 'my_model'

    id = Column(Integer, primary_key=True)
    first_name = Column(String, nullable=False)
    last_name = Column(String, nullable=False)
    value = Column(Integer, nullable=False)

    @hybrid_property
    def full_name(self):
        return '%s-%s' % (self.first_name, self.last_name)

    @hybrid_property
    def multiply(self):
        return self.id * self.value

And this will let you use these properties directly in ColumnDT.

columns = [
    ...    
    ColumnDT(User.full_name),
    ... 
    ]

I hope this gives you the big idea

[saifraider]

Thank you for this example. Consider this 'Random' table which has id and random number as columns.

id---random_number 1----20 2----40 3----40 1----10 4----20 2----20 2----10

How can i write a sqlachemy query which is (select id, count(id), SUM(random_number) from Random group by id)? It should output

id----count----sum 1-----2-------30 2-----3------70 3-----1------40 4-----1------20

How can I declare columns for count, sum or any Aggregate functions in server side in columns = [ ...
ColumnDT(Random.id), ColumnDT(Random.count), <----- How should i define? ... ] Also can you provide sqlalchemy query which can be passed to the Datatables() query parameter?

Thank you.

[saifraider]

Used Literal column in columns definition to add 'count' column and 'sum' column and it worked. columns = [ ColumnDT(Timepass.user_name), ColumnDT(literal_column("'Count'")), ColumnDT(literal_column("'Sum'")) ] results = db.session.query('Timepass.user_name',func.count(Timepass.user_id),func.sum(Timepass.income)).group_by(Timepass.user_name)