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 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
-~----------~----~----~----~------~----~------~--~---
