I want use django ORM to finish count group by, but select sql unexpected limit 21 every time. I don't want the limit, why limit 21 appear, and how can I get all data?
model:
class Company(models.Model):
company_no = models.CharField(primary_key=True, max_length=128)
name = models.CharField(max_length=128)
is_test = models.BooleanField(default=False)
class Meta:
db_table = 'company'
class User(models.Model):
symbol = models.BigIntegerField(primary_key=True)
is_test = models.BooleanField(default=False)
class Meta:
db_table = 'user'
class UserEmploy(models.Model):
user = models.ForeignKey(User, on_delete=models.CASCADE, db_column='symbol', to_field='symbol', related_name='employ')
company = models.ForeignKey(Company, on_delete=models.CASCADE, to_field='company_no', db_column='company_no', related_name='user_employ')
class Meta:
db_table = 'user_employ'
django code in my views:
employ_qs_exclude_test = UserEmploy.objects\
.exclude(user__is_test__in=utils.IS_TEST_MODE)\
.values("company__name") \
.annotate(employ_count=Count('user', distinct=True))\
.order_by('company')
sql log:
SELECT `company`.`name`, COUNT(DISTINCT `user_employ`.`symbol`) AS `employ_count` FROM `user_employ`
INNER JOIN `user`
ON (`user_employ`.`symbol` = `user`.`symbol`)
INNER JOIN `company`
ON (`user_employ`.`company_no` = `company`.`company_no`)
WHERE NOT (`user`.`is_test` IN (1))
GROUP BY `company`.`name`, `company`.`created_at`
ORDER BY `company`.`created_at` DESC LIMIT 21;
As suggested by Iain in a comment above, a LIMIT 21 is automatically added when taking the repr() of a queryset, which also happens implicitely when printing a queryset.
To get the full output, you can force the queryset into a list before printing, e.g. instead of print(qs) you would write print(list(qs)) (which shows all data, but omits the queryset classname).
The relevant code is here and here:
REPR_OUTPUT_SIZE = 20
def __repr__(self):
data = list(self[:REPR_OUTPUT_SIZE + 1])
if len(data) > REPR_OUTPUT_SIZE:
data[-1] = "...(remaining elements truncated)..."
return '<%s %r>' % (self.__class__.__name__, data)
Note that the LIMIT 21 will also be present on queries generated by calling .get() on a queryset. This is intended as a safeguard against the databasse returning a ton of data that will be thrown away anyway. The limit could have been 2, but using 21 means that the error can tell you how many records were actually found (provided it is less than 21). This does give some performance penalty, but only in the multiple records case, which should be an exceptional situation. When there is just one record there is no overhead.
See discussion of this here and code here:
MAX_GET_RESULTS = 21