python - Join two queries SQLAlchemy

Question

I need to join two queries and get all the columns from both, but the returned object only contains the columns from the left query.

get_list_best_prices and get_list_best_promotions work perfectly, my problem is with get_list_best_prices_and_promotions which joins both queries.

• get_list_best_prices returns the row with the lowest price for each ean in the list eans. This is my left query/table.
• get_list_best_promotions returns the row with the lowest promotion_price for each ean in the list eans. This is my right query/table.
• get_list_best_prices_and_promotions is supposed to join the above two, so I get best price and best promotion_price

class Item(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    ean = db.Column(db.BigInteger, index=True, nullable=False)
    name = db.Column(db.String(256))
    url = db.Column(db.String(256))
    price = db.Column(db.Float)
    promotion_price = db.Column(db.Float)

    @classmethod
    def get_list_best_prices(cls, eans, prefix=''):
        subq = db.session.query(cls.ean, func.min(cls.price).label('min_price')).filter(cls.ean.in_(eans)).group_by(cls.ean).subquery()
        query = db.session.query(cls).join(subq, and_(cls.ean == subq.c.ean, cls.price == subq.c.min_price))
        query = db.session.query(cls.ean.label(prefix + 'ean'), cls.url.label(prefix + 'url'), cls.price.label(prefix + 'price'), ).join(subq, and_(cls.ean == subq.c.ean, cls.price == subq.c.min_price))
        return query

    @classmethod
    def get_list_best_promotions(cls, eans, prefix=''):
        subq = db.session.query(cls.ean, func.min(cls.promotion_price).label('min_promotion_price')).filter(cls.ean.in_(eans)).group_by(cls.ean).subquery()
        query = db.session.query(cls.ean.label(prefix + 'ean'), cls.url.label(prefix + 'url'), cls.promotion_price.label(prefix + 'promotion_price')).join(subq, and_(cls.ean == subq.c.ean, cls.promotion_price == subq.c.min_promotion_price))
        return query

    @classmethod
    def get_list_best_prices_and_promotions(cls, eans):
        prices = cls.get_list_best_prices(eans, prefix='pp_')
        alias_item_2 = aliased(Item, name='t2')
        subq_promotions = alias_item_2.get_list_best_promotions(eans, prefix='p_').subquery()
        results = prices.outerjoin(subq_promotions, and_(subq_promotions.columns.p_ean == cls.ean))
        return results

Answer 1

等待大神答复