'How to select single entity from multiple entity with maximum value of a field in django?
models.py
def return_timestamped_id():
last_unique_id = RMLocation.objects.all().order_by('id').last()
if not last_unique_id:
return 'RM0001'
else:
timestamped_id = last_unique_id.unique_id
timestamped_id = int(timestamped_id.split('RM')[-1])
width = 4
new_unique_int = timestamped_id + 1
formatted = (width - len(str(new_unique_int))) * "0" + str(new_unique_int)
new_unique_id = 'RM' + str(formatted)
return new_unique_id
class RMLocation(models.Model):
warehouse = models.ForeignKey(RMWarehouse, on_delete=models.CASCADE, related_name='l_warehouse')
cabinet = models.CharField(max_length=255, blank=True, null=True)
rack = models.CharField(max_length=255, blank=True, null=True)
shelf = models.CharField(max_length=255, blank=True, null=True)
file_name = models.CharField(max_length=255, blank=True, null=True)
file_no = models.CharField(max_length=255, blank=True, null=True)
unique_id = models.CharField(default=return_timestamped_id, max_length=255, blank=True, null=True) # Auto generated.
uuid = models.CharField(max_length=40)
record_extension = models.ForeignKey(DocumentExtension, on_delete=models.CASCADE, related_name='rec_ext', blank=True, null=True)
company = models.ForeignKey(Company, on_delete=models.CASCADE, related_name='record_company')
is_ocr = models.BooleanField(default=False)
ocr_text_file = models.CharField(max_length=255, blank=True, null=True)
is_trashed = models.BooleanField(default=False)
trashed_on = models.DateTimeField(blank=True, null=True)
created_at = models.DateTimeField(default=timezone.now)
created_by = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE, blank=True, null=True, related_name='l_created_by')
updated_at = models.DateTimeField(blank=True, null=True)
updated_by = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE, blank=True, null=True, related_name='l_updated_by')
deleted_at = models.DateTimeField(blank=True, null=True)
deleted_by = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE, blank=True, null=True, related_name='l_deleted_by')
def __str__(self):
return str(self.id)
class Meta:
db_table = 'record_location'
class RMRecordVersion(models.Model):
VER_TYPE_CHOICE = (
('major', 'major'),
('minor', 'minor')
)
record = models.ForeignKey(RMLocation, on_delete=models.CASCADE, related_name='rv_rec')
scanned_file_location = models.TextField(blank=True, null=True)
size_in_kb = models.FloatField(default=0.0)
version_no = models.IntegerField(default=1)
version_code = models.CharField(default='1.0', max_length=10)
version_type = models.CharField(choices=VER_TYPE_CHOICE, max_length=10, default='major')
created_at = models.DateTimeField(auto_now_add=True, null=True)
created_by = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE, related_name='rv_rm_user', null=True, blank=True)
def __str__(self):
return str(self.id)
class Meta:
db_table = 'record_versions'
views.py
In the Older Response below you can see there are items with the same id. For example, For id = 69 we got two items with version_no 1 and 2 respectively. But I want to have only one item per id with maximum version_no. So, for id = 69 we gonna get one item whose version_no is 2.
I tried .aggregate(Max('rv_rec__version_no')). Currently trying Subquery. Which is giving this:
This queryset contains a reference to an outer query and may only be used in a subquery.
class RMLocationListView(APIView):
permission_classes = [IsAuthenticated]
authentication_classes = [TokenAuthentication]
queryset = RMLocation.objects.values()
def get_filter(self, instance):
filter = {}
unique_id = self.request.query_params.get('unique_id', None)
warehouse = self.request.query_params.get('warehouse_id', None)
cabinet = self.request.query_params.get('cabinet', None)
rack = self.request.query_params.get('rack', None)
shelf = self.request.query_params.get('shelf', None)
from django.db.models import Max
from django.db.models import Subquery, OuterRef
filter['is_trashed'] = False
filter['company'] = instance.user.company
if unique_id:
filter['unique_id'] = unique_id
if warehouse:
filter['warehouse'] = warehouse
if warehouse and cabinet:
filter['cabinet'] = cabinet
if warehouse and cabinet and rack:
filter['rack'] = rack
if warehouse and cabinet and rack and shelf:
filter['shelf'] = shelf
queryset = self.queryset.filter(**filter, rv_rec__version_no__gte = Subquery(RMRecordVersion.objects.filter(record = OuterRef('id')).values('version_no').last().get("version_no"))).order_by('-id')
queryset=queryset.annotate(
warehouse_id = F('warehouse'),
warehouse_name = F('warehouse__name'),
warehouse_code = F('warehouse__code'),
record_name = F('file_name'),
company_name = F('company__name'),
uploaded_at = F('created_at'),
uploaded_by_id = F('created_by'),
uploaded_by = Concat('created_by__first_name', Value(' '), 'created_by__last_name'),
size_in_kb = F('rv_rec__size_in_kb'),
version_no = F('rv_rec__version_no'),
version_code = F('rv_rec__version_code'),
version_type = F('rv_rec__version_type'),
)
return queryset.values('id','warehouse_id','warehouse_name','warehouse_code','cabinet','rack','shelf','record_name',
'file_no','unique_id','company_name','size_in_kb','version_no','version_code',
'version_type','uploaded_at','uploaded_by_id','uploaded_by')
@response_modify_decorator_list_or_get_after_execution_for_onoff_pagination
def get(self, request, *args, **kwargs):
count = self.request.query_params.get('count')
# Pagination Functionality
paginator = OnOffPagination()
page_size = self.request.GET['page_size']
# Filter
self.queryset = self.get_filter(request)
field_name = self.request.query_params.get('ordering')
if field_name:
self.queryset = base_f.get_sorted_by_fields(self, self.queryset)
if page_size == '0':
response = self.queryset
if count:
count = len(response)
return Response({'total_count': count})
else:
result_page = paginator.paginate_queryset(self.queryset, request)
response = paginator.get_paginated_response(result_page)
# Appending preview_path in response.data of those records whose soft copies are available.
for index, record in enumerate(response.data['results']):
record_obj = RMLocation.objects.filter(id=record['id']).last()
if not record_obj.record_extension == None:
record_response = record_service.get_record_response(record_obj, request.user, show_preview_path=True)
response.data['results'][index]['soft_copy_permission'] = record_response['permission']
response.data['results'][index]['extension_name'] = record_response['record_extension']['name']
response.data['results'][index]['preview_path'] = record_response['preview_path']
response = response.data
return Response(response)
Older Response
{
"count": 49,
"next": "http://127.0.0.1:8001/rm/location/list/?page=2&page_size=10",
"previous": null,
"results": [
{
"id": 69,
"cabinet": "C1",
"rack": "R1",
"shelf": "S1",
"file_no": "FNo1236sdfsdff",
"unique_id": "RM0026",
"warehouse_id": 1,
"warehouse_name": "Godrej Store",
"warehouse_code": "D667",
"record_name": "Record Namejjjsdff",
"company_name": "Demo Company",
"uploaded_at": "2022-04-08T06:47:21.063450Z",
"uploaded_by_id": 33,
"uploaded_by": "Person Name",
"size_in_kb": 1.0,
"version_no": 2,
"version_code": "3.0",
"version_type": "major",
"soft_copy_permission": "admin",
"extension_name": "png",
"preview_path": "http://127.0.0.1:8001/rm/_storage/preview/1cc1372e-313e-4cbe-8463-09ef85fc36f6|6f63cf58fe90-3648-ebc4-e313-e2731cc1|NA.png/"
},
{
"id": 69,
"cabinet": "C1",
"rack": "R1",
"shelf": "S1",
"file_no": "FNo1236sdfsdff",
"unique_id": "RM0026",
"warehouse_id": 1,
"warehouse_name": "Godrej Store",
"warehouse_code": "D667",
"record_name": "Record Namejjjsdff",
"company_name": "Demo Company",
"uploaded_at": "2022-04-08T06:47:21.063450Z",
"uploaded_by_id": 33,
"uploaded_by": "Person Name",
"size_in_kb": 1.0,
"version_no": 1,
"version_code": "2.0",
"version_type": "major",
"soft_copy_permission": "admin",
"extension_name": "png",
"preview_path": "http://127.0.0.1:8001/rm/_storage/preview/1cc1372e-313e-4cbe-8463-09ef85fc36f6|6f63cf58fe90-3648-ebc4-e313-e2731cc1|NA.png/"
},
{
"id": 68,
"cabinet": "C1",
"rack": "R1",
"shelf": "S1",
"file_no": "FNo1236sdfsdf",
"unique_id": "RM0025",
"warehouse_id": 1,
"warehouse_name": "Godrej Store",
"warehouse_code": "D667",
"record_name": "Record Namejjjsdf",
"company_name": "Demo Company",
"uploaded_at": "2022-04-08T06:30:05.690434Z",
"uploaded_by_id": 33,
"uploaded_by": "Person Name",
"size_in_kb": 1.0,
"version_no": 2,
"version_code": "2.0",
"version_type": "major",
"soft_copy_permission": "admin",
"extension_name": "png",
"preview_path": "http://127.0.0.1:8001/rm/_storage/preview/22dc1968-0e07-4db5-b146-e136b76eac32|23cae67b631e-641b-5bd4-70e0-8691cd22|NA.png/"
},
{
"id": 68,
"cabinet": "C1",
"rack": "R1",
"shelf": "S1",
"file_no": "FNo1236sdfsdf",
"unique_id": "RM0025",
"warehouse_id": 1,
"warehouse_name": "Godrej Store",
"warehouse_code": "D667",
"record_name": "Record Namejjjsdf",
"company_name": "Demo Company",
"uploaded_at": "2022-04-08T06:30:05.690434Z",
"uploaded_by_id": 33,
"uploaded_by": "Person Name",
"size_in_kb": 1.0,
"version_no": 1,
"version_code": "1.0.1",
"version_type": "minor",
"soft_copy_permission": "admin",
"extension_name": "png",
"preview_path": "http://127.0.0.1:8001/rm/_storage/preview/22dc1968-0e07-4db5-b146-e136b76eac32|23cae67b631e-641b-5bd4-70e0-8691cd22|NA.png/"
},
{
"id": 67,
"cabinet": "C1",
"rack": "R1",
"shelf": "S1",
"file_no": "FNo1236633r",
"unique_id": "RM0024",
"warehouse_id": 1,
"warehouse_name": "Godrej Store",
"warehouse_code": "D667",
"record_name": "Record Name6633r",
"company_name": "Demo Company",
"uploaded_at": "2022-04-07T09:02:07.704273Z",
"uploaded_by_id": 33,
"uploaded_by": "Person Name",
"size_in_kb": 98.0,
"version_no": 22,
"version_code": "20.1",
"version_type": "minor",
"soft_copy_permission": "admin",
"extension_name": "pdf",
"preview_path": "http://127.0.0.1:8001/rm/_storage/preview/91e666ad-9108-4860-a0bb-891bdcace5f0|0f5ecacdb198-bb0a-0684-8019-da666e19|NA.pdf/"
},
{
"id": 67,
"cabinet": "C1",
"rack": "R1",
"shelf": "S1",
"file_no": "FNo1236633r",
"unique_id": "RM0024",
"warehouse_id": 1,
"warehouse_name": "Godrej Store",
"warehouse_code": "D667",
"record_name": "Record Name6633r",
"company_name": "Demo Company",
"uploaded_at": "2022-04-07T09:02:07.704273Z",
"uploaded_by_id": 33,
"uploaded_by": "Person Name",
"size_in_kb": 98.0,
"version_no": 21,
"version_code": "20.0",
"version_type": "major",
"soft_copy_permission": "admin",
"extension_name": "pdf",
"preview_path": "http://127.0.0.1:8001/rm/_storage/preview/91e666ad-9108-4860-a0bb-891bdcace5f0|0f5ecacdb198-bb0a-0684-8019-da666e19|NA.pdf/"
},
{
"id": 67,
"cabinet": "C1",
"rack": "R1",
"shelf": "S1",
"file_no": "FNo1236633r",
"unique_id": "RM0024",
"warehouse_id": 1,
"warehouse_name": "Godrej Store",
"warehouse_code": "D667",
"record_name": "Record Name6633r",
"company_name": "Demo Company",
"uploaded_at": "2022-04-07T09:02:07.704273Z",
"uploaded_by_id": 33,
"uploaded_by": "Person Name",
"size_in_kb": 98.0,
"version_no": 20,
"version_code": "19.0",
"version_type": "major",
"soft_copy_permission": "admin",
"extension_name": "pdf",
"preview_path": "http://127.0.0.1:8001/rm/_storage/preview/91e666ad-9108-4860-a0bb-891bdcace5f0|0f5ecacdb198-bb0a-0684-8019-da666e19|NA.pdf/"
},
{
"id": 67,
"cabinet": "C1",
"rack": "R1",
"shelf": "S1",
"file_no": "FNo1236633r",
"unique_id": "RM0024",
"warehouse_id": 1,
"warehouse_name": "Godrej Store",
"warehouse_code": "D667",
"record_name": "Record Name6633r",
"company_name": "Demo Company",
"uploaded_at": "2022-04-07T09:02:07.704273Z",
"uploaded_by_id": 33,
"uploaded_by": "Person Name",
"size_in_kb": 98.0,
"version_no": 19,
"version_code": "18.0",
"version_type": "major",
"soft_copy_permission": "admin",
"extension_name": "pdf",
"preview_path": "http://127.0.0.1:8001/rm/_storage/preview/91e666ad-9108-4860-a0bb-891bdcace5f0|0f5ecacdb198-bb0a-0684-8019-da666e19|NA.pdf/"
},
{
"id": 67,
"cabinet": "C1",
"rack": "R1",
"shelf": "S1",
"file_no": "FNo1236633r",
"unique_id": "RM0024",
"warehouse_id": 1,
"warehouse_name": "Godrej Store",
"warehouse_code": "D667",
"record_name": "Record Name6633r",
"company_name": "Demo Company",
"uploaded_at": "2022-04-07T09:02:07.704273Z",
"uploaded_by_id": 33,
"uploaded_by": "Person Name",
"size_in_kb": 98.0,
"version_no": 18,
"version_code": "17.0",
"version_type": "major",
"soft_copy_permission": "admin",
"extension_name": "pdf",
"preview_path": "http://127.0.0.1:8001/rm/_storage/preview/91e666ad-9108-4860-a0bb-891bdcace5f0|0f5ecacdb198-bb0a-0684-8019-da666e19|NA.pdf/"
},
{
"id": 67,
"cabinet": "C1",
"rack": "R1",
"shelf": "S1",
"file_no": "FNo1236633r",
"unique_id": "RM0024",
"warehouse_id": 1,
"warehouse_name": "Godrej Store",
"warehouse_code": "D667",
"record_name": "Record Name6633r",
"company_name": "Demo Company",
"uploaded_at": "2022-04-07T09:02:07.704273Z",
"uploaded_by_id": 33,
"uploaded_by": "Person Name",
"size_in_kb": 98.0,
"version_no": 17,
"version_code": "16.0",
"version_type": "major",
"soft_copy_permission": "admin",
"extension_name": "pdf",
"preview_path": "http://127.0.0.1:8001/rm/_storage/preview/91e666ad-9108-4860-a0bb-891bdcace5f0|0f5ecacdb198-bb0a-0684-8019-da666e19|NA.pdf/"
}
],
"status_code": 200,
"request_status": 1,
"msg": "Data has been fetched successfully"
}
Some of the functions used in views.py
def get_record_response(record: RMLocation, user: UserDetail, show_preview_path=False, version: int = None):
read_permission = get_record_permission_for_user(record, user, permission_level='read')
data = None
if read_permission:
record_extension_name = record.record_extension.name if record.record_extension and record.record_extension.name else ''
data = {
'id': record.id,
'type': 'record',
'warehouse_name': record.warehouse.name,
'warehouse_code': record.warehouse.code,
'warehouse_address': record.warehouse.address,
'cabinet': record.cabinet,
'rack': record.rack,
'shelf': record.shelf,
'record_name': record.file_name,
'file_no': record.file_no,
'unique_id': record.unique_id,
'uuid': record.uuid,
'company_id': record.company_id,
'company': record.company.name,
'record_extension': {
'id': record.record_extension_id,
'name': record_extension_name,
},
'is_trashed': record.is_trashed,
'create_modified_details': get_record_create_and_modified_details(record),
}
if not record.is_trashed:
data['permission'] = read_permission[0].soft_copy_permission if user else 'read'
version_history, last_version = RMRecordVersion.objects.filter(record=record), None
if version:
last_version = version_history.filter(version_no=version).first()
if not last_version:
last_version = version_history.order_by('-version_no').first()
version = None
data['version_no'] = last_version.version_no
data['version_code'] = last_version.version_code
data['version_type'] = last_version.version_type
data['size_in_kb'] = last_version.size_in_kb
if show_preview_path:
data['preview_path'], _ = create_single_record_download_link(record.uuid, user,
request=None, version_no=version,
action='preview')
else:
data['trashed_on'] = record.trashed_on
return data
def get_sorted_by_fields(self, response):
field_name = self.request.query_params.get('ordering')
if 'results' in response:
if '-' in field_name:
field_name = field_name[1:]
response['results'] = sorted(response['results'], key=lambda i: i[field_name]if i[field_name] else '', reverse=True)
else:
response['results'] = sorted(response['results'], key=lambda i: i[field_name] if i[field_name] else '')
else:
if '-' in field_name:
field_name = field_name[1:]
response = sorted(response, key=lambda i: i[field_name]if i[field_name] else '', reverse=True)
else:
response = sorted(response, key=lambda i: i[field_name] if i[field_name] else '')
return response
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|