Great. The trick (for me anyway) is to add the outerjoin to session
and not to try put it somehow into mapper. Other thing I was not
aware is the fact that when the outerjoin returns NULL the whole
property becomes None so asking item.lot.CODE doesn't make sense (I
get error AttributeError: 'NoneType' object has no attribute 'CODE') -
this is a slightly different behaviour then straight SQL where
individual columns are set to NULL. No big deal I just have to guard
the .lot code with if (or exception) statement.
thanks
-- jacek
On Oct 30, 9:45 am, Michael Bayer <[EMAIL PROTECTED]> wrote:
> here is a small script illustrating your desired usage:
>
> from sqlalchemy import *
> from sqlalchemy.orm import *
>
> m = MetaData()
>
> item_lot = Table('item_lot', m,
> Column('id', Integer, primary_key=True)
> )
>
> item_sku = Table('item_sku', m,
> Column('id', Integer, primary_key=True)
> )
>
> tbl_item = Table('items', m,
> Column('id', Integer, primary_key=True),
> Column('item_lot_id', Integer, ForeignKey('item_lot.id')),
> Column('item_sku_id', Integer, ForeignKey('item_sku.id'))
> )
>
> class dbItem(object):
> pass
>
> class dbItemLot(object):
> pass
>
> class dbItemSku(object):
> pass
>
> mapper(dbItem, tbl_item, properties={'lot':relation(dbItemLot),
> 'sku':relation(dbItemSku)})
> mapper(dbItemLot, item_lot)
> mapper(dbItemSku, item_sku)
>
> print
> create_session
> ().query
> (dbItem
> ).outerjoin
> (dbItem.lot).filter(dbItem.item_sku_id==dbItemSku.id).statement
>
> statement generated is:
>
> SELECT items.id, items.item_lot_id, items.item_sku_id
> FROM item_sku, items LEFT OUTER JOIN item_lot ON item_lot.id =
> items.item_lot_id
> WHERE items.item_sku_id = item_sku.id
>
> On Oct 30, 2008, at 12:19 PM, jack2318 wrote:
>
>
>
> > I tried quite a few combinations and at the end I decided ask experts.
> > I started with primaryjoin as condition but as soon as I tried to
> > access anything from the LOT table (outerjoin table) I got error:
> > Unexpected error: <type 'exceptions.AttributeError'> 'NoneType' object
> > has no attribute 'CODE'
>
> > (I tried to access something like dbItem.lot.CODE)
>
> > At that moment I checked the generated SqL and I didn't see any LEFT
> > OUTER JOINS and decided to something introduce the outerjoin to the
> > mapper. As long there is a record in LOT table corresponding to ITEM
> > everything works fine, but as soon as I hit NULL in foreign key column
> > I cant access anything (I expect None values - substitute for NULL
> > values in such case for ALL dbItem.lot.xxxxxxxx )
>
> > The LOT table:
>
> > CREATE TABLE `ITEM_LOT` (
> > `id` int(10) unsigned NOT NULL auto_increment,
> > `CODE` varchar(20) NOT NULL,
> > `SERIAL_NO` enum('0','1') NOT NULL default '0',
> > `COMMENT` varchar(100) default NULL,
> > PRIMARY KEY (`id`)
> > ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
>
> > I just want to outerjoin LOT table to ITEM table (I sent the create in
> > previous post and this table in mapper) - please remember that the LOT
> > table has almost no records but I want all records form ITEM table
> > (typical outerjoin scenario).
>
> > the SQL I want to generate is:
> > SELECT * FROM ITEM_SKU, ITEM LEFT OUTER JOIN ITEM_LOT ON ITEM_LOT.id
> > = ITEM.ITEM_LOT_id WHERE ITEM_SKU.id = ITEM.ITEM_SKU_id;
>
> > my mapper (current version):
> > mdbItem = mapper(dbItem, tbl_item,
> > properties={
> > 'sku': relation(dbItemSKU,
> > uselist=False,
>
> > primaryjoin=tbl_item.c.ITEM_SKU_id==tbl_item_sku.c.id,
>
> > foreign_keys=[tbl_item.c.ITEM_SKU_id]
> > ),
> > 'lot': relation(dbItemLot,
> > uselist=False,
>
> > primaryjoin=tbl_item.c.ITEM_LOT_id==tbl_item_lot.c.id,
>
> > foreign_keys=[tbl_item.c.ITEM_LOT_id]
> > )})
>
> > I guess I am doing something wrong - I just can't figure out what.
>
> > thanks for help.
> > -- jacek
>
> > On Oct 30, 7:11 am, Michael Bayer <[EMAIL PROTECTED]> wrote:
> >> primaryjoin references a join condition, not a join() object. so
> >> just use the parent_table.c.col==child_table.c.col part of your
> >> join()
> >> below for primaryjoin.
>
> >> On Oct 29, 2008, at 10:43 PM, jack2318 wrote:
>
> >>> Hi,
> >>> I have 3 tables - tbl_item, tbl_item_sku, and tbl_item_lot
>
> >>> Table tbl_item has 2 FKs
>
> >>> my mapper is pretty simple:
>
> >>> mdbItem = mapper(dbItem, tbl_item,
> >>> properties={
> >>> 'sku': relation(dbItemSKU,
> >>> uselist=False,
>
> >>> primaryjoin=tbl_item_sku.c.id==tbl_item.c.ITEM_SKU_id,
>
> >>> foreign_keys=[tbl_item.c.ITEM_SKU_id]
> >>> ),
> >>> 'lot': relation(dbItemLot,
> >>> uselist=False,
>
> >>> primaryjoin
> >>> =
> >>> outerjoin
> >>> (tbl_item_lot,tbl_item,tbl_item.c.ITEM_LOT_id==tbl_item_lot.c.id),
>
> >>> foreign_keys=[tbl_item.c.ITEM_LOT_id])})
>
> >>> as soon as I try access anything from tbl_item_lot
>
> >>> e.g. dbItem.lot.CODE
>
> >>> I am getting:
> >>> Unexpected error: <class 'sqlalchemy.exc.ProgrammingError'>
> >>> (ProgrammingError) (1064, "You have an error in your SQL syntax;
> >>> check
> >>> the manual that corresponds to your MySQL server version for the
> >>> right
> >>> syntax to use near 'LEFT OUTER JOIN `ITEM` ON 1 = `ITEM_LOT`.id' at
> >>> line 3") u'SELECT `ITEM_LOT`.id AS `ITEM_LOT_id`,
> >>> `ITEM_LOT`.`CODE` AS
> >>> `ITEM_LOT_CODE`, `ITEM_LOT`.`SERIAL_NO` AS `ITEM_LOT_SERIAL_NO`,
> >>> `ITEM_LOT`.`COMMENT` AS `ITEM_LOT_COMMENT` \nFROM `ITEM_LOT` LEFT
> >>> OUTER JOIN `ITEM` ON %s = `ITEM_LOT`.id \nWHERE `ITEM_LOT` LEFT
> >>> OUTER
> >>> JOIN `ITEM` ON %s = `ITEM_LOT`.id' [1L, 1L]
>
> >>> if I just access data from first join then everything is fine.
>
> >>> Another thing is that I have to specify foreign_keys without that
> >>> I am
> >>> getting:
> >>> Unexpected error: <class 'sqlalchemy.exc.ArgumentError'> Could not
> >>> determine relation direction for primaryjoin condition
> >>> '`ITEM_SKU`.id
> >>> = `ITEM`.`ITEM_SKU_id`', on relation dbItem.sku. Specify the
> >>> 'foreign_keys' argument to indicate which columns on the relation
> >>> are
> >>> foreign.
> >>> even the relations are setup in the database
> >>> CREATE TABLE `ITEM` (
> >>> `id` int(10) unsigned NOT NULL auto_increment,
> >>> `ITEM_SKU_id` int(10) unsigned NOT NULL,
> >>> `ITEM_LOT_id` int(10) unsigned default NULL,
> >>> PRIMARY KEY (`id`),
> >>> KEY `FK_ITEM_LOT` (`ITEM_LOT_id`),
> >>> KEY `FK_ITEM_SKU` (`ITEM_SKU_id`),
> >>> CONSTRAINT `FK_ITEM_LOT` FOREIGN KEY (`ITEM_LOT_id`) REFERENCES
> >>> `ITEM_LOT` (`id`),
> >>> CONSTRAINT `FK_ITEM_SKU` FOREIGN KEY (`ITEM_SKU_id`) REFERENCES
> >>> `ITEM_SKU` (`id`)
> >>> ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
>
> >>> I guess I need help :-)
>
> >>> regards
> >>> -- jacek
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
