Cross database joins

[rbp]

hello,

I read in this thread (http://groups.google.com/group/web2py/
browse_thread/thread/add0a8ff0a086111) that cross database joins are
supported.
I'm trying to get this working.

I define my tables like this:

db1 = SQLDB("sqlite://db1.db")
db2 = SQLDB("sqlite://db2.db")

db1.define_table('a',
SQLField('name', 'string'),
)

db2.define_table('b',
SQLField('a_id', 'integer', requires=IS_IN_DB(db1, 'a.id', '%
(name)s')),
SQLField('name', 'string'),
)

In the appadmin interface the tables are connected so I assume the
above is correct.
Unfortunately I haven't figured out the right syntax for cross
database joins.

If I try:
db2(db2.b.a_id == db1.a.id).select(db1.a.name, db2.b.name)
or:
db2().select(db1.a.name, db2.b.name, left=db2.b.on(db2.b.a_id ==
db1.a.id))
Then I get "OperationalError: no such table: db1"

What is the correct cross database join syntax?
thanks, Richard

[mdipierro]

On 26 Apr, 20:28, rbp <richar...@gmail.com> wrote:
> hello,
>
> I read in this thread (http://groups.google.com/group/web2py/
> browse_thread/thread/add0a8ff0a086111) that cross database joins are
> supported.
> I'm trying to get this working.
>
> I define my tables like this:
>
> db1 = SQLDB("sqlite://db1.db")
> db2 = SQLDB("sqlite://db2.db")
>
> db1.define_table('a',
>      SQLField('name', 'string'),
> )
>
> db2.define_table('b',
>      SQLField('a_id', 'integer', requires=IS_IN_DB(db1, 'a.id', '%
> (name)s')),
>      SQLField('name', 'string'),
> )

yes you can do this. IS_NOT_IN_DB instead may behave in a weird way.

>
> In the appadmin interface the tables are connected so I assume the
> above is correct.
> Unfortunately I haven't figured out the right syntax for cross
> database joins.
>
> If I try:
>  db2(db2.b.a_id == db1.a.id).select(db1.a.name, db2.b.name)
> or:
>  db2().select(db1.a.name, db2.b.name, left=db2.b.on(db2.b.a_id ==
> db1.a.id))
> Then I get "OperationalError: no such table: db1"
>
> What is the correct cross database join syntax?

This is not possible sorry.

Massimo

[Baron]

oh really. How do you recommend I combine data across databases?
Should I select from db2 with
db2().select(db2.b.a_id, db2.b.name)
and then do a separate query to manually replace db2.b.a_id with
db1.a.name?

Richard

[mdipierro]

You can do separate queries or you can copy the tables form database 2
into database 1. The second option will save you a lot of trouble.

Massimo