[Sqlalchemy-users] eager load: is this correct?

[Sandro Dentella]

Hi,

  the attached script produces a sql statement that I can't correctly
  interpret but anyhow gives wrong results (doubled rows):

  m.select(users.c.user_id == mails.c.user_id, from_obj=[users])



    SELECT user_tbl.user_last_name AS user_tbl_user_last_name,
    mail_tbl_3b11.user_id AS mail_tbl_3b11_user_id, mail_tbl_3b11.mail_address
    AS mail_tbl_3b11_mail_address, user_tbl.user_id AS user_tbl_user_id,
    user_tbl.user_name AS user_tbl_user_name 
    FROM mail_tbl, user_tbl LEFT OUTER JOIN mail_tbl AS mail_tbl_3b11 
    ON user_tbl.user_id = mail_tbl_3b11.user_id
    WHERE user_tbl.user_id = mail_tbl.user_id

  All the examples in the documentation have a neater statement (FROM table1
  LEFT JOIN table2).  

  Adding another condition as in 
  
  m.select(and_(
    users.c.user_id == mails.c.user_id ,
    mails.c.mail_address.like('%a%')
    )
    from_obj=[users])

  just fails (no LIKE whereclause applied)

  Is this an SA bug or I'm doing something wrong with the select?


  TIA
  sandro


PS: should I use from_obj? I never see a difference if I use the join
    condition... 


-- 
Sandro Dentella  *:-)
e-mail: [EMAIL PROTECTED] 
http://www.tksql.org                    TkSQL Home page - My GPL work
from sqlalchemy import *
import sys, re, datetime

sys.path.append ('../../../')
from sqlalchemy import *

eng = create_engine("sqlite://filename=test-sql.db", echo=False)
#eng = create_engine("sqlite://", echo=False)
#eng = config.spawnEngine()

users = Table('user_tbl', eng,
              Column('user_id', Integer, primary_key = True),
              Column('user_name', String(30), nullable = False),
              Column('user_last_name', String(30), nullable = False))

mails = Table('mail_tbl', eng, 
              Column('mail_address', String(30), nullable = True,
                     primary_key = True),
              Column('user_id', Integer, ForeignKey("user_tbl.user_id")))



objectstore.commit()

users.create()
mails.create()

users.insert().execute(
    {'user_id': 1 , 'user_name': 'Sam', 'user_last_name': 'Patts', 'city_id' : '1' },
    {'user_id': 2 , 'user_name': 'Sid', 'user_last_name': 'Watts', 'city_id' : '2' },
    {'user_id': 3 , 'user_name': 'Axe', 'user_last_name': 'Smith', 'city_id' : '3' },
    {'user_id': 4 , 'user_name': 'Ted', 'user_last_name': 'Will' , 'city_id' : '4'  })


mails.insert().execute(
    {'mail_address': '[EMAIL PROTECTED]',  'user_id': 1},
    {'mail_address': '[EMAIL PROTECTED]',  'user_id': 2},
    {'mail_address': '[EMAIL PROTECTED]',  'user_id': 4},
    {'mail_address': '[EMAIL PROTECTED]', 'user_id': 4})
                       
class User(object): pass
class Mail(object): pass

m = mapper(Mail, mails)
rel = relation(m, lazy=False)

eng.echo = True
m = mapper(User, users, properties = { 'mail': rel } )


ret = m.select(and_(
    users.c.user_id == mails.c.user_id ,
    mails.c.mail_address.like('%a%')
    )
    from_obj=[users])


# for R in ret:
#     print R.user_name, R.mail.mail_address