Get ids of many to many table

Question

in django if there's table with many to many field like that :

class Category(models.Model):
    name = models.CharField(unique=True, max_length=255)
    icon = models.ImageField(upload_to='images')
    sport = models.ManyToManyField(Sport) 

when you try to get all objects from this table you do this :

Category.objects.all()

and the result will be like that after serializing :

    "data": [
     {
       "id": 1,
       "name": '..'
       "icon": '...'
       "sport": [
          1,
          2
      ]
    ]
   }

so my question here how to achieve the same result using pure sql I tried this :

sql = '''select a.*,b.sport_id,array_agg(b.sport_id) as sport from category a join category_sport b on b.category_id=a.id group by a.id,b.sport_id ;'''

but the result was like that :

"data": [
  {
   "id": 1,
    "name": '..'
    "icon": '...'
    "sport": [
      1,
      ]
  },
  {
    "id": 1,
    "name": '..'
    "icon": '...'
    "sport": [
          2
      ]
    ]
  }

Answer 1

From my understanding, if you need to achieve that you just need to remove b.sport_id, you want to concatenate the sport_id into an array for each category, so no reason to group by this field, your SQL should be:

sql = '''select a.id, array_agg(b.sport_id) as sport from category a join category_sport b on b.category_id=a.id group by a.id;'''

Remember, all nonaggregate selected fields should appear in the group by section.

Another alternative, if you are using PostgreSQL, can be to use the ArrayAgg, something like:

from django.contrib.postgres.aggregates import ArrayAgg

Category.objects.aggregate(sport=ArrayAgg('sport__id'))

Also, you can see this very simple PostgreSQL 13 example:

Schema:

CREATE TABLE a (
  id INT,
  title VARCHAR(128)
);

CREATE TABLE b (
  id INT
);

CREATE TABLE ab (
  id INT,
  aid INT,
  bid INT
);

INSERT INTO a (id, title) VALUES (1, 'title_a');
INSERT INTO a (id, title) VALUES (2, 'title_b');

INSERT INTO b (id) VALUES (1);
INSERT INTO b (id) VALUES (2);
INSERT INTO b (id) VALUES (3);
INSERT INTO b (id) VALUES (4);
INSERT INTO b (id) VALUES (5);
INSERT INTO b (id) VALUES (6);

INSERT INTO ab (id, aid, bid) VALUES(1, 1, 1);
INSERT INTO ab (id, aid, bid) VALUES(2, 1, 3);
INSERT INTO ab (id, aid, bid) VALUES(3, 1, 5);
INSERT INTO ab (id, aid, bid) VALUES(4, 2, 2);
INSERT INTO ab (id, aid, bid) VALUES(5, 2, 4);
INSERT INTO ab (id, aid, bid) VALUES(6, 2, 6);

Query:

SELECT a.id, a.title, array_agg(ab.bid) AS bs FROM a JOIN ab ON a.id=ab.aid GROUP BY a.id, a.title;

Result:

| id  | title   | bs      |
| --- | ------- | ------- |
| 2   | title_b | [2,4,6] |
| 1   | title_a | [1,3,5] |

You can test it on https://www.db-fiddle.com/