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())... ?

Aucun commentaire:

Enregistrer un commentaire