jeudi 23 juin 2016
How can I build a Django query without knowing what fields I will query on?
I'm new to Django and kinda new to Python too, and I'm trying to build a query. I have some fields in a dictionary and I don't know which will be used in a query (they're coming in from a form.)
I've got something kinda working, but I can tell it's broke, and it's terrible, not reusable code:
if 'street_number' in params:
query = query.filter(addresses__street_number=params['street_number'])
if 'street_name' in params:
query = query.filter(addresses__street_name=params['street_name'])
if 'district' in params:
query = query.filter(addresses__district=params['district'])
if 'city' in params:
query = query.filter(addresses__city=params['city'])
if 'county' in params:
query = query.filter(addresses__county=params['county'])
if 'state' in params:
query = query.filter(addresses__state=params['state'])
if 'country' in params:
query = query.filter(addresses__country=params['country'])
if 'zip_code' in params:
query = query.filter(addresses__zip_code=params['zip_code'])
if 'precinct' in params:
query = query.filter(addresses__precinct=params['precinct'])
I believe it's joining once for every time I call query.filter. When I view the SQL it generates I see this mess:
SELECT `voters`.`id`, `voters`.`created_at`, `voters`.`updated_at`, `voters`.`first_name`, `voters`.`middle_name`, `voters`.`last_name`, `voters`.`name_prefix`, `voters`.`name_suffix`, `voters`.`ethnicity`, `voters`.`gender`, `voters`.`i360_id`, `voters`.`rnc_id`, `voters`.`congressional_voter_id`, `voters`.`state_voter_id`, `voters`.`county_voter_id`
FROM `voters`
INNER JOIN `voters_addresses` ON (`voters`.`id` = `voters_addresses`.`voter_id`)
INNER JOIN `addresses` ON (`voters_addresses`.`address_id` = `addresses`.`id`)
INNER JOIN `voters_addresses` T4 ON (`voters`.`id` = T4.`voter_id`)
INNER JOIN `addresses` T5 ON (T4.`address_id` = T5.`id`)
WHERE (`addresses`.`city` = Nashville AND T5.`state` = TN) LIMIT 10
(there's also a couple other things going on in the SQL, like joining on phones, but that's not related to this problem.)
Note the extra joining on voters_addresses and addresses. Can I refactor this so it uses one filter() call instead? Alternatively, can I build a query starting with each of the related models (Address, Phone, etc.) and combine them later, somehow? Something like
Voter.objects
.whereExists(Address.objects.filter())
.whereExists(Phone.objects.filter())...
?
Inscription à :
Publier les commentaires (Atom)
Aucun commentaire:
Enregistrer un commentaire