CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemy
Andre Araujo
Andre Araujo

Reputation: 2400

How to make a subquery in Sqlalchemy using not in with two fields?

I need filter using not in but in two fields. 

q = db_session.query(Necessidade, WFLeilao, BidHeader, BidItemPrice, func.sbprecobruto(BidItemPrice.bid_number,BidItemPrice.line_number, Necessidade.identportal, type_=Float))
q = q.join(WFLeilao, and_(Necessidade.numeroportal == WFLeilao.leilao, Necessidade.numeroitemportal == WFLeilao.itemleilao))    
q = q.join(BidHeader, and_(BidHeader.bid_number == BidItemPrice.bid_number))    
q = q.join(BidItemPrice, and_(BidItemPrice.auction_header_id == WFLeilao.leilao, BidItemPrice.auction_line_number == WFLeilao.itemleilao, BidItemPrice.bid_number == WFLeilao.lance, BidItemPrice.line_number == WFLeilao.itemlance))    

subquery = db_session.query(ItfRetornoPedido.num_leilao_superbuy, ItfRetornoPedido.num_item_leilao_superbuy).filter_by(status_comprador=1).filter_by(acao='I').filter_by(empresa='NK').subquery()

q = q.filter(~(WFLeilao.leilao,Wfleilao.itemleilao).in_(subquery))

In oracle is possible, a similar example:

 Select *
  from table_a
 where (leilao, itemleilao) not in
       (Select num_leilao_superbuy, num_item_leilao_superbuy
          from table_b 
         where empresa = 'NK')

Is it possible?

Upvotes: 1

Views: 760

Answers (2)

Andre Araujo
Andre Araujo

Reputation: 2400

I found a solution using tuple_

q = q.filter(~tuple_(WFLeilao.leilao, WFLeilao.itemleilao).in_(subquery))

Upvotes: 2

ahmed
ahmed

Reputation: 5600

you can chain the query:

q = q.filter(~(WFLeilao.leilao.in_(subquery))) \
     .filter(~(Wfleilao.itemleilao.in_(subquery)))

Upvotes: 1

Related Questions