Joins

To simply join 2 tables without any other conditions, use the '*' operator. Conditions can be added to the query as usual.

foo = db.foo()
test = db.test()
query = foo * test            # SELECT * FROM foo,test
query &= foo.id == test.id
query.AddCol(foo.money + test.money)

The '**' operator is provided for NATURAL INNER JOIN. This operation joins 2 tables on columns that have the same name.

foo = db.foo()
test = db.test()
query = foo ** test          # SELECT * FROM foo natural inner join test

The query object does not expose column names as attributes. You mast use the original select instances of the table.

query = db.foo() ** db.test()
query.AddCol(query.age)              # This DOES NOT work

Other types of joins are provided using methods LeftJoin(), RightJoin(), OuterJoin() and InnerJoin(). First parameter is table name, second optional parameter is join condition. If the second parameter is not specified, it defaults to natural join.

All instance methods of select instance modify directly the instance. When you join a table to a query, you cannot continue accessing the column names of the query. The method Copy() is provided to create a copy of the instance, that can be further modified.

foo = db.foo()
test = db.test()
foo.LeftJoin(test, foo.userid == test.userid) 
foo.AddCol(foo.name)                 # Incorrect, foo now contains 2 tables and the columns cannot be accessed
##################
# Correct
foo = db.foo()
test = db.test()
query = foo.Copy().LeftJoin(test, foo.userid == test.userid)
query.AddCol(foo.name)

Subqueries in FROM clause

If you wish to use a query as a 'virtual table' in another query, create a special instance of the query by calling the SubQuery() method. This instance will look like a new query with 1 table (containing result of the subquery) and you can easily access the columns of the table.

test = db.test()
test.AddCol(db.a.sum(test.carnum) >> 'cars', test.city)
subquery = test.SubQuery()

foo = db.foo('name')
query = foo.Copy().Join(subquery, foo.city=subquery.city)
query.AddCol(subquery.carnum)

# Query now contains names from foo and for every name the total number
# of cars that are registered in this city

Union, except, intersect

Operations union, except and intersect on queries are performed easily using operators '+', '-' and '%' respectively.

## UNION
new_york = db.foo('name',city='New York')
atlanta = db.foo('name',city='Atlanta'
result = new_york + atlanta

## EXCEPT
new_york = db.foo('name',city='New York')
strangers = db.foo('name',born='Mars')
nonstrangers = new_york - strangers

## INTERSECT
new_york = db.foo('name',city='New York')
blueeyes = db.test('name',eyes='blue')
new_york_blueeyes = new_york % blueeyes

These examples would better be created using an ordinary join, but it illustrates the point.

---