Web2py is more explicit than that. I think you are asking about the
Field.Lazy in the example below
db=DAL()
db.define_table('mr_link',
Field('recipient_id','reference recipient'),
Field('msg_id','reference message'))
db.define_table('att_link',
Field('file_id','reference attachment'),
Field('msg_id','reference message'))
db.define_table('attachment',
Field('filename'),
Field('received','datetime'),
Field('hash',unique=True),
Field('data','blob'))
db.define_table('message',
Field('subject'),
Field('headers'),
Field('body','text'),
Field('date_received','datetime'),
Field('raw_original','text'),
Field('sender_id','reference sender'))
db.define_table('recipient',
Field('email_address',unique=True))
db.define_table('sender',
Field('email_address',unique=True))
##############
db.message.recipients = Field.Lazy(lambda
row,**args:db(db.recipient.id==db.mr_link.recipient_id)(db.mr_link.msg_id==row.message.id).select(d\
b.recipient.ALL,**args))
db.message.attachments = Field.Lazy(lambda
row,**args:db(db.attachment.id==db.att_link.file_id)(db.att_link.msg_id==row.message.id).select(db\
.attachment.ALL,**args))
##############
# insert records
sender_id = db.sender.insert(email_address='[email protected]')
msg_id = db.message.insert(sender_id=sender_id,subject='test')
rec1 = db.recipient.insert(email_address='[email protected]')
db.mr_link.insert(msg_id=msg_id,recipient_id=rec1)
rec2 = db.recipient.insert(email_address='[email protected]')
db.mr_link.insert(msg_id=msg_id,recipient_id=rec2)
# select records
for message in db(db.message).select():
print message.subject, message.sender_id.email_address
print 'Recipients:'
for recipient in message.recipients(orderby=db.recipient.email_address):
print recipient.email_address
print 'Attachments:'
for attachment in message.attachments(orderby=db.attachment.filename):
print attachment.filename
On Monday, 20 August 2012 07:14:16 UTC-5, Larry Wapnitsky wrote:
>
> from sqlalchemy import Table, Column, Integer, String, DateTime, MetaData,
> ForeignKey
>
>
> from sqlalchemy.orm import relationship, backref
>
>
> from sqlalchemy.dialects.mysql import \
> BIGINT, BINARY, BIT, BLOB, BOOLEAN, CHAR, DATE, \
> DATETIME, DECIMAL, DECIMAL, DOUBLE, ENUM, FLOAT, INTEGER, \
> LONGBLOB, LONGTEXT, MEDIUMBLOB, MEDIUMINT, MEDIUMTEXT, NCHAR, \
> NUMERIC, NVARCHAR, REAL, SET, SMALLINT, TEXT, TIME, TIMESTAMP, \
> TINYBLOB, TINYINT, TINYTEXT, VARBINARY, VARCHAR, YEAR
>
>
> from sqlalchemy.ext.declarative import declarative_base
>
>
> Base = declarative_base()
>
>
> mr_link = Table( 'mr_link', Base.metadata,
> Column( 'recipient_id', Integer, ForeignKey( 'recipient.id
> ' ) ),
> Column( 'msg_id', Integer, ForeignKey( 'message.id' ) )
> )
>
>
> ma_link = Table( 'att_link', Base.metadata,
> Column( 'file_id', Integer, ForeignKey( 'attachment.id' )
> ),
> Column( 'msg_id', Integer, ForeignKey( 'message.id' ) )
> )
>
>
> class Attachment( Base ):
> __tablename__ = "attachment"
>
>
> id = Column( Integer, primary_key = True )
> filename = Column( VARCHAR( 255 ) )
> received = Column( DATETIME )
> hash = Column( VARCHAR( 255 ) , unique = True )
> data = Column( LONGBLOB )
>
>
> def __init__( self, filename, received, hash, data ):
> self.filename = filename
> self.received = received
> self.hash = hash
> self.data = data
>
>
>
>
> class Message( Base ):
> __tablename__ = "message"
>
>
> id = Column( Integer, primary_key = True )
> subject = Column( TEXT )
> headers = Column( TEXT )
> body = Column( LONGTEXT )
> dateReceived = Column( DateTime )
> raw_original = Column( LONGTEXT )
>
>
> sender_id = Column( Integer, ForeignKey( 'sender.id' ) )
>
>
> recipients = relationship( 'Recipient',
> secondary = mr_link,
> backref = 'message',
> lazy = 'dynamic' )
>
>
> attachments = relationship( 'Attachment',
> secondary = ma_link,
> backref = 'message',
> lazy = 'dynamic' )
>
>
> def __init__( self, subject, headers, body, dateReceived,raw_original
> ):
> self.subject = subject
> self.headers = headers
> self.body = body
> self.dateReceived = dateReceived
> self.raw_original = raw_original
>
>
> class Recipient( Base ):
> __tablename__ = 'recipient'
>
>
> id = Column( Integer, primary_key = True )
> email_address = Column( VARCHAR( 100 ) , unique = True )
>
>
> def __init__( self, email_address ):
> self.email_address = email_address
>
>
> class Sender( Base ):
> __tablename__ = 'sender'
>
>
> id = Column( Integer, primary_key = True )
> email_address = Column( VARCHAR( 100 ) , unique = True )
>
>
> messages = relationship( "Message" , backref = 'sender' )
>
>
> def __init__( self, email_address ):
> self.email_address = email_address
>
>
>
>
> On Friday, August 17, 2012 4:49:32 PM UTC-4, Massimo Di Pierro wrote:
>>
>> Not sure what you asking. You are welcome to post sqlachemy code.
>>
>> On Friday, 17 August 2012 15:31:30 UTC-5, Larry Wapnitsky wrote:
>>>
>>> also, do you think I might be better off doing this in MongoDB rather
>>> than MySQL/SQLAlchemy?
>>>
>>> On 8/17/2012 3:41 PM, Massimo Di Pierro wrote:
>>>
>>> It depends on context. Web2py DAL is closer to SQL than on ORM. Assuming
>>>
>>> db.define_table('person',Field('name'),Field('email'))
>>>
>>> you have two options:
>>>
>>> 1)
>>>
>>> db.define_table('message',Field('body'),Field('recipients','list:reference
>>> person'))
>>>
>>> for row in db(db.message).select(): # one select
>>> for recipient in row.recipents:
>>> print recipient.name # one select per recipient to get name
>>> (lazy)
>>>
>>>
>>> 2)
>>>
>>> db.define_table('message',Field('body'))
>>>
>>> db.define_table('recipient',Field('message',db.message),Field('person',db.person))
>>>
>>> for message in db(db.message).select():
>>> for recipient in message.recipient.select() # one select/message
>>> print recipient.person.name # one select/recipient
>>>
>>> or with a single select using joins:
>>>
>>> for row in db(db.message.id
>>> ==db.recipient.message)(db.recipient.person==person.id).select()
>>> print row.message.body, row.person.id
>>>
>>>
>>>
>>>
>>> On Friday, 17 August 2012 11:01:34 UTC-5, Larry Wapnitsky wrote:
>>>>
>>>> OK. I"m getting close, but I"m stuck on the following SQLAlchemy code
>>>> conversion:
>>>>
>>>> recipients = relationship( 'Recipient',
>>>> secondary = mr_link,
>>>> backref = 'message',
>>>> lazy = 'dynamic' )
>>>>
>>>>
>>>> attachments = relationship( 'Attachment',
>>>> secondary = ma_link,
>>>> backref = 'message',
>>>> lazy = 'dynamic' )
>>>>
>>>>
>>>> I don't see how to adapt this in the manual.
>>>>
>>>> TIA,
>>>> Larry
>>>>
>>>> On Friday, August 17, 2012 8:24:41 AM UTC-4, Larry Wapnitsky wrote:
>>>>>
>>>>> Thanks, Massimo. I'll give this a read and see if I can adapt it
>>>>> properly.
>>>>>
>>>>> As usual, your hard work is greatly appreciated.
>>>>>
>>>>> On 8/15/2012 6:34 PM, Massimo Di Pierro wrote:
>>>>>
>>>>> I meant this:
>>>>>
>>>>> http://web2py.com/AlterEgo/default/show/189
>>>>>
>>>>>
>>>>> On Wednesday, 15 August 2012 14:22:36 UTC-5, Larry Wapnitsky wrote:
>>>>>>
>>>>>> I have a project in which I've just written the database functions
>>>>>> using SQLAlchemy. It was much simpler than my original, hand-written
>>>>>> SQL
>>>>>> queries, especially once I got the hang of creating "relationships" with
>>>>>> SA's ORM.
>>>>>>
>>>>>> Now, I would like to create a front-end for this using web2py, but,
>>>>>> from experience, I know the DAL and SA's ORM are very different.
>>>>>>
>>>>>> What's the group's view on the best way to integrate my two
>>>>>> projects?
>>>>>>
>>>>> --
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>
>>>
>>>
>>>
>>>
>>>
--
