时间戳

首页 > 解决方案 > Django 原始查询输出与 pgAdmin 输出不一致

python - Django 原始查询输出与 pgAdmin 输出不一致

问题描述

我正在使用带有 postgres 10 db 引擎的 django 通过表单构建基于 Web 的搜索工具。在 django 中通过原始 SQL 执行查询时，在 pgAdmin 的 Ouery 工具中运行相同的 SQL 时，我只收到 3 条记录，而不是 902 条记录。

我已经尝试使用 django ORM 来构建我的查询，但是这些示例非常简单，我无法成功地利用它来满足我的需求。以下列方式查询多个模型的指导将不胜感激。

Django 原始 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%')
;

我希望输出是相同数量和内容的记录，因为它们查询的是相同的源。

标签： pythondjangopostgresql

解决方案

塞尔丘克的评论让我得到了正确的结果。需要更改提供 Where 子句的变量以反映Conditions.name LIKE '%prostate%'在查询中，因此：

ProstateUpper = '%Prostate%'
ProstateLower = '%prostate%'
GenderMale = '%Male%'
GenderAll = '%All%'

推荐阅读