join sqlalchemy vs rome

gistfile1.txt

from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String, Table, Text, Date
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship, backref, sessionmaker, joinedload
from datetime import datetime

engine = create_engine('sqlite:///:memory:', echo=False)
Session = sessionmaker(bind=engine)
session = Session()


Base = declarative_base()
class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('user.id'))
    address = Column(String(100))

    def __init__(self,id,user_id,address):
        self.id = id
        self.user_id = user_id
        self.address= address


class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String(100))

    addresses = relationship(Address)

    def __init__(self, id, name):
        self.id = id
        self.name = name

Base.metadata.create_all(engine)

session.add_all([User(1, 'a'), \
                User(2, 'b'), \
                User(3, 'c'), \
                User(4, 'd')])


session.add_all([Address(1, 1, 'address11'),\
                 Address(2, 1, 'adress12'),\
                 Address(3, 2, 'adress21'),\
                 Address(4, 3, 'adress31')])

print '\nUser names'
for instance in session.query(User).order_by(User.id).all():
    print instance.id,instance.name

print '\nAddresses'
for instance in session.query(Address).order_by(Address.id).all():
    print instance.id,instance.address

print '\nJoin'
result = session.query(User)\
  .outerjoin(Address, User.id == Address.user_id)\
  .options(joinedload("addresses"))\
  .all()

for r in result:
  print r.id, r.name, r.addresses

gistfile2.txt

from lib.rome.core.orm.query import Query
from lib.rome.core.orm.query import Function
from _fixtures import *

import unittest

import lib.rome.driver.database_driver as database_driver
from lib.rome.core.models import Entity
from lib.rome.core.models import global_scope
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Index, Integer, BigInteger, Enum, String, schema
from sqlalchemy.dialects.mysql import MEDIUMTEXT
from sqlalchemy import orm
from sqlalchemy.orm import relationship, backref, sessionmaker, joinedload
from sqlalchemy.sql import func
from sqlalchemy import ForeignKey, DateTime, Boolean, Text, Float
import logging
from lib.rome.core.models import get_model_class_from_name
BASE = declarative_base()

from lib.rome.utils.SecondaryIndexDecorator import secondary_index_decorator

@global_scope
class Address(BASE, Entity):

    __tablename__ = 'address'

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('user.id'))
    address = Column(String(100))

    def __init__(self,id,user_id,address):
        self.id = id
        self.user_id = user_id
        self.address= address

@global_scope
class User(BASE, Entity):

    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    name = Column(String(100))

    addresses = relationship(Address)

    def __init__(self, id, name):
        self.id = id
        self.name = name


users = [User(1, 'a'), \
                User(2, 'b'), \
                User(3, 'c'), \
                User(4, 'd')]
for u in users:
    u.save()

addresses = [Address(1, 1, 'address11'),\
                 Address(2, 1, 'adress12'),\
                 Address(3, 2, 'adress21'),\
                 Address(4, 3, 'adress31'),\
                 Address(5, 4, 'adress31'),\
                 ]

for a in addresses:
    a.save()

print '\nUser names'
result = Query(User).all()
for instance in result:
    print instance.id,instance.name

print '\nUser names (faulty: result is a list of list of Users)'
result = Query(User).order_by(User.id).all()
for instance in result:
    print instance.id,instance.name


print '\nAddresses'
for instance in Query(Address).order_by(Address.id).all():
    print instance.id,instance.address

print '\nJoin (faulty)'
result = Query(User)\
  .outerjoin(Address, User.id == Address.user_id)\
  .options(joinedload("addresses"))\
  .all()

for r in result:
  print r.id, r.name, r.addresses