sqlalchemy-datatables
sqlalchemy-datatables copied to clipboard
How to search column with func.group_concat?
I have a cloumn such as:
ColumnDT(func.group_concat(distinct(Role.name)), global_search=False, mData='roles'),
And search in DataTables not working for this column.
I found while I using having
everything is okey.
SELECT * FROM users
LEFT OUTER JOIN users_roles ON users.id = users_roles.user_id
LEFT OUTER JOIN roles ON roles.id = users_roles.role_id
having group_concat(DISTINCT roles.name) LIKE lower('%admin%')
But the real sql is using where
.
Is there a way to solve it?
No, that is not supported directly, search input is translated to a where clause. Probably you can work around this by doing a subquery first and then filtering (using where) in the outer query