SQLAlchemy group_concat and duplicates

Question

When I try to join a many-to-many table and group it by the primary identifier, I get duplicates when I add a second many-to-many table.

Here's what my models look like:

Models

user

class User(UserMixin, db.Model): id = db.Column(db.Integer, primary_key=True) user_fistName = db.Column(db.String(64)) ... 

student_identifier

 student_identifier = db.Table('student_identifier', db.Column('class_id', db.Integer, db.ForeignKey('class.class_id')), db.Column('id', db.Integer, db.ForeignKey('user.id')) ) 

the class

 class Class(db.Model): sqlite_autoincrement=True class_id = db.Column(db.Integer, primary_key=True) class_name = db.Column(db.String(128), unique=True) mm_children = db.relationship('User', secondary=student_identifier, backref=db.backref('classes')) 

class_course_identifier

 class_course_identifier = db.Table('class_course_identifier', db.Column('course_id', db.Integer, db.ForeignKey('course.course_id')), db.Column('class_id', db.Integer, db.ForeignKey('class.class_id')) ) 

database structure

Well, I use SQLAlchemy to select the right tables with the data I want. with this session.query

 db.session.query( Class.class_id, Class.class_name, func.group_concat(User.user_fistName), func.group_concat(Course.course_name) ).filter(Class.courses, User.classes).group_by(Class.class_id) 

the problem is that I get duplicates of both AND AND names, so if the course has two users, it will print students and the course twice. Here's what it looks like:

wrong view

And this is how it should look:

right view

problem

The problem occurs when I add a second many-to-many table, for example user / student-identifier. If I delete the line where I "join" it, I get duplicates. Is there any way to fix this? Or should I use RAW-SQL instead (and if so, how?)

Answer 1

A solution has been found, and it is quite simple.

RAW SQL

 SELECT class.class_id, class.class_name, GROUP_CONCAT(DISTINCT course.course_name), GROUP_CONCAT(DISTINCT user.user_fistName) FROM class JOIN class_course_identifier ON class.class_id = class_course_identifier.class_id JOIN course ON class_course_identifier.course_id = course.course_id JOIN student_identifier ON class.class_id = student_identifier.class_id JOIN user ON student_identifier.id = user.id GROUP BY class.class_id 

SQLAlchemy

 db.session.query( Class.class_id, Class.class_name, func.group_concat(User.user_fistName.distinct()), func.group_concat(Course.course_name.distinct()) ).filter(Class.courses, User.classes).group_by(Class.class_id) 

Just add distinct() to the desired column that you want to be unique.