526互联

django orm 软删除联表查询 需要显示软删除条件

发布时间 2023-12-12 11:59:51作者: vx_guanchaoguo0

比如课程学习

required_ok = UserLesson.objects.values('uuid').distinct().filter(
            user_id=self.request.user_id,
        ).filter(
            lesson__course__course_bx_org__org_id__in=org_ids,
        ).filter(
            Q(lesson__course__course_bx_org__user_type_id__in=list(user_type_list)) | Q(
                lesson__course__course_bx_org__user_type__isnull=True)
        )

转化为SQL

SELECT DISTINCT
	"edu_user_lesson"."uuid" 
FROM
	"edu_user_lesson"
	INNER JOIN "edu_course_lesson" ON ( "edu_user_lesson"."lesson_id" = "edu_course_lesson"."uuid" )
	INNER JOIN "edu_course" ON ( "edu_course_lesson"."course_id" = "edu_course"."uuid" )
	INNER JOIN "edu_course_bx" ON ( "edu_course"."uuid" = "edu_course_bx"."course_id" )
	LEFT OUTER JOIN "edu_course_bx" T7 ON ( "edu_course"."uuid" = T7."course_id" )
	INNER JOIN "edu_course_bx" T9 ON ( "edu_course"."uuid" = T9."course_id" ) 
WHERE
	"edu_user_lesson"."deleted_at" = 0 
	AND "edu_user_lesson"."user_id" = '26f1a6d68be24a1a896d6f1b3c8561b6' 
	AND "edu_course_bx"."org_id" IN ( '5873a495e7b448ada3714b1ea44dbe2d', 'bd5abe6cff8542b9b9f7df945ec342c7' ) 
	AND ( T7."user_type_id" IN ( 'aacbd77381f5485693cbf93f439ce373' ) OR T7."user_type_id" IS NULL ) 
	AND T9."deleted_at" = 0 
	AND "edu_user_lesson"."learn_cycle_date" BETWEEN '2023-01-01' 
	AND '2023-12-31'

统计不正确原因

  • course_bx_org_ 已经被软删除
  • 添加 SQL
"edu_course_bx"."deleted_at" = 0
  • 添加 ORM
filter(
            lesson__course__course_bx_org__deleted_at=0
        )