Thanks guys. I'm using automap, but I'm not completely sure how much that gives me for free. Yes, these tables are big, and the resulting set would be worrying large (potentially 5*200000, and that's without the attributes and attachments, plus their assignment and values tables). I've switched to left outerjoins, hoping that that will keep things smaller.
My original query is below. This works fine, but only gets items that have vendor, attributes, and attachments. Items may have some or none of these, as I was just informed today. As you can probably see, item.itm_id ties everything together. It's used as the key for assignmentTable, which holds all the attachments associated with the item and uses att_id to index into attachmentTable and attachmentTextTable. A similar relationship exists for attributes. old_items = session.query(itemTable, attachmentTable, attachmentTextTable, assignmentTable, attributeTable, attributeValueTable, attributeValueAssignmentTable, vendorTable)\ .filter(vendorTable.PVVNNO == itemTable.itm_vendornum)\ .filter(assignmentTable.itm_id == itemTable.itm_id)\ .filter(assignmentTable.att_id == attachmentTable.att_id)\ .filter(assignmentTable.att_id == attachmentTextTable.att_id)\ .filter(attributeValueAssignmentTable.itm_id == itemTable.itm_id)\ .filter(attributeTable.attr_id == attributeValueAssignmentTable.attr_id)\ .filter(attributeValueTable.attr_value_id == attributeValueAssignmentTable.attr_value_id) My next thought was to break this down into multiple queries: allItems = session.query(items)\ .filter(items.itm_webflag != 'N', items.itm_suspflag != 'Y') itemVendors = allItems.query(vendorTable).filter(vendorTable.PVVNNO == itemTable.itm_vendornum) attachments = allItems.query(assignmentTable, attachmentTable, attachmentTextTable)\ .filter(assignmentTable.itm_id == itemTable.itm_id)\ .filter(assignmentTable.att_id == attachmentTable.att_id)\ .filter(assignmentTable.att_id == attachmentTextTable.att_id)\ attributes = allItems.query(attributeTable, attributeValueTable, attributeValueAssignmentTable)\ .filter(attributeValueAssignmentTable.itm_id == itemTable.itm_id)\ .filter(attributeTable.attr_id == attributeValueAssignmentTable.attr_id)\ .filter(attributeValueTable.attr_value_id == attributeValueAssignmentTable.attr_value_id) The problem was, I couldn't work out how to put them together. Given some item ID, how would I access that item's attributes or attachments without making tons of queries back to the database? Currently, I'm using this query, which I haven't yet even tested: items = session.query(itemTable)\ .outerjoin(vendorTable, vendorTable.PVVNNO == itemTable.itm_vendornum)\ .outerjoin(assignmentTable, assignmentTable.itm_id == itemTable.itm_id)\ .filter(assignmentTable.att_id == attachmentTable.att_id)\ .outerjoin(attachmentTextTable, assignmentTable.att_id == attachmentTextTable.att_id)\ .outerjoin(attributeValueAssignmentTable, attributeValueAssignmentTable.itm_id == itemTable.itm_id)\ .outerjoin(attributeTable, attributeTable.attr_id == attributeValueAssignmentTable.attr_id)\ .filter(attributeValueTable.attr_value_id == attributeValueAssignmentTable.attr_value_id) On 3/15/16, Christopher Lee <c...@sirdodger.net> wrote: > Note that if your items have a lot of attributes and attachments, an > outer-join will return a multiplicatively-large result set. That will get > boiled down into a sane number of objects by the SqlAlchemy ORM, but your > performance might be ugly in terms of I/O to your database, or the > processing time it takes to allocate the entire result set. If the related > tables are small, then querying all the data in a single query can be a lot > faster. > > Anyway, it would help to see some code and know if you are using just the > Core, or if you are using the ORM and have relationships defined. You can > pretty easily force an outer join on a relationship by setting the eager > loading argument to "joined". If you are using queries directly, then > Jonathan's suggestions above should get you where you need to go. > > > > On Tue, Mar 15, 2016 at 10:17 AM, Jonathan Vanasco <jonat...@findmeon.com> > wrote: > >> The ORM has an `outerjoin` method on queries: >> >> http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.outerjoin >> >> You can also pass "isouter=True" to `join` >> >> http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.join >> >> The core supports an outerjoin in both variations as well: >> >> http://docs.sqlalchemy.org/en/latest/core/selectable.html?highlight=outer#sqlalchemy.sql.expression.Join.join >> >> http://docs.sqlalchemy.org/en/latest/core/selectable.html?highlight=outer#sqlalchemy.sql.expression.Join.outerjoin >> >> -- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to sqlalchemy+unsubscr...@googlegroups.com. >> To post to this group, send email to sqlalchemy@googlegroups.com. >> Visit this group at https://groups.google.com/group/sqlalchemy. >> For more options, visit https://groups.google.com/d/optout. >> > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
