Re: [sqlalchemy] query.filter AND ( ... OR ... OR ) how to?

[Jose Soares]

It works, thanks Simon.
j

On 01/23/2013 12:53 PM, Simon King wrote:

On Wed, Jan 23, 2013 at 11:30 AM, Jose Soares
<jose.soa...@sferacarta.com> wrote:

Hi all,
I'm trying to compile a query to avoid Oracle limit of 1000 in IN():

def chunks(l, n):
     """ Yield successive n-sized chunks from l.  """
     for i in xrange(0, len(l), n):
      yield l[i:i+n]


qry=session.query(Azienda).fiter(Azienda.c.cap=='1111')

val=[1,3,3,4,3,23,2,4,5,6,7,8,9,90,34,2]

for jj in list(chunks(val, 5)):
     qry = qry.filter(sa.or_(Azienda.c.id.in_( jj )))


I expected a query like this one:

SELECT * FROM azienda
WHERE cap = '1111'
AND (
               azienda.id IN (%(id_1)s, %(id_2)s, %(id_3)s, %(id_4)s,
%(id_5)s)
         OR azienda.id IN (%(id_6)s, %(id_7)s, %(id_8)s, %(id_9)s, %(id_10)s)
OR azienda.id IN (%(id_11)s, %(id_12)s, %(id_13)s, %(id_14)s, %(id_15)s)
OR azienda.id IN (%(id_16)s)
         )

instead I got this one:

SELECT * FROM azienda
WHERE azienda.cap = %(cap)s
        AND azienda.id IN (%(id_1)s, %(id_2)s, %(id_3)s, %(id_4)s, %(id_5)s)
        AND azienda.id IN (%(id_6)s, %(id_7)s, %(id_8)s, %(id_9)s, %(id_10)s)
        AND azienda.id IN (%(id_11)s, %(id_12)s, %(id_13)s, %(id_14)s,
%(id_15)s)
        AND azienda.id IN (%(id_16)s)

How can I do this in the right way?
thanks for your help.
j

Successive calls to query.filter() always use AND. Each call to
query.filter() further restricts the results that would be returned
from the query.

The "or_" function is meant to take multiple conditions and "OR" them
together, so something like this should do what you want:

  conditions = []
  for jj in list(chunks(val, 5)):
      conditions.append(Azienda.c.id.in_( jj ))

  qry = qry.filter(sa.or_(*conditions))

Hope that helps,

Simon

--
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.