Then what is the purpose of the intersection method? It looks to me as a (bad) alternative to chained filtering!! Can you think of any case when intersection is better choice than filters?
On Jun 23, 4:08 am, Mike Conley <mconl...@gmail.com> wrote: > On Tue, Jun 21, 2011 at 6:05 AM, Eduardo <ruche...@googlemail.com> wrote: > > What is the best practice: to chain filters or to collect queries in a > > list and then apply intersect_all()? > > Overall efficiency will depend on the underlying database engine, but I > can't help but expect that most databases will be more efficient with the > chained filters query. It would take a really smart optimizer to make the > intersect method as efficient as the chained filter. > > Using an unrealistic set of queries, but it shows the principle. > > Using intersect_all will generate SQL like this: > q1 = sess.query(Book).filter(Book.title=='A') > q2 = sess.query(Book).filter(Book.title=='B') > q3 = sess.query(Book).filter(Book.title=='C') > q4 = sess.query(Book).filter(Book.title=='D') > q5 = q1.intersect_all(q2,q3,q4) > > SELECT anon_1.book_bookid AS anon_1_book_bookid, anon_1.book_title AS > anon_1_book_title, anon_1.book_authorid AS anon_1_book_authorid > FROM (SELECT book.bookid AS book_bookid, book.title AS book_title, > book.authorid AS book_authorid > FROM book > WHERE book.title = ? INTERSECT ALL SELECT book.bookid AS book_bookid, > book.title AS book_title, book.authorid AS book_authorid > FROM book > WHERE book.title = ? INTERSECT ALL SELECT book.bookid AS book_bookid, > book.title AS book_title, book.authorid AS book_authorid > FROM book > WHERE book.title = ? INTERSECT ALL SELECT book.bookid AS book_bookid, > book.title AS book_title, book.authorid AS book_authorid > FROM book > WHERE book.title = ?) AS anon_1 > > Chaining filters generates this SQL: > q7 = sess.query(Book).filter(Book.title=='A') > q7 = q7.filter(Book.title=='B') > q7 = q7.filter(Book.title=='C') > q7 = q7.filter(Book.title=='D') > > SELECT book.bookid AS book_bookid, book.title AS book_title, book.authorid > AS book_authorid > FROM book > WHERE book.title = ? AND book.title = ? AND book.title = ? AND book.title = > ? > > -- > Mike Conley -- 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.
