How to combine 2 complex sqlalchemy queries?

Question

Flask

Good afternoon.
There are 2 tables in the SQLite database. You need to take data from the "quantity" column from one table, sum it up and group it by the values ​​in the "barcode" column. It worked out.
You need to take the data of the "quantity" column from the second table, sum it up and group it by the values ​​in the "barcode" column. This also worked out.
Then you need to combine them by the "barcode" value.
Foreign keys are not used for tables.

Here are the models:

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

It turned out to make 2 requests, which separately catch and group what is needed, I don’t understand how to combine:

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()

Further I will add queries from other tables to these queries also by "barcode"
I will be grateful for your help

Answer 1

Here is a possible solution:

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
;

The four selects in the subquery mimic the operation of your two queries against different tables. We combine them and summarize by barcode . We get:

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