# define, create and populate test tables

from tutorial_tables import *
create()
data()


# first mapped class

class Order(Base):
    __table__ = orders


# simple queries

session = Session()

q = session.query(Order)
str(q)
q.filter_by(user_id=7).order_by(Order.isopen).first()
q.filter(Order.description.like('order%')).all()
q.filter((Order.user_id==7) | (Order.isopen==1)).all()
q.filter(or_(Order.user_id==7, Order.isopen==1)).all()


# User mapping, with Order relation

class User(Base):
    __table__ = users
    orders = relation(Order, order_by=[Order.order_id])


# with delete-orphan

class User(Base):
    __table__ = users
    orders = relation(Order, 
                      cascade="all, delete-orphan", 
                      order_by=[orders.c.order_id])


# with backref

class User(Base):
    __table__ = users
    orders = relation(Order,
                      backref='user',
                      cascade="all, delete-orphan", 
                      order_by=[orders.c.order_id])


# one to one

class Address(Base):
    __table__ = addresses

class User(Base):
    orders = relation(Order,
                      backref='user',
                      cascade="all, delete-orphan", 
                      order_by=[orders.c.order_id])
    address = relation(Address)


# many to many

class Keyword(Base):
    __table__ = keywords

class Item(Base):
    __table__ = orderitems
    keywords = relation(Keyword, secondary=itemkeywords)


# multi-class queries

q = session.query(User).join(User.orders)

q = session.query(User, Order).filter(User.user_id==Order.user_id)


# Relation queries

user = session.query(User).filter_by(user_id=7).one()
q = session.query(Order)
q.filter(Order.user==user).all()  
q.filter(Order.user_id==user.user_id).all()

q.filter(Order.user.has(name='jack')).all()
q.filter(Order.user.has((User.name=='jack') | (User.user_id >= 9))).all()

q = session.query(User)
q.filter(User.orders.any(Order.description.like('order%'))).all()
q.filter(User.orders.any(Order.isopen > 0)).all()

keyword = session.query(Keyword).filter_by(name='red').one()
q = session.query(Item)
q.filter(Item.keywords.contains(keyword)).all()  

q.filter(Item.keywords.contains(keyword)).all()  


# SqlSoup example

from sqlalchemy.ext.sqlsoup import SqlSoup
db = SqlSoup(metadata)
db.users.first()

s = select([func.count('*')],
           users.c.user_id==Order.user_id,
           from_obj=[orders],
           scalar=True)
s2 = select([users, s.label('order_count')]).alias('users_with_count')
db.users_with_count = db.map(s2)

db.users.relate('orders', db.orders)
db.users.first().orders