1

I have two tables as follows:

class Workflows():
    __tablename__ = 'workflows'
    id = db.Column(db.Integer(), primary_key=True)
    name = db.Column(db.Unicode(255),unique=True)

class Tasks():   
    __tablename__ = 'tasks'
    id = db.Column(db.Integer(), primary_key=True)
    workflow_id = db.Column(db.Integer(), db.ForeignKey('workflows.id', ondelete='CASCADE'))
    name = db.Column(db.Unicode(255), server_default=u'')
    modified_at = db.Column(db.DateTime(),default=datetime.datetime.utcnow)

I have written a simple query using sqlalchemy ORM which is follows:

(
    db.session.query(
        Workflows.id,
        (
            '[' +
            func.group_concat(
                '{"name": "', Tasks.name, '", "modified_date": "', Tasks.modified_at, '"}'
            )
            + ']'
        ).label('task_name')
    )
    .filter(Workflows.id == Tasks.workflow_id)
    .group_by(Workflows.id)
    .all()
)

When I execute above query I get the expected output which is a pair of Workflows.id and group_concat(dict). An example of output is:

[
    {'id': 30, 'task_name': '[{'name': 'Task 1', 'modified_date': '2019-03-07 12:59:51'}, {'name': 'Task 2', 'modified_date': '2019-03-07 12:59:51'}, {'name': 'Task 3', 'modified_date': '2019-03-07 12:59:51'}]'},
    {'id': 31, 'task_name': '[{'name': 'Task A', 'modified_date': '2019-03-07 13:01:58'}, {'name': 'Task B', 'modified_date': '2019-03-07 13:01:58'}, {'name': 'Task C', 'modified_date': '2019-03-07 13:01:58'}]'}
]

The problem occurs when I use the above query as a subquery. When I change the above query and use it is an alias in another query the task_name gets truncated. For example if I run following query:

workflow_data = (
    db.session.query(
        Workflows.id,
        (
            '[' +
        func.group_concat(
            '{"name": "', Tasks.name, '", "modified_date": "', Tasks.modified_at, '"}'
            )
            + ']'
        ).label('task_name')
    )
    .filter(Workflows.id == Tasks.workflow_id)
    .group_by(Workflows.id)
    .subquery()
)

sub_data = (
    db.session.query(
        workflow_data
    )
    .all()
)

Here when I print sub_data, I get following output:

[
    {'id': 30, 'task_name': '[{'name': 'Task 1', 'modified_date': '2019-03-07 12:59:51'}, {'name': 'Tas
    {'id': 31, 'task_name': '[{'name': 'Task A', 'modified_date': '2019-03-07 13:01:58'}, {'name': 'Tas
]

task_name in output is getting truncated over a particular length. I believe I need to increase GROUP_CONCAT length limit like this this post. But I can't figure out how to do it in sqlalchemy. If there is any other solution than that will also do.

Prashant
  • 63
  • 3
  • I can't simply execute raw SQL as there are much more queries that needs to be executed on the subquery object. – Prashant Mar 12 '19 at 13:57
  • 1
    *"I can't simply execute raw SQL as there are much more queries that needs to be executed on the subquery object."* Only way possible you need to do it before using the `GROUP_CONCAT()` or you need to configure it on the server and reboot it.. – Raymond Nijland Mar 12 '19 at 13:58
  • 1
    What @raymond is getting at (I think) is that you will have to execute the `SET` statement in your session *before* using it to issue your query that's using `group_concat()`. – Ilja Everilä Mar 12 '19 at 14:04

0 Answers0