SQLAlchemy group_concat和重复项 [英] SQLAlchemy group_concat and duplicates

问题描述

当我尝试加入一个多对多表并按 main-id 对其进行分组时，我得到重复项添加第二个多对多表.

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.

这是我的模型的样子:

用户

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'))
)

数据库结构

好吧，我正在使用SQLAlchemy来选择具有所需数据的所需表.通过这个session.query

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:

错误的视图

这是它的外观:

正确的视图

当我添加第二个多对多表(例如用户/学生标识符)时，问题就来了.如果我删除了我加入"的行，它，我正在重复.反正有什么要纠正的吗?还是应该改用RAW-SQL(如果可以，怎么办?)

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 "join" it, I am getting the duplicates. Is there anyway to correct this? Or should I use RAW-SQL instead(and if yes, how?)

推荐答案

找出解决方案，而且非常简单.

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)  
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)

只需将distinct()添加到想要唯一的所需列中即可

Simply add the distinct() to the desired column you want to be unique

这篇关于SQLAlchemy group_concat和重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！