Having a problem with `with_alias`

[rcalsaverini]

Hi there. I'm trying to use pydal with an existing set of tables on my database and I'm hitting a wall here about how to deal with two rows of the same table appearing in the same query.

First a working example. Imagine I have two tables representing a directed graph:

from pydal import * 

db = DAL('mysql+mysqldb://someuser:somepassword@somehost:3306/graph')

db.define_table(
    'node',
    Field('name', type="string")
)

db.define_table(
    'edge',
    Field('node_from', type=db.node),
    Field('node_to', type=db.node)
)

Than I want the simplest query, just to print the names of the nodes that are connected by an edge:

db.node.with_alias('start')
db.node.with_alias('end')
db.edge.with_alias('hop')

hop_join = (db.start.id == db.hop.node_from) & (db.end.id == db.hop.node_to)
print db(hop_join)._select(db.start.name, db.end.name)

The obtained query is:

SELECT 
    start.name, end.name
FROM
    graph.some_rname_for_node AS start,
    graph.some_rname_for_node AS end,
    graph.some_rname_for_edges AS hop
WHERE
    ((start.id = hop.node_from)
        AND (end.id = hop.node_to));

Which is exactly what I want. This works perfectly.

But for some reason, when I try this with my real database this doesn't work at all:

db.define_table(
    'position',
    Field('position_id', rname='cargo_id', type='id'),
    Field('title', rname="titulo", type="string"),
    rname='cargo'
)

db.define_table(
    'similar_positions',
    Field('similar_id', rname='cargo_similar_id', type='id'),
    Field('position_from', rname='cargo_id1', type=db.position),
    Field('position_to', rname='cargo_id2', type=db.position),
    rname='cargo_similar'
)

db.position.with_alias("position_from")
db.position.with_alias("position_to")
db.similar_positions.with_alias("hop")

hop = (
    (db.position_from.id == db.hop.position_from) & 
    (db.position_to.id == db.hop.position_to)
)

print db(hop)._select(db.position_from.title, db.position_to.title)

The sql generated is:

SELECT 
    position_from.titulo, position_to.titulo
FROM
    cargo,
    cargo_similar AS hop,
    cargo AS position_from,
    cargo AS position_to
WHERE
    ((cargo.cargo_id = hop.cargo_id1)
        AND (cargo.cargo_id = hop.cargo_id2));

which fails to use the alias and ends up not producing the inner join I want (it actually returns all pairs of position_from and position_to regardless of whether there is a hop connecting them or not.

I can't spot the difference between the two codes except for the names of the tables and fields. What am I doing wrong?

Thanks for your time.

[rcalsaverini]

I managed to narrow down the least difference between the two codes that messes the query. This will work:

db.define_table(
    'cargo',
    Field('titulo', type="string")
)

db.define_table(
    'cargo_similar',
    Field('cargo_id1', type=db.cargo),
    Field('cargo_id2', type=db.cargo)
)

db.cargo.with_alias("start")
db.cargo.with_alias("end")
db.cargo_similar.with_alias("hop")

hop_join = (db.start.id == db.hop.cargo_id1) & (db.end.id == db.hop.cargo_id2)
print db(hop_join)._select(db.start.titulo, db.end.titulo)

This won't:

from pydal import *

db = DAL('mysql+mysqldb://root:@localhost:3306/conline', migrate=False)

db.define_table(
    'cargo',
    Field('cargo_id', type='id'),
    Field('titulo', type="string")
)

db.define_table(
    'cargo_similar',
    Field('cargo_similar_id', type='id'),    
    Field('cargo_id1', type=db.cargo),
    Field('cargo_id2', type=db.cargo)
)

db.cargo.with_alias("start")
db.cargo.with_alias("end")
db.cargo_similar.with_alias("hop")

hop_join = (db.start.id == db.hop.cargo_id1) & (db.end.id == db.hop.cargo_id2)
print db(hop_join)._select(db.start.titulo, db.end.titulo)

It seems that introducing custom id fields messes something about the query generation.

[rcalsaverini]

Also, if I use the name of the custom field it seems to work:

hop_join = (db.start.cargo_id == db.hop.cargo_id1) & (db.end.cargo_id == db.hop.cargo_id2)

[ilvalle]

At pydal level you have defined cargo_id1/cargo_id2 as FK for cargo.id
How are they defined at database level ? Are cargo_id1/cargo_id2 FK for cargo.cargo_id or cargo.id?

[gi0baro]

@rcalsaverini @ilvalle may I close this?

[nextghost]

This is indeed a PyDAL bug and will be fixed by PR #433. Table.with_alias() didn't properly update all field attributes when cloning the table.