Как использовать union в SQLAlchemy для объединения двух и более таблиц с неограниченным количеством общих связей?

Question

[Иван Тищенко]

Нужно объединить две разных модели у которых есть общий ключ с третьей моделью.

Описание моделей

class Income_Fact(db.Model):
    __tablename__ = 'Income_Fact'
    id = db.Column(
        UUID,
        default=text('uuid_generate_v4()'),
        server_default=text('uuid_generate_v4()'),
        primary_key=True
        )
    codes = db.Column(
        UUID,
        db.ForeignKey('Code.id'),
        nullable=True
        )
    date = db.Column(
        db.Date,
        nullable=False
        )
    value = db.Column(
        db.Float,
        nullable=False
        )

class Income_Plan(db.Model):
    __tablename__ = 'Income_Plan'
    id = db.Column(
        UUID,
        default=text('uuid_generate_v4()'),
        server_default=text('uuid_generate_v4()'),
        primary_key=True
        )
    codes = db.Column(
        UUID,
        db.ForeignKey('Code.id'),
        nullable=True
        )
    date = db.Column(
        db.Date,
        nullable=False
        )
    value = db.Column(
        db.Float,
        nullable=False
        )

class Code(db.Model):
    __tablename__ = 'Code'
    id = db.Column(UUID, default=text('uuid_generate_v4()'), server_default=text('uuid_generate_v4()'), primary_key=True)
    parents = db.Column(
        'parent_id',
        UUID,
        db.ForeignKey(id),
        default=None
        )
    code = db.Column(
        db.String,
        nullable=False
        )
    name = db.Column(
        db.String,
        nullable=False
        )
    popup = db.Column(
        db.String,
        nullable=True
        )
    income_plan_values = db.relationship(
        'Income_Plan',
        order_by='Income_Plan.date',
        backref='Code'
        )
    income_fact_values = db.relationship(
        'Income_Fact',
        order_by='Income_Fact.date',
        backref='Code'
        )

Если вызывать запрос вот так:

q1 = query(Income_Plan.value.label('plan'), literal_column("0").label('fact'), Code.id, Code.name).select_from(Income_Plan).outerjoin(Code))
q2 = query(literal_column("0").label('plan'), Income_Plan.value.label('fact'), Code.id, Code.name).select_from(Income_Fact).outerjoin(Code))
sq = q1.union_all(q2).subquery()
query(func.sum(sq.c.plan), func.sum(sq.c.fact), Income_Code.name, Income_Code.id).select_entity_from(sq).group_by(Income_Code.id, Income_Code.name)

Код на SQL генерируется вот такой

SELECT 
  sum(anon_1.plan) AS sum_1, 
  sum(anon_1.fact) AS sum_2, 
  anon_1."Code_name" AS "anon_1_Code_name",
  anon_1."Code_id" AS "anon_1_Code_id"
FROM (
  SELECT 
    anon_2.plan AS plan, anon_2.fact AS fact, 
    anon_2."Code_id" AS "Code_id",
    anon_2."Code_name" AS "Code_name"
  FROM (
        SELECT
          "Income_Plan".value AS plan,
          0 AS fact, "Code".id AS "Income_Code_id",
          "Code".name AS "Code_name"
        FROM  "Income_Plan"
          LEFT OUTER JOIN "Code" ON "Code".id = "Income_Plan".codes
        UNION ALL
        SELECT
          0 AS plan,
          "Income_Fact".value AS fact,
          "Code".id AS "Code_id",
          "Code".name AS "Code_name"
        FROM "Income_Fact"
          LEFT OUTER JOIN "Code" ON "Code".id = "Income_Fact".codes
       ) AS anon_2
     ) AS anon_1 
GROUP BY anon_1."Code_id", anon_1."Code_name"

Что нужно поменять чтобы на выходе получить хотя бы приблизительно следующее:

SELECT *
FROM (
    SELECT SUM(Income_Plan.value) as 'plan', 0 as 'plan', Code.name
        FROM Income_Plan LEFT JOIN Code ON Code.id = Income_Plan.codes
        GROUP BY Code.id;
    UNION ALL;
    SELECT SUM(Income_Facr.value) as 'plan', 0 as 'fact', Code.name
        FROM Income_Plan LEFT JOIN Code ON Code.id = Income_Plan.codes
        GROUP BY Code.id;
)

Как это сделать с помощью SQLAlchemy?

Comments

[DDDsa]

Покажите, что вы пытались сделать и что не получается, что не понятно. Пока что это не вопрос, а "сделайте все за меня".

[Иван Тищенко]

Даниил: Поправил вопрос. Буду благодарен если подскажете что исправить, или альтернативный способ которым это можно решить.

[DDDsa]

Tihon_V: аргумент literal_column должен быть строкой:
literal_column("0")

[Иван Тищенко]

Даниил: А ёще как оказалось нужно указывать поля в одинаковом порядке и указывать из какой модели нужно делать запрос
q2 = (literal_column("0").label("plan"), ...).select_from(...)
Спасибо большое

[Иван Тищенко]

Даниил: А можно ли добавить вызвать в запросе колонку другой модель (не связанную) и заполнить её нулями?
query(
Income_Plan.value,
Income_Fact.value, # присвоить 0
Code.name
).join(Code)

[DDDsa]

Tihon_V: В смысле "колонку другой модели и заполнить нулями"? В смысле, в одном из запросов вместо колонки другой модели вывести нули? Тогда почему бы просто не использовать literal_column или я чего-то не понимаю?

Вот так разве не работает?

q1 = session.query(func.sum(Income_Plan.value).label('plan'),
                   Code.name,
                   literal_column('0').label('fact')).group_by(Code.id)
q2 = session.query(literal_column(0).label('plan'),
                   Code.name,
                   func.sum(Income_Fact.value).label('fact')).group_by(Code.id)
q1.union_all(q2)

[Иван Тищенко]

Даниил: Хочется сделать так:
q = session.query(func.sum(literal_column("plan")), func.sum(literal_column("fact")), Code.name).select_entity_from(union_all(q1.subquery(), q2.subquery())).group_by(Code.id)
Но SQLAlchemy возвращает:

SELECT 
  sum(%(param_1)s) AS sum_1, -- вот тут ошибка
  sum(%(param_2)s) AS sum_2, 
  anon_1.name AS in_code 
FROM (
  SELECT "Income_Plan".value AS plan, 
  0 AS fact, 
  "Code".id AS id, 
  "Code".name AS name 
    FROM "Income_Plan" 
      LEFT OUTER JOIN "Code" ON "Code".id = "Income_Plan".Codes AS anon_2 
  UNION ALL 
  SELECT 0 AS plan, 
    "Income_Fact".value AS fact, 
    "Code".id AS id, 
    "Code".name AS name 
  FROM "Income_Fact" 
    LEFT OUTER JOIN "Code" ON "Code".id = "Income_Fact".Codes AS anon_3
  ) AS anon_1 GROUP BY anon_1.id

[DDDsa]

Tihon_V: Попробуйте вот так:

union_sq = union_all(q1, q2).subquery()

q = session.query(union_sq.c.plan, union_sq.c.fact, Code.name).select_entity_from(union_sq).group_by(Code.id)

Только мне все же кажется, можно все сделать куда проще. Если покажете итоговый запрос на SQL, могу покрутить

[Иван Тищенко]

Даниил:
sq = union_all(q1, q2).subquery()
AttributeError: 'CompoundSelect' object has no attribute 'subquery'

[DDDsa]

Tihon_V:
sq =q1.union_all(q2).subquery()
а так?

[Иван Тищенко]

Даниил:

SELECT 
  anon_1.plan AS anon_1_plan, 
  anon_1.fact AS anon_1_fact, 
  anon_1."Income_Code_name" AS "anon_1_Income_Code_name" 
FROM (
  SELECT 
    anon_2.plan AS plan, 
    anon_2.fact AS fact, 
    anon_2."Income_Code_id" AS "Income_Code_id", 
    anon_2."Income_Code_name" AS "Income_Code_name"
  FROM (
    SELECT 
      "Income_Plan".value AS plan, 
      0 AS fact, 
      "Code".id AS "Income_Code_id", 
      "Code".name AS "Income_Code_name" 
    FROM "Income_Plan" 
      LEFT OUTER JOIN "Code" ON "Code".id = "Income_Plan".codes 
    UNION ALL 
    SELECT 
      0 AS plan, 
      "Income_Fact".value AS fact, 
      "Code".id AS "Income_Code_id", 
      "Code".name AS "Income_Code_name" 
    FROM "Income_Fact" 
      LEFT OUTER JOIN "Code" ON "Code".id = "Income_Fact".codes
       ) AS anon_2
     ) AS anon_1 
GROUP BY anon_1."Income_Code_id"

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) ОШИБКА: столбец "anon_1.plan" должен фигурировать в предложении GROUP BY или использоваться в агрегатной функции
LINE 1: SELECT anon_1.plan AS anon_1_plan, anon_1.fact AS anon_1_fac...

Что-то очень страшно выходит если нужно ещё делать группировку для сумм значений...

[DDDsa]

Tihon_V: Да нет, это я пропустил func.sum( перед union_sq. В общем, сам черт ногу сломит - я и говорю, должно быть гораздо проще решение

union_sq = q1.union_all(q2).subquery()
q = session.query(func.sum(union_sq.c.plan),
                  func.sum(union_sq.c.fact),
                  Code.name).select_entity_from(union_sq).group_by(Code.id)

[Иван Тищенко]

Даниил: А я не пропустил =\
Вот так отдает результат.

sq = q1.union_all(q2).subquery()
db.session.query(
    func.sum(sq.c.plan), func.sum(sq.c.fact), Code.name, Code.id
).select_entity_from(sq).group_by(Code.id, Code.name)

Но хотелось бы ещё найти варианты попроще.
Потому как это - плохо выглядит:

SELECT 
  sum(anon_1.plan) AS sum_1, 
  sum(anon_1.fact) AS sum_2, 
  anon_1."Code_name" AS "anon_1_Code_name", 
  anon_1."Code_id" AS "anon_1_Code_id" 
FROM (
  SELECT 
    anon_2.plan AS plan, anon_2.fact AS fact, 
    anon_2."Code_id" AS "Code_id", 
    anon_2."Code_name" AS "Code_name"
  FROM (
        SELECT
          "Income_Plan".value AS plan,
          0 AS fact, "Code".id AS "Income_Code_id",
          "Code".name AS "Code_name"
        FROM  "Income_Plan"
          LEFT OUTER JOIN "Code" ON "Code".id = "Income_Plan".codes
        UNION ALL
        SELECT
          0 AS plan,
          "Income_Fact".value AS fact,
          "Code".id AS "Code_id",
          "Code".name AS "Code_name"
        FROM "Income_Fact"
          LEFT OUTER JOIN "Code" ON "Code".id = "Income_Fact".codes
       ) AS anon_2
     ) AS anon_1 
GROUP BY anon_1."Code_id", anon_1."Code_name"

[DDDsa]

Tihon_V: вот этот запрос выводит то, что вы хотите?

SELECT Code.name,
       SUM(Income_Plan.value) AS 'plan',
       SUM(Income_Fact.value) AS 'fact'
FROM Code
LEFT JOIN Income_Plan
ON Income_Plan.codes = Code.id
LEFT JOIN Income_Fact
ON Income_Fact.codes = Code.id
GROUP BY Code.id

[Иван Тищенко]

Даниил: Практически. Нужно отфильтровать поля которые не имеют значений (NOTNULL)
Т.е. на sqlalchemy нужно это делать так:

db.session.query(
    Code.name, func.sum(Income_Plan.value), func.sum(Income_Fact.value)
).select_from(Code).join(Income_Plan).join(Income_Fact).order_by(Code.id)

Верно?

[Иван Тищенко]

Даниил: Тогда генерируется

SELECT
  "Code".name AS "Code_name",
  sum("Income_Plan".value) AS sum_1,
  sum("Income_Fact".value) AS sum_2
FROM "Code"
  JOIN "Income_Plan" ON "Code".id = "Income_Plan".codes
  JOIN "Income_Fact" ON "Code".id = "Income_Fact".codes
ORDER BY "Code".id;

ОШИБКА: столбец "Code.name" должен фигурировать в предложении GROUP BY или использоваться в агрегатной функции
Позиция: 10

[DDDsa]

Tihon_V: Нет, это я хрень какую-то написал. По идее, вот правильный:

SELECT Code.name,
       Plan.value AS 'plan',
       Fact.value AS 'fact'
FROM Code
LEFT JOIN (SELECT codes,
                  SUM(value) AS 'value'
           FROM Income_Plan
           GROUP BY codes) plan
ON plan.codes = Code.id
LEFT JOIN (SELECT codes,
                  SUM(value) AS 'value'
           FROM Income_Fact
           GROUP BY codes) fact
ON fact.codes = Code.id

[Иван Тищенко]

Даниил: Да. Вот так:

SELECT 
  "Code".name,
  plan.value AS "plan",
  fact.value AS "fact"
FROM "Code"
LEFT JOIN (
  SELECT 
    codes,
    SUM(value) AS "value"
  FROM "Income_Plan"
  GROUP BY codes) plan
ON plan.income_codes = "Code".id
LEFT JOIN (SELECT codes,
                  SUM(value) AS "value"
           FROM "Income_Fact"
           GROUP BY codes) fact
ON fact.codes = "Code".id

[Иван Тищенко]

Даниил: но это работает хорошо, если у таблиц плана и факта одна общая опция, а нужно больше. Поэтому нужно продолжать искать решение с union

Answer 1

[Sushkov]

Для массивных запросов советую использовать метод execute, чем городить через ORM

Вот пример из фласка:

from flask.ext.sqlalchemy import SQLAlchemy 
db = SQLAlchemy(app) 
db.execute('insert into entries (title, text) values (?, ?)', 
[request.form['title'], request.form['text']])

Comments

[Иван Тищенко]

Я пока смотрю в сторону SQLAlchemy Core. Там можно использовать способы интереснее и удобнее.