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 sqlalchemy@googlegroups.com
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
-~----------~----~----~----~------~----~------~--~---
