Как объединить 2 сложных запроса sqlalchemy?

Question

[iVit84]

Добрый день.
Имеются 2 таблицы в базе SQLite. Нужно взять из одной таблицы данные столбца "quantity", просуммировать и сгруппировать по значениям в столбце "barcode". Это получилось.
Нужно взять из второй таблицы данные столбца "quantity", просуммировать и сгруппировать по значениям в столбце "barcode". Это тоже получилось.
Потом нужно объединить их по значению "barcode".
Внешние ключи у таблиц не используются.

Вот модели:

class Incomes(db.Model):
 
  __tablename__ = "incomes"
 
  number = db.Column(db.Integer, primary_key=True, autoincrement=True)  
  barcode = db.Column(db.String(30))
  quantity = db.Column(db.Integer)  
 
  def __init__(self, barcode, quantity):    
      self.barcode = barcode
      self.quantity = quantity
      
class Sales(db.Model):
  __tablename__ = "sales"
  
  barcode = db.Column(db.String(30))
  quantity = db.Column(db.Integer)  
  saleID = db.Column(db.String(30), primary_key=True)  
 
  def __init__(self, barcode, quantity, saleID):      
      self.barcode = barcode
      self.quantity = quantity     
      self.saleID = saleID

Получилось сделать 2 запроса, которые по отдельности цепляют и группируют то что нужно, как объединить не пойму:

incomesQuery = db.session.query(models.Incomes, func.sum(models.Incomes.quantity).label('inc')).group_by(models.Incomes.barcode).all()

salesQuery = db.session.query(models.Sales, func.sum(models.Sales.quantity).label('sal')).group_by(models.Sales.barcode).all()

Дальше буду к этим запросам добавлять запросы из других таблиц так же по "barcode"
Буду признателен за помощь

Answer 1

[Artur-Salo]

Вот вариант способа решения:

select barcode, sum(if (tbl='incomes',quantity,0)) as inc_quant,  sum(if (tbl='sales',quantity,0)) as sal_quant 
from 
(
select 'b' as barcode, 5 as quantity, 'incomes' as tbl
union all 
select 'c' as barcode, 7 as quantity, 'incomes' as tbl
union all 
select 'b' as barcode, 10 as quantity, 'sales' as tbl
union all 
select 'c' as barcode, 100 as quantity, 'sales' as tbl
) subquery
group by barcode
;

Четыре селекта в подзапросе имитируют работу двух Ваших запросов к разным таблицам. Объединяем их и суммируем по barcode. Получаем:

"barcode"	"inc_quant"	  "sal_quant"
"b"	        "5"               "10"
"c"	        "7"    	          "100"

Comments

[iVit84]

Я так понимаю это чистый SQL, мне бы через ORM

[Artur-Salo]

Как использовать union в SQLAlchemy для объединения двух и более таблиц с неограниченным количеством общих связей?
Вот здесь расписано через ORM, только свои запросы и таблицы надо подставить:
первые два запроса - Ваши, только добавить текстовое поле с названием таблицы, объединяете в sq, к нему делаете итоговый запрос.

[iVit84]

Artur-Salo, Спасибо за наводку. C union_all не сработало, выдавал ошибку, мол в запросах должно быть одинаковое кол-во столбцов, у меня их действительно разное количество, я просто в вопросе упрощенные модели дал. Зато получилось через subquery():

incomes = db.session.query(models.Incomes.barcode, func.sum(models.Incomes.quantity).label('inc')).group_by(models.Incomes.barcode).subquery()

sales = db.session.query(models.Sales, incomes.c.inc, func.sum(models.Sales.quantity).label('sal')).group_by(models.Sales.barcode)
    .outerjoin(incomes, models.Sales.barcode == incomes.c.barcode)