Coverage for rest_api/viewsets.py: 29%

1from datetime import datetime 

2from datetime import timezone 

3from functools import reduce 

4import hashlib 

5import io 

6import json 

7import operator 

8import os 

9import pickle 

10import uuid 

11import zipfile 

12 

13from django.core.cache import cache 

14from django.core.files.uploadedfile import InMemoryUploadedFile 

15from django.db.models import CharField 

16from django.db.models import F 

17from django.db.models import Q 

18from django.db.models.functions import Cast 

19from django.db.utils import IntegrityError 

20from django.http import FileResponse 

21from django_filters.rest_framework import DjangoFilterBackend 

22from rest_framework import generics 

23from rest_framework import status 

24from rest_framework import viewsets 

25from rest_framework.decorators import action 

26from rest_framework.request import Request 

27from rest_framework.response import Response 

28 

29from rest_api.data_entry.gbk_import import import_gbk_file 

30from rest_api.data_entry.property_job import delete_property 

31from rest_api.data_entry.property_job import find_or_create_property 

32from rest_api.data_entry.reference_job import delete_reference 

33from rest_api.data_entry.sample_entry_job import check_for_new_data 

34from rest_api.management.commands.import_lineage import LineageImport 

35from rest_api.utils import generate_job_ID 

36from rest_api.utils import get_distinct_gene_symbols 

37from rest_api.utils import parse_default_data 

38from rest_api.utils import PropertyColumnMapping 

39from rest_api.utils import strtobool 

40from sonar_backend.settings import CACHE_OBJECT_TTL 

41from sonar_backend.settings import LOGGER 

42from sonar_backend.settings import SONAR_DATA_ENTRY_FOLDER 

43from . import models 

44from .serializers import AlignmentSerializer 

45from .serializers import GeneSerializer 

46from .serializers import ImportLogSerializer 

47from .serializers import LineagesSerializer 

48from .serializers import ProcessingJobSerializer 

49from .serializers import PropertySerializer 

50from .serializers import ReferenceSerializer 

51from .serializers import RepliconSerializer 

52 

53 

54class Echo: 

55 """An object that implements just the write method of the file-like 

56 interface. 

57 """ 

58 

59 def write(self, value): 

60 """Write the value by returning it, instead of storing in a buffer.""" 

61 return value 

62 

63 

64class AlignmentViewSet( 

65 viewsets.GenericViewSet, 

66 generics.mixins.ListModelMixin, 

67 generics.mixins.RetrieveModelMixin, 

68): 

69 """ 

70 AlignmentViewSet 

71 """ 

72 

73 queryset = models.Alignment.objects.all() 

74 serializer_class = AlignmentSerializer 

75 

76 @action( 

77 detail=False, 

78 methods=["get"], 

79 url_path="get_alignment_data/(?P<seqhash>[a-zA-Z0-9]+)/(?P<replicon_id>[0-9]+)", 

80 ) 

81 def get_alignment_data(self, request: Request, seqhash=None, replicon_id=None): 

82 queryset = self.queryset.filter( 

83 sequence__seqhash=seqhash, 

84 replicon_id=replicon_id, 

85 ).select_related("sequence") 

86 

87 sequence_data = list( 

88 queryset.annotate( 

89 alignment_id=F("id"), 

90 sequence_name=F("sequence__name"), 

91 ).values( 

92 "replicon_id", 

93 "alignment_id", 

94 "sequence_id", 

95 "sequence_name", 

96 ) 

97 ) 

98 

99 # Falls nur ein Datensatz zurückkommt, als einzelnes Dict liefern 

100 if sequence_data: 

101 return Response(sequence_data[0], status=status.HTTP_200_OK) 

102 else: 

103 return Response({}, status=status.HTTP_404_NOT_FOUND) 

104 

105 @action(detail=False, methods=["post"]) 

106 def get_bulk_alignment_data(self, request: Request, *args, **kwargs): 

107 data = json.loads(request.body.decode("utf-8")) 

108 sequence_data_list = data.get("sample_data", []) 

109 

110 # Extract values from the list 

111 seqhash_values = [item.get("seqhash") for item in sequence_data_list] 

112 replicon_id_values = [item.get("replicon_id") for item in sequence_data_list] 

113 

114 queryset = models.Alignment.objects.filter( 

115 reduce( 

116 operator.or_, 

117 ( 

118 Q(sequence__seqhash=seqhash, replicon_id=replicon_id) 

119 for seqhash, replicon_id in zip(seqhash_values, replicon_id_values) 

120 ), 

121 ) 

122 ) 

123 queryset = queryset.select_related("sequence") 

124 

125 # Convert the queryset to a list of dictionaries 

126 # sequence_data = list(queryset.values()) 

127 sequence_data = list( 

128 queryset.annotate( 

129 alignment_id=F("id"), 

130 sequence_name=F("sequence__name"), 

131 ).values( 

132 "replicon_id", 

133 "alignment_id", 

134 "sequence_id", 

135 "sequence_name", 

136 ) 

137 ) 

138 

139 return Response(data=sequence_data, status=status.HTTP_200_OK) 

140 

141 

142class RepliconViewSet(viewsets.ModelViewSet): 

143 queryset = models.Replicon.objects.all() 

144 serializer_class = RepliconSerializer 

145 

146 @action(detail=False, methods=["get"]) 

147 def distinct_accessions(self, request: Request, *args, **kwargs): 

148 queryset = models.Replicon.objects.only("accession").values_list( 

149 "accession", flat=True 

150 ) 

151 if ref := request.query_params.get("reference"): 

152 queryset = queryset.filter(reference__accession=ref) 

153 distinct_accessions = queryset.distinct() 

154 

155 return Response( 

156 {"accessions": distinct_accessions}, 

157 status=status.HTTP_200_OK, 

158 ) 

159 

160 @action(detail=False, methods=["get"]) 

161 def get_molecule_data(self, request: Request, *args, **kwargs): 

162 sample_data = {} 

163 

164 if replicon_id := request.query_params.get("replicon_id"): 

165 queryset_obj = self.queryset.filter(id=replicon_id) 

166 elif ref := request.query_params.get("reference"): 

167 queryset_obj = self.queryset.filter(reference__accession=ref) 

168 else: 

169 return Response( 

170 {"detail": "Accession ID is missing"}, 

171 status=status.HTTP_400_BAD_REQUEST, 

172 ) 

173 

174 if queryset_obj.exists(): 

175 # NOTE: Fixed value for translation ID 

176 # sample_data.translation_id = 1 

177 # sample_data = serialize('json', queryset_obj) 

178 sample_data = queryset_obj.values() 

179 for obj in sample_data: 

180 obj["translation_id"] = 1 

181 return Response(data=sample_data, status=status.HTTP_200_OK) 

182 

183 

184class GeneViewSet(viewsets.ModelViewSet): 

185 queryset = models.Gene.objects.all() 

186 serializer_class = GeneSerializer 

187 

188 @action(detail=False, methods=["get"]) 

189 def distinct_gene_symbols(self, request: Request, *args, **kwargs): 

190 reference = request.query_params.get("reference") 

191 gene_symbols = get_distinct_gene_symbols(reference=reference) 

192 return Response( 

193 {"gene_symbols": gene_symbols}, 

194 status=status.HTTP_200_OK, 

195 ) 

196 

197 @action(detail=False, methods=["get"]) 

198 def get_gene_data(self, request: Request): 

199 sample_data = {} 

200 

201 if ref_acc := request.query_params.get("ref_acc"): 

202 # queryset = models.Gene.objects.filter(replicon__reference__accession=ref_acc) 

203 queryset = models.GeneSegment.objects.select_related( 

204 "gene__replicon__reference" 

205 ).filter(gene__replicon__reference__accession=ref_acc) 

206 

207 elif replicon_id := request.query_params.get("replicon_id"): 

208 queryset = self.queryset.filter(replicon_id=replicon_id) 

209 elif replicon_acc := request.query_params.get("replicon_acc"): 

210 queryset = models.GeneSegment.objects.select_related( 

211 "gene__replicon" 

212 ).filter(gene__replicon__accession=replicon_acc) 

213 else: 

214 return Response( 

215 {"detail": "Searchable field is missing"}, 

216 status=status.HTTP_400_BAD_REQUEST, 

217 ) 

218 

219 sample_data = [] 

220 # TODO : check for mulitple cds's per gene 

221 # raise NotImplementedError("Multiple CDS per gene not implemented") 

222 for item in queryset.all(): 

223 _data = {} 

224 _data["reference.id"] = item.gene.replicon.reference.id 

225 _data["reference.accession"] = item.gene.replicon.reference.accession 

226 _data["reference.description"] = item.gene.replicon.reference.description 

227 _data["reference.organism"] = item.gene.replicon.reference.organism 

228 _data["reference.host"] = item.gene.replicon.reference.host 

229 _data["replicon.id"] = item.gene.replicon.id 

230 _data["replicon.accession"] = item.gene.replicon.accession 

231 _data["replicon.description"] = item.gene.replicon.description 

232 _data["replicon.length"] = item.gene.replicon.length 

233 _data["replicon.segment_number"] = item.gene.replicon.segment_number 

234 _data["gene.id"] = item.gene.id 

235 _data["gene.start"] = item.gene.start 

236 _data["gene.end"] = item.gene.end 

237 _data["gene.description"] = item.gene.description 

238 _data["gene.gene_symbol"] = item.gene.symbol 

239 _data["gene.gene_accession"] = item.gene.accession 

240 _data["gene.gene_sequence"] = item.gene.sequence 

241 _data["gene_segment.id"] = item.id 

242 _data["gene_segment.gene_id"] = item.gene_id 

243 _data["gene_segment.start"] = item.start 

244 _data["gene_segment.end"] = item.end 

245 _data["gene_segment.forward_strand"] = item.forward_strand 

246 _data["gene_segment.order"] = item.order 

247 

248 # Add CDS information 

249 cds_list = [] 

250 for cds in item.gene.cds_set.all(): 

251 cds_data = { 

252 "cds.id": cds.id, 

253 "cds.accession": cds.accession, 

254 "cds.sequence": cds.sequence, 

255 "cds.description": cds.description, 

256 } 

257 cds_segments = [] 

258 for segment in cds.cds_segments.all(): 

259 segment_data = { 

260 "cds_segment.id": segment.id, 

261 "cds_segment.order": segment.order, 

262 "cds_segment.start": segment.start, 

263 "cds_segment.end": segment.end, 

264 "cds_segment.forward_strand": segment.forward_strand, 

265 } 

266 cds_segments.append(segment_data) 

267 cds_data["cds_segments"] = cds_segments 

268 

269 # Add Peptide information 

270 peptide_list = [] 

271 for peptide in cds.peptides.all(): 

272 peptide_data = { 

273 "peptide.id": peptide.id, 

274 "peptide.description": peptide.description, 

275 "peptide.type": peptide.type, 

276 } 

277 peptide_segments = [] 

278 for segment in peptide.peptide_segments.all().order_by("order"): 

279 segment_data = { 

280 "peptide_segment.id": segment.id, 

281 "peptide_segment.order": segment.order, 

282 "peptide_segment.start": segment.start, 

283 "peptide_segment.end": segment.end, 

284 } 

285 peptide_segments.append(segment_data) 

286 peptide_data["peptide_segments"] = peptide_segments 

287 peptide_list.append(peptide_data) 

288 cds_data["peptide_list"] = peptide_list 

289 

290 cds_list.append(cds_data) 

291 _data["cds_list"] = cds_list 

292 

293 sample_data.append(_data) 

294 # sample_data =queryset.values() 

295 return Response(data=sample_data, status=status.HTTP_200_OK) 

296 

297 

298class ReferenceViewSet( 

299 viewsets.GenericViewSet, 

300 generics.mixins.ListModelMixin, 

301 generics.mixins.RetrieveModelMixin, 

302): 

303 queryset = models.Reference.objects.all() 

304 serializer_class = ReferenceSerializer 

305 

306 @action(detail=False, methods=["post"]) 

307 def import_gbk(self, request: Request, *args, **kwargs): 

308 if not request.FILES or "gbk_file" not in request.FILES: 

309 return Response( 

310 {"detail": "No file uploaded."}, status=status.HTTP_400_BAD_REQUEST 

311 ) 

312 if "translation_id" not in request.data: 

313 return Response( 

314 {"detail": "No translation_id provided."}, 

315 status=status.HTTP_400_BAD_REQUEST, 

316 ) 

317 translation_id = int(request.data.get("translation_id")) 

318 enable_segment = strtobool(request.data.get("segment")) 

319 # if enable_segment: 

320 # gbk_files = request.FILES.getlist("gbk_file") 

321 # import_gbk_files(gbk_files, translation_id) 

322 # else: 

323 gbk_file = request.FILES.getlist("gbk_file") 

324 import_gbk_file(gbk_file, translation_id) 

325 

326 return Response( 

327 {"detail": "File uploaded successfully"}, status=status.HTTP_201_CREATED 

328 ) 

329 

330 @action(detail=False, methods=["get"]) 

331 def dataset_options(self, request, *args, **kwargs): 

332 """ 

333 For each organism in the Reference table, returns the corresponding 

334 accession values found int the Reference table and 

335 dataset values found in the Sample table. 

336 """ 

337 queryset = models.Sample.objects.values( 

338 accession=F("sequences__alignments__replicon__reference__accession"), 

339 organism=F("sequences__alignments__replicon__reference__organism"), 

340 data_set_value=F("data_set"), 

341 ).distinct() 

342 

343 result = {} 

344 

345 for reference in queryset: 

346 organism = reference["organism"] 

347 accession = reference["accession"] 

348 data_set_value = reference["data_set_value"] 

349 

350 # Skip entries where organism is None 

351 if organism is None: 

352 continue 

353 

354 if organism not in result: 

355 result[organism] = {"accessions": set(), "data_sets": set()} 

356 if accession is not None: 

357 result[organism]["accessions"].add(accession) 

358 if data_set_value is not None: 

359 if data_set_value == "": 

360 # NOTE: if "-Empty-" string is changed here, it must be changed as well in samples.ts !! 

361 result[organism]["data_sets"].add("-Empty-") 

362 else: 

363 result[organism]["data_sets"].add(data_set_value) 

364 

365 # sort data_set values 

366 for organism in result: 

367 result[organism]["data_sets"] = sorted(result[organism]["data_sets"]) 

368 

369 return Response( 

370 result, 

371 status=status.HTTP_200_OK, 

372 ) 

373 

374 @action(detail=False, methods=["post"]) 

375 def delete_reference(self, request: Request, *args, **kwargs): 

376 if "accession" not in request.data: 

377 return Response( 

378 {"detail": "No accession provided."}, status=status.HTTP_400_BAD_REQUEST 

379 ) 

380 

381 accession = request.data.get("accession") 

382 

383 data = delete_reference(accession) 

384 return Response(data, status=status.HTTP_200_OK) 

385 

386 @action(detail=False, methods=["get"]) 

387 def distinct_accessions(self, request: Request, *args, **kwargs): 

388 accession_list = models.Reference.objects.values_list( 

389 "accession", flat=True 

390 ).distinct() 

391 return Response(data=accession_list, status=status.HTTP_200_OK) 

392 

393 @action(detail=False, methods=["get"]) 

394 def get_all_references(self, request: Request, *args, **kwargs): 

395 queryset = models.Reference.objects.all() 

396 reference_data = queryset.values() 

397 return Response(data=reference_data, status=status.HTTP_200_OK) 

398 

399 @action(detail=False, methods=["get"]) 

400 def get_reference_file(self, request: Request, *args, **kwargs): 

401 reference = request.query_params.get("reference") 

402 # get Reference and extract name (path) 

403 queryset = models.Reference.objects.filter(accession=reference) 

404 if queryset.exists(): 

405 reference_obj = queryset.first() 

406 reference_files = reference_obj.name.split( 

407 ", " 

408 ) # Split the stored file paths 

409 

410 # Fetch associated replicon accessions 

411 replicons = models.Replicon.objects.filter(reference=reference_obj) 

412 replicon_accessions = list(replicons.values_list("accession", flat=True)) 

413 if len(reference_files) == 1: 

414 # Only one file, send it directly 

415 reference_file = reference_files[0] 

416 response = FileResponse( 

417 open(reference_file, "rb"), 

418 as_attachment=True, 

419 filename=os.path.basename(reference_file), 

420 ) 

421 

422 # Add replicon_accessions to the response headers 

423 response["Replicon-Accessions"] = ",".join(replicon_accessions) 

424 return response 

425 else: 

426 # Multiple files -> Zip them 

427 zip_buffer = io.BytesIO() 

428 

429 with zipfile.ZipFile(zip_buffer, "w", zipfile.ZIP_DEFLATED) as zipf: 

430 for ref_file in reference_files: 

431 with open(ref_file, "rb") as f: 

432 zipf.writestr(os.path.basename(ref_file), f.read()) 

433 

434 zip_buffer.seek(0) 

435 

436 response = FileResponse( 

437 zip_buffer, 

438 as_attachment=True, 

439 filename=f"{reference}.segment.zip", 

440 content_type="application/zip", 

441 ) 

442 # Add replicon_accessions to the response headers 

443 response["Replicon-Accessions"] = ",".join(replicon_accessions) 

444 return response 

445 else: 

446 return Response( 

447 {"detail": "No reference found"}, status=status.HTTP_404_NOT_FOUND 

448 ) 

449 

450 # multilple get in one. 

451 

452 

453class PropertyViewSet( 

454 viewsets.GenericViewSet, 

455 generics.mixins.ListModelMixin, 

456 generics.mixins.RetrieveModelMixin, 

457): 

458 queryset = models.Sample2Property.objects.all() 

459 serializer_class = PropertySerializer 

460 filter_backends = [DjangoFilterBackend] 

461 filterset_fields = ["sample__id", "property__name"] 

462 

463 @action(detail=False, methods=["get"]) 

464 def distinct_properties(self, request: Request, *args, **kwargs): 

465 if not (property_name := request.query_params.get("property_name")): 

466 return Response( 

467 "No property_name provided.", status=status.HTTP_400_BAD_REQUEST 

468 ) 

469 sample_property_fields = [ 

470 field.name for field in models.Sample._meta.get_fields() 

471 ] 

472 if property_name in sample_property_fields: 

473 queryset = models.Sample.objects.all() 

474 if ref := request.query_params.get("reference"): 

475 queryset = queryset.filter( 

476 sequences__alignments__replicon__reference__accession=ref 

477 ) 

478 queryset = queryset.distinct(property_name) 

479 return Response( 

480 {"values": [getattr(item, property_name) for item in queryset]}, 

481 status=status.HTTP_200_OK, 

482 ) 

483 else: 

484 queryset = models.Sample2Property.objects.filter( 

485 property__name=property_name 

486 ) 

487 if ref := request.query_params.get("reference"): 

488 queryset = queryset.filter( 

489 sample__sequences__alignments__replicon__reference__accession=ref 

490 ) 

491 datatype = queryset[0].property.datatype 

492 queryset = queryset.distinct(datatype) 

493 return Response( 

494 {"values": [getattr(item, datatype) for item in queryset]}, 

495 status=status.HTTP_200_OK, 

496 ) 

497 

498 @action(detail=False, methods=["get"]) 

499 def distinct_property_names(self, request: Request, *args, **kwargs): 

500 property_names = self.get_distinct_property_names() 

501 return Response( 

502 data={"property_names": property_names}, status=status.HTTP_200_OK 

503 ) 

504 

505 @action(detail=False, methods=["post"]) 

506 def add_property(self, request: Request, *args, **kwargs): 

507 name = request.data.get("name") 

508 datatype = request.data.get("datatype", None) 

509 querytype = request.data.get("querytype", None) 

510 description = request.data.get("description", None) 

511 default = parse_default_data(request.data.get("default", None)) 

512 obj, created = find_or_create_property( 

513 name=name, 

514 datatype=datatype, 

515 querytype=querytype, 

516 description=description, 

517 default=default, 

518 ) 

519 

520 if created: 

521 return Response( 

522 {"detail": "Property added successfully"}, 

523 status=status.HTTP_201_CREATED, 

524 ) 

525 elif obj: 

526 return Response( 

527 {"detail": "Property already exists"}, status=status.HTTP_200_OK 

528 ) 

529 else: 

530 return Response( 

531 {"detail": "Failed to add property"}, 

532 status=status.HTTP_500_INTERNAL_SERVER_ERROR, 

533 ) 

534 

535 @action(detail=False, methods=["post"]) 

536 def delete_property(self, request: Request, *args, **kwargs): 

537 name = request.data.get("name") 

538 deleted = delete_property(name) 

539 

540 if deleted is not False: 

541 if deleted == 0: 

542 return Response( 

543 {"detail": "No matching property found for deletion"}, 

544 status=status.HTTP_200_OK, 

545 ) 

546 return Response( 

547 {"detail": "Property deleted successfully"}, 

548 status=status.HTTP_200_OK, 

549 ) 

550 else: 

551 return Response( 

552 {"detail": "Error occurred, please inform the admin."}, 

553 status=status.HTTP_500_INTERNAL_SERVER_ERROR, 

554 ) 

555 

556 @action(detail=False, methods=["get"]) 

557 def get_all_properties(self, request: Request, *args, **kwargs): 

558 """ 

559 get all properties with their unique value counts. 

560 

561 Returns: 

562 Response with format: 

563 { 

564 "keys": ["name", "query_type", "description", "default", "unique_values_count"], 

565 "values": [ 

566 {"name": "lineage", "query_type": "value_varchar", "unique_values_count": 850}, 

567 ... 

568 ] 

569 } 

570 """ 

571 ref = request.query_params.get("reference") 

572 

573 # STEP 1: Check cache first (instant return if cached) 

574 cache_key = ( 

575 f"all_properties:{hashlib.md5(ref.encode() if ref else b'').hexdigest()}" 

576 ) 

577 cached_result = cache.get(cache_key) 

578 if cached_result is not None: 

579 return Response(data=cached_result, status=status.HTTP_200_OK) 

580 

581 # STEP 2: Define property categories 

582 # Properties stored directly in Sample table 

583 default_properties = [ 

584 "name", 

585 "collection_date", 

586 "lineage", 

587 "zip_code", 

588 "lab", 

589 "sequencing_tech", 

590 "host", 

591 "genome_completeness", 

592 "country", 

593 "init_upload_date", 

594 "last_update_date", 

595 ] 

596 

597 # Properties stored in Sequence table 

598 sequence_properties = ["length"] 

599 

600 # STEP 3: Build initial property metadata list 

601 data_list = [ 

602 { 

603 "name": "name", 

604 "query_type": "value_varchar", 

605 "description": "sample name or ID (fixed prop.)", 

606 "default": None, 

607 }, 

608 { 

609 "name": "collection_date", 

610 "query_type": "value_date", 

611 "description": "Date when the sample was collected (predefined prop.)", 

612 "default": None, 

613 }, 

614 { 

615 "name": "lab", 

616 "query_type": "value_varchar", 

617 "description": "Name of the laboratory where the sample was analyzed (predefined prop.)", 

618 "default": None, 

619 }, 

620 { 

621 "name": "zip_code", 

622 "query_type": "value_varchar", 

623 "description": "ZIP code of the location where the sample was collected (predefined prop.)", 

624 "default": None, 

625 }, 

626 { 

627 "name": "host", 

628 "query_type": "value_varchar", 

629 "description": "Host organism from which the sample was taken (e.g., Human) (predefined prop.)", 

630 "default": None, 

631 }, 

632 { 

633 "name": "genome_completeness", 

634 "query_type": "value_varchar", 

635 "description": "Completeness of the genome (e.g., partial or complete) (predefined prop.)", 

636 "default": None, 

637 }, 

638 { 

639 "name": "lineage", 

640 "query_type": "value_varchar", 

641 "description": "Lineage (predefined prop.)", 

642 "default": None, 

643 }, 

644 { 

645 "name": "sequencing_tech", 

646 "query_type": "value_varchar", 

647 "description": "Technology used for sequencing the genome (predefined prop.)", 

648 "default": None, 

649 }, 

650 { 

651 "name": "country", 

652 "query_type": "value_varchar", 

653 "description": "Country where the sample was collected (predefined prop.)", 

654 "default": None, 

655 }, 

656 { 

657 "name": "init_upload_date", 

658 "query_type": "value_date", 

659 "description": "Date when the sample data was initially uploaded to the database (fixed prop.)", 

660 "default": "current date and time", 

661 }, 

662 { 

663 "name": "last_update_date", 

664 "query_type": "value_date", 

665 "description": "Date when the sample data was last updated in the database (fixed prop.)", 

666 "default": "current date and time", 

667 }, 

668 { 

669 "name": "data_set", 

670 "query_type": "value_varchar", 

671 "description": "Name of the data set", 

672 "default": None, 

673 }, 

674 { 

675 "name": "length", 

676 "query_type": "value_integer", 

677 "description": "Sequence length in base pairs", 

678 "default": None, 

679 }, 

680 ] 

681 

682 for _property_queryset in models.Property.objects.order_by("name"): 

683 data_list.append( 

684 { 

685 "name": _property_queryset.name, 

686 "query_type": _property_queryset.datatype, 

687 "description": _property_queryset.description, 

688 "default": _property_queryset.default, 

689 } 

690 ) 

691 # STEP 6: Count unique values for each property 

692 # For each property, get count of unique values 

693 # Problems: 

694 # 1. repeats JOIN chain for each property (data_list ) 

695 # sequences__alignments__replicon__reference__accession 4 JOINs * n properties 

696 # - if using IN clause instead of JOIN chains (~700-1000ms vs ~300-500ms) 

697 # (no improvment, Django JOIN do the optimization and magic ~300ms) 

698 for property_data in data_list: 

699 property_name = property_data["name"] 

700 # CASE 1: Default properties from Sample table 

701 if property_name in default_properties: 

702 try: 

703 # get count of unique values from Sample table 

704 count = ( 

705 models.Sample.objects.annotate( 

706 raw_value=Cast(property_name, output_field=CharField()) 

707 ) 

708 .values_list("raw_value", flat=True) 

709 .exclude(raw_value__isnull=True) 

710 .exclude(raw_value__exact="") 

711 .distinct() 

712 .count() 

713 ) 

714 except Exception as e: 

715 LOGGER.error(f"Error counting {property_name}: {e}") 

716 count = 0 

717 # CASE 2: Sequence properties from Sequence table 

718 elif property_name in sequence_properties: 

719 # Special handling for properties stored in Sequence table 

720 try: 

721 count = ( 

722 models.Sequence.objects.filter( 

723 alignments__replicon__reference__accession=ref 

724 ) 

725 .annotate( 

726 raw_value=Cast(property_name, output_field=CharField()) 

727 ) 

728 .values_list("raw_value", flat=True) 

729 .exclude(raw_value__isnull=True) 

730 .exclude(raw_value__exact="") 

731 .distinct() 

732 .count() 

733 ) 

734 except Exception as e: 

735 LOGGER.error(f"Error counting {property_name}: {e}") 

736 count = 0 

737 # CASE 3: Custom properties from Sample2Property table 

738 else: 

739 query_type = property_data["query_type"] 

740 # get count of unique values from Property table 

741 try: 

742 count = ( 

743 models.Sample2Property.objects.filter( 

744 property__name=property_name 

745 ) 

746 .filter( 

747 sample__sequences__alignments__replicon__reference__accession=ref 

748 ) 

749 .annotate(raw_value=Cast(query_type, output_field=CharField())) 

750 .values_list("raw_value", flat=True) 

751 .exclude(raw_value__isnull=True) 

752 .exclude(raw_value__exact="") 

753 .distinct() 

754 .count() 

755 ) 

756 except Exception as e: 

757 LOGGER.error(f"Error counting {property_name}: {e}") 

758 count = 0 

759 

760 property_data["unique_values_count"] = count 

761 

762 cols = [ 

763 "name", 

764 "query_type", 

765 "description", 

766 "default", 

767 "unique_values_count", 

768 ] 

769 data = {"keys": cols, "values": data_list} 

770 

771 # Cache for xx minutes 

772 cache.set(cache_key, data, CACHE_OBJECT_TTL) 

773 

774 return Response(data=data, status=status.HTTP_200_OK) 

775 

776 @staticmethod 

777 def get_distinct_property_names(): 

778 queryset = models.Property.objects.all() 

779 queryset = queryset.distinct("name") 

780 filter_list = ["id", "datahash", "properties"] 

781 property_names = [item.name for item in queryset] 

782 sample_properties = [ 

783 field.name 

784 for field in models.Sample._meta.get_fields() 

785 if field.name not in filter_list 

786 ] 

787 property_names += sample_properties 

788 return property_names 

789 

790 @staticmethod 

791 def get_custom_property_names(): 

792 queryset = models.Property.objects.all() 

793 queryset = queryset.distinct("name") 

794 property_names = [item.name for item in queryset]