我正在使用django和postgres 10 db引擎通过表单构建基于Web的搜索工具。当通过django中的原始SQL执行查询时,我在pgAdmin的Ouery工具中运行相同的SQL时只收到3条记录而不是902条。
我已经尝试使用django ORM来构建我的查询但是示例非常简单,我无法成功地将其用于我的需求。
Django Raw SQL语句
ProstateUpper = 'Prostate'
ProstateLower = 'prostate'
GenderMale = 'Male'
GenderAll = 'All'
Master_Query = Eligibilities.objects.raw('''SELECT DISTINCT ON (Eligibilities.nct_id) Eligibilities.id, Studies.brief_title, Eligibilities.nct_id, Conditions.name, Eligibilities.gender, Eligibilities.minimum_age, Eligibilities.maximum_age, Eligibilities.criteria, Interventions.intervention_type, Interventions.name, Facilities.city, Facilities.state, Facilities.country, Brief_Summaries.description, Facility_Contacts.name, Facility_Contacts.email FROM Eligibilities INNER JOIN Studies on Studies.nct_id = Eligibilities.nct_id INNER JOIN Conditions on Conditions.nct_id = Eligibilities.nct_id INNER JOIN Interventions on Interventions.nct_id = Eligibilities.nct_id INNER JOIN Facilities on Facilities.nct_id = Eligibilities.nct_id INNER JOIN Brief_Summaries on Brief_Summaries.nct_id = Eligibilities.nct_id INNER JOIN Facility_Contacts on Facility_Contacts.nct_id = Eligibilities.nct_id WHERE ((Conditions.name LIKE %s OR Conditions.name LIKE %s)) AND (gender LIKE %s OR gender LIKE %s) ''', [ProstateUpper, ProstateLower, GenderMale, GenderAll])[:1000]
pgAdmin SQL语句
SELECT DISTINCT ON (eligibilities.nct_id) eligibilities.id, studies.brief_title, eligibilities.nct_id, conditions.name, eligibilities.gender, eligibilities.minimum_age, eligibilities.maximum_age, eligibilities.criteria, interventions.intervention_type, interventions.name, facilities.city, facilities.state, facilities.country, brief_summaries.description, facility_contacts.name, facility_contacts.email
FROM ctgov.eligibilities
INNER JOIN ctgov.studies on studies.nct_id = eligibilities.nct_id
INNER JOIN ctgov.conditions on conditions.nct_id = eligibilities.nct_id
INNER JOIN ctgov.interventions on interventions.nct_id = eligibilities.nct_id
INNER JOIN ctgov.facilities on facilities.nct_id = eligibilities.nct_id
INNER JOIN ctgov.brief_summaries on brief_summaries.nct_id = eligibilities.nct_id
INNER JOIN ctgov.facility_contacts on facility_contacts.nct_id = eligibilities.nct_id
WHERE ((conditions.name LIKE '%Prostate%' OR conditions.name LIKE '%prostate%')) AND (gender LIKE '%Male%' OR gender LIKE '%All%')
;
我希望输出与记录的数量和内容相同,因为它们查询相同的源。
需要更改提供Where子句的变量以反映Conditions.name LIKE '%prostate%'在查询中,因此:
ProstateUpper = '%Prostate%'
ProstateLower = '%prostate%'
GenderMale = '%Male%'
GenderAll = '%All%'
版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。