query with two relationships

[pedro]

Ok, this is what i am trying to replicate using web2py data object
model:

SELECT * FROM teams, matches WHERE matches.team1_id=teams.id AND
matches.team2_id=teams.id


This is what I've used, but it returns an empty record.

partidas = db( db.matches.team1_id==db.teams.id) &
(db.matches.team2_id==db.teams.id)).select()

What am I doing wrong?

[mdipierro]

try:

print db((db.matches.team1_id==db.teams.id)&
(db.matches.team2_id==db.teams.id))._select()

_select not select

and you will see this is the same as the SQL query below. I guess no
records match the query.

Massimo

[Wes James]

try the same select at your database prompt.

[DenesL]

> SELECT * FROM teams, matches WHERE matches.team1_id=teams.id AND
> matches.team2_id=teams.id

If I interpret that correctly it means: look for all matches where a
team played itself.
Unless you are looking for wrongly entered match info it would not be
very useful.

[pedro]

After looking carefully to my query I came to that conclusion too.
_select() returns exactly the SQL statement I initially thought,
problem is that was incorrect. That is indeed not useful at all.
So this turns out to be a question about basic SQL,
How do I select all matches, from the matches table, and the teams'
names from the teams table?

[billf]

in sql:

select * from matches a inner join teams b on a.team1_id=b.teams_id
inner join teams c on a.team2_id=c.team_id

so that a.? is match data, b.? is team1 data and c.? is team2 data

[mdipierro]

pedro, show us the model else we cannot answer and will have to guess.

Massimo

[DenesL]

billf, that works using db.executesql().
I wonder what is the DAL equivalent.

[mdipierro]

I am confused. What is the model. is a table? should'n b and c be the
same table?
I think you mean

db.define_table('team',db.Field('name'))
db.define_table('match',db.Field('t1',db.team),db.Field('t2',db.team))
a=db.team.with_alias('a')
b=db.team.with_alias('b')
rows=db().select(db.match.ALL,a.name,b.name,left=[a.on
(a.id==db.match.t1),b.on(b.id==db.match.t2)])
for row in rows: print row.a,row.b

This generates:

SELECT match.id, match.t1, match.t2, a.name, b.name FROM match LEFT
JOIN team AS a ON a.id=match.t1 LEFT JOIN team AS b ON b.id=match.t2;

Beat that SQLAlchemy!

Massimo

[DenesL]

> db.define_table('team',db.Field('name'))
> db.define_table('match',db.Field('t1',db.team),db.Field('t2',db.team))
> a=db.team.with_alias('a')
> b=db.team.with_alias('b')
> rows=db().select(db.match.ALL,a.name,b.name,left=[a.on
> (a.id==db.match.t1),b.on(b.id==db.match.t2)])
> for row in rows: print row.a,row.b
>
> This generates:
>
> SELECT match.id, match.t1, match.t2, a.name, b.name FROM match LEFT
> JOIN team AS a ON a.id=match.t1 LEFT JOIN team AS b ON b.id=match.t2;

I guess we missed the previous post on with_alias (and it is not in
the manual), nice!.

One thing though, this:

> for row in rows: print row.a,row.b

should be:
for row in rows: print a.name,b.name

[mdipierro]

oops actually

for row in rows: print row.a.name,row.b.name

[DenesL]

> should be:
> for row in rows: print a.name,b.name

Scratch that.
But I am getting a KeyError... looking into it.

[DenesL]

> for row in rows: print row.a.name,row.b.name

Yes, like that, or maybe even row['a.name'],row['b.name']
but do they work for you?.

On my end any attempt to access a row as in rows[0] results in:
Traceback (most recent call last):
File "<console>", line 1, in <module>
File "D:\web2py\trunk\gluon\sql.py", line 1256, in __getitem__
table=self._db[tablename]
File "D:\web2py\trunk\gluon\sql.py", line 300, in __getitem__
def __getitem__(self, key): return dict.__getitem__(self,str(key))
KeyError: 'a'

rows.response and rows.colnames are ok.

[mdipierro]

Auch! We have a bug. Will look into it.

Massimo

[mdipierro]

fixed in trunk now. Posting 1.54 later today.

Massimo

On Dec 11, 9:30 am, DenesL <denes1...@yahoo.ca> wrote:

[DenesL]

Fix tested ok ( rev.546 )
Thanks.

Any other uses for with_alias ?.

[mdipierro]

Nothing I can think of right now. In fact I do not think anybody ever
used it. Nobody reported the bug before.

[DenesL]

Hard to use it if you don't know about it.
There was only one previous post mentioning it and it is not
documented anywhere else.

I am not sure how CPU costly those double joins are but pedro could
have used:

teams=dict(db().select(db.team.ALL).response)
matches=db().select(db.match.ALL)
for m in matches: print m.id,teams[m.t1],teams[m.t2]

[pedro]

I also got a key error and I ended up using workaround just like that
one. I'll need to tune it a bit in the future so i don't end up with a
monster teams dictionary.
Or maybe when I update web2py i use massimo's solution.
BTW, those joins are not supported by google app engine's datastore...
or are they?

Anyway, thank you both for all the quick help.