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

Question

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?

Andre Araujo · Accepted Answer · 2015-09-28 20:32:02Z

2

I found a solution using tuple_

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

answered Sep 28, 2015 at 20:32

Andre Araujo

2,3982 gold badges30 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ahmed · Accepted Answer · 2015-09-28 20:10:54Z

1

you can chain the query:

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

answered Sep 28, 2015 at 20:10

ahmed

5,6001 gold badge23 silver badges38 bronze badges

3 Comments

Andre Araujo Over a year ago

Thank you. But I think that using tuple is better

van Over a year ago

Not just better, resulting SQL is semantically very different.

Andre Araujo Over a year ago

Yes! I mean tuple_ worked for me and it was exactly I needed.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related