Title: SQLAlchemy group_concat and duplicates
Tags: python;flask;sqlalchemy;flask-sqlalchemy
Question: When I try to join a many-to-many table and group it by  the main-id I am getting duplicates when I add the second many-to-many table.
Here is how my models look like:
Models
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'))
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 am using SQLAlchemy to select the desired 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 am getting duplicates of both the courses AND names, so if the course has two users it will print the students and the course two times.
Here is how it is looking:
wrong view
And here is how it should look:
correct view
the problem
the problem is coming when I am adding the second many-to-many table, for example users/student-identifier. If I remove the line where I &quot;join&quot; it, I am getting the duplicates. Is there anyway to correct this? Or should I use RAW-SQL instead(and if yes, how?)
Comment: Sidenote. I believe it's firstname instead of fistname

Here is the accepted answer: Found out the solution, 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)
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)
Simply add the distinct() to the desired column you want to be unique
Comment for this answer: How did you access those grouped fields in your template, simply .user_firstName and .course_name?
Comment for this answer: Never mind, I added a .label() in lieu of the original field name which didn't allow me to access the grouped field.


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 1, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2014:00:10/27/14', 'FavoriteCount': 2.0, 'PostTypeId': 1, 'Tags': '<python><flask><sqlalchemy><flask-sqlalchemy>', 'Title': 'SQLAlchemy group_concat and duplicates', 'ViewCount': 13078, 'id': 26583832, 'user_AboutMe': '<p>Software consultant at IT Minds</p>\n', 'user_AccountId': 1919416.0, 'user_DisplayName': 'Sigils', 'user_Reputation': 2382.0, 'user_Views': 168.0, 'user_id': 1731280.0}}