id summary reporter owner description type status component version severity resolution keywords cc stage has_patch needs_docs needs_tests needs_better_patch easy ui_ux 10723 ORM exclute strange anonymous nobody "My models is: {{{ #!python class Type(models.Model): id = models.AutoField(primary_key=True) slug = models.SlugField(unique=True, max_length=10) title = models.CharField(max_length=20) weight = models.SmallIntegerField(default=0, db_index=True) class Active(models.Model): post = models.ForeignKey(Post, related_name='activeposts') rubrica = models.ForeignKey(Rubrica, related_name='activeposts') type = models.ForeignKey(Type, related_name='activeposts') user_id = models.PositiveIntegerField() pub_date = models.DateTimeField(default=datetime.now) }}} {{{ #!python Type.objects.filter(Q(activeposts__rubrica__pk=112) & \ Q(activeposts__user_id__in=[1,2,3])). \ annotate(count=Count('activeposts')).query.as_sql() }}} Result is correct: {{{ #!sql ('SELECT `doska_type`.`id`, `doska_type`.`slug`, `doska_type`.`weight`, COUNT(`doska_active_new`.`id`) AS `count` FROM `doska_type` LEFT OUTER JOIN `doska_active_new` ON (`doska_type`.`id` = `doska_active_new`.`type_id`) WHERE (`doska_active_new`.`rubrica_id` = %s AND `doska_active_new`.`user_id` IN (%s, %s, %s)) GROUP BY `doska_type`.`id` ORDER BY `doska_type`.`weight` ASC', (112, 1, 2, 3)) }}} But {{{ #!python Type.objects.filter(Q(activeposts__rubrica__pk=112) & \ ~Q(activeposts__user_id__in=[1,2,3])). \ annotate(count=Count('activeposts')).query.as_sql() }}} Result is not correct: {{{ #!sql ('SELECT `doska_type`.`id`, `doska_type`.`slug`, `doska_type`.`weight`, COUNT(`doska_active_new`.`id`) AS `count` FROM `doska_type` LEFT OUTER JOIN `doska_active_new` ON (`doska_type`.`id` = `doska_active_new`.`type_id`) WHERE (`doska_active_new`.`rubrica_id` = %s AND NOT (`doska_type`.`id` IN ( SELECT U1.`type_id` FROM `doska_active_new` U1 WHERE U1.`user_id` IN (%s, %s, %s)))) GROUP BY `doska_type`.`id` ORDER BY `doska_type`.`weight` ASC', (112, 1, 2, 3)) }}} " closed Database layer (models, ORM) dev invalid Accepted 0 0 0 0 0 0