(
select(
Advice.id,
Advice.dt,
Advice.with_recommendations,
AdvicePurposeVariety,
AdvicingAlgorithmVariety,
AdvicePurposeVariety,
AdviceReactionVariety,
EventVariety.name,
)
.distinct(Advice.id)
.where(and_(*filters_to_apply))
.outerjoin(AdvicePurposeVariety)
.outerjoin(AdviceReaction)
.outerjoin(AdviceReactionVariety)
.outerjoin(AdviceContext)
.outerjoin(AdvicingAlgorithmVariety)
.outerjoin(AdviceEvent)
.outerjoin(Event)
.outerjoin(EventVariety)
)
Такой вариант даёт ошибку
query = (
select(
func.count()
)
.distinct(Advice.id)
.where(and_(*filters_to_apply))
.outerjoin(AdvicePurposeVariety)
.outerjoin(AdviceReaction)
.outerjoin(AdviceReactionVariety)
.outerjoin(AdviceContext)
.outerjoin(AdvicingAlgorithmVariety)
.outerjoin(AdviceEvent)
.outerjoin(Event)
.outerjoin(EventVariety)
)
В первом запросе sql такой:
SELECT
DISTINCT ON (technological_section_1.advices.id) technological_section_1.advices.id,
....
Что бы получить кол-во надо, что бы было так
SELECT
count(DISTINCT technological_section_1.advices.id)
...
там нужно делать select_from
так? query = ( select(func.count()).select_from() .distinct(Advice.id) .where(and_(*filters_to_apply)) .outerjoin(AdvicePurposeVariety) .outerjoin(AdviceReaction) .outerjoin(AdviceReactionVariety) .outerjoin(AdviceContext) .outerjoin(AdvicingAlgorithmVariety) .outerjoin(AdviceEvent) .outerjoin(Event) .outerjoin(EventVariety) )
ну укажи из чего селектить
query = ( select(func.count()).select_from( select() .distinct(Advice.id) .where(and_(*filters_to_apply)) .outerjoin(AdvicePurposeVariety) .outerjoin(AdviceReaction) .outerjoin(AdviceReactionVariety) .outerjoin(AdviceContext) .outerjoin(AdvicingAlgorithmVariety) .outerjoin(AdviceEvent) .outerjoin(Event) .outerjoin(EventVariety) ) ) Вроде так, по идеи, но тоже ошибка
на SQL как пишется селект?
select_from(…) в скобках то что у тебя было в этом запросе: select( Advice.id, Advice.dt, Advice.with_recommendations, AdvicePurposeVariety, AdvicingAlgorithmVariety, AdvicePurposeVariety, AdviceReactionVariety, EventVariety.name, )
SELECT count(DISTINCT technological_section_1.advices.id) FROM technological_section_1.advices -- LEFT OUTER JOIN common.advice_purpose_varieties ON common.advice_purpose_varieties.id = technological_section_1.advices.purpose_variety_id LEFT OUTER JOIN technological_section_1.advice_reactions ON technological_section_1.advices.id = technological_section_1.advice_reactions.advice_id -- LEFT OUTER JOIN common.advice_reaction_varieties ON common.advice_reaction_varieties.id = technological_section_1.advice_reactions.variety_id LEFT OUTER JOIN technological_section_1.advice_contexts ON technological_section_1.advices.id = technological_section_1.advice_contexts.advice_id -- LEFT OUTER JOIN common.advising_algorithm_varieties ON common.advising_algorithm_varieties.id = technological_section_1.advice_contexts.advising_algorithm_variety_id LEFT OUTER JOIN technological_section_1.advice_events ON technological_section_1.advices.id = technological_section_1.advice_events.advice_id LEFT OUTER JOIN technological_section_1.events ON technological_section_1.events.id = technological_section_1.advice_events.event_id LEFT OUTER JOIN common.event_varieties ON common.event_varieties.id = technological_section_1.events.variety_id WHERE technological_section_1.advices.dt >= '2023-05-05 00:00:00' AND technological_section_1.advices.dt <= '2023-05-06 00:00:00' AND technological_section_1.advices.purpose_variety_id = 1 AND technological_section_1.advice_reactions.variety_id = 1 AND technological_section_1.advice_contexts.advising_algorithm_variety_id = 1 AND technological_section_1.advices.with_recommendations = true
вон from видишь =) его и передавай в select_from
мне не нужны все поля.
query = ( select(func.count()).select_from( select(distinct(Advice.id)) .where(and_(*filters_to_apply)) .outerjoin(AdvicePurposeVariety) .outerjoin(AdviceReaction) .outerjoin(AdviceReactionVariety) .outerjoin(AdviceContext) .outerjoin(AdvicingAlgorithmVariety) .outerjoin(AdviceEvent) .outerjoin(Event) .outerjoin(EventVariety) ) ) Вот как надо, т.е. в селект вставить distinct(Advice.id)
это работает несколько не так как исходный запрос
это создаёт вот такой запрос SELECT count(*) AS count_1 FROM ( SELECT DISTINCT technological_section_1.advices.id AS anon_2 FROM technological_section_1.advices LEFT OUTER JOIN common.advice_purpose_varieties ON common.advice_purpose_varieties.id = technological_section_1.advices.purpose_variety_id LEFT OUTER JOIN technological_section_1.advice_reactions ON technological_section_1.advices.id = technological_section_1.advice_reactions.advice_id LEFT OUTER JOIN common.advice_reaction_varieties ON common.advice_reaction_varieties.id = technological_section_1.advice_reactions.variety_id LEFT OUTER JOIN technological_section_1.advice_contexts ON technological_section_1.advices.id = technological_section_1.advice_contexts.advice_id LEFT OUTER JOIN common.advising_algorithm_varieties ON common.advising_algorithm_varieties.id = technological_section_1.advice_contexts.advising_algorithm_variety_id LEFT OUTER JOIN technological_section_1.advice_events ON technological_section_1.advices.id = technological_section_1.advice_events.advice_id LEFT OUTER JOIN technological_section_1.events ON technological_section_1.events.id = technological_section_1.advice_events.event_id LEFT OUTER JOIN common.event_varieties ON common.event_varieties.id = technological_section_1.events.variety_id ) AS anon_1
Обсуждают сегодня