Sqlalchemy many to one array response

Question

Im working with SQLAlchemy and Flask. I have a content table like:

--------------------------------------------
|    id   |    title     |   description   |
--------------------------------------------
|    1    |    example   |   my content    |
|    2    | another piece| my other content|
--------------------------------------------

And a status table like this:

--------------------------------------------------------
|   id   |   content_id  |     status type |    date   |
--------------------------------------------------------
|   1    |      1        |      written    |  1/5/2020 |
|   2    |      1        |      edited     |  1/7/2020 |
--------------------------------------------------------

I want to be able to query the db and get a content with all of the status's in one row instead of have multiple rows of the content repeated. For example I want:

----------------------------------------------------------
| id  |   title    |   description    |      status's    |
----------------------------------------------------------
|  1  |    example |   my content     |     [1,2]        |
----------------------------------------------------------

Is there a way to do this with sqlalchemy?

SQL approach for this is GROUP_CONCAT. You will find several examples for sqlalchemy such as https://stackoverflow.com/questions/58198288/sqlalchemy-func-group-concat-and-random-order-of-data — Andi Schroff, Jan 09 '20 at 06:44

Jaldhi Mehta · Accepted Answer · 2020-01-09T13:17:11.140

1

You can use this query for fetching your answer:

 SELECT b.*,
        (SELECT GROUP_CONCAT (id) FROM status_table
         WHERE content_id = b.id) AS `status's`
         FROM status_table a JOIN content_table b 
         ON a.content_id = b.id 
 GROUP BY a.content_id;

edited Jan 09 '20 at 13:17

answered Jan 09 '20 at 13:11

Jaldhi Mehta

711
6
10

Sqlalchemy many to one array response

1 Answers1