INNER JOIN and WHERE clause in web2py

[Annet]

I defined the following table:

db.define_table('ntw_edge',
    Field('outID', 'reference vtx_vertex'),
    Field('inID', 'reference vtx_vertex'),
    Field('labelID', 'reference ntw_edge_label_set'),
    Field('networkkey', type='integer', default=1),
    migrate = False)


The database contains a.o. the following records:

id    outID    inID    labelID  networkkey
1    1622     1456    6          1
2    1456     1622    6          2
3    1622     1473    6          1
4    1473     1622    6          1
5    1622     1456    8          1
6    1456     1622    8          1

I want to join inID on outID where the labelIDs are the same, resulting in:

1622    1456    6    1456    1622   6
1622    1473    6    1473    1622   6
1622    1456    8    1456    1622   8

and then join this with the vtx_vertex and ntw_edge_label_set tables.
I created an alias: ntw_edge_alias

In SQL the query would be:

SELECT ...
FROM ntw_edge
INNER JOIN ntw_edge_alias ON ntw_edge.inID=alias_edge.outID
WHERE ntw_edge.outID=1622 AND (ntw_edge.labelID=alias_edge.labelID)

How do I code this in web2py? My attempts so far seem to interpret this code
(ntw_edge.labelID=alias_edge.labelID) in the WHERE clause as a join, so
the query returns too many records>

KInd regards,

Annet

[Anthony]

This:

join = db.ntw_edge.with_alias('alias_edge').on(db.ntw_edge.inID == db.alias_edge.outID)
db((db.ntw_edge.outID == 1622) & (db.ntw_edge.labelID == db.alias_edge.labelID)).select(join=join)

will produce this:

SELECT "ntw_edge"."id", "ntw_edge"."outID", "ntw_edge"."inID", "ntw_edge"."labelID", "ntw_edge"."networkkey"
FROM "ntw_edge"
JOIN "ntw_edge" AS "alias_edge" ON ntw_edge.inID = alias_edge.outID
WHERE (("ntw_edge"."outID" = 1622) AND ("ntw_edge"."labelID" = "alias_edge"."labelID"))

Not sure if that will get you the results you want, but it does produce the SQL you provided.

Anthony

[Annet]

Hi Anthony,

Thanks for your reply.

I get the results I want. Two more questions, using this syntax, how would I join

ntw_edge_inID on vtx_vertex.id

and

ntw_edge_labelID on ntw_edge_label_set.id

without ending up with more records than I have now.


Second, up to now I have been writing joins using this syntax:

rows = ((db.ntw_edge.outID==vertexID) &  (db.ntw_edge.inID==alias_edge.outID) &
 (db.ntw_edge.labelID==alias_edge.labelID) .select()

However, this does not always give me the results I want for it mixes the join with the
where clause. Am I right to conclude that the way you coded the join is the best way
to do it?

When I want to add a LEFT JOIN,  do I add it to the select() part as I did before:

left=[db.lct_address.on((db.ntw_edge.inID==db.lct_address.vertexID) &
                                    (db.lct_address.label==PHYSICALADDRESSID)),
                  db.app_settings.on((.ntw_edge.inID==db.app_settings.vertexID) & (db.app_settings.openID==PUBLIC))],


Kind regards,

Annet

On Sunday, 18 February 2018 17:46:31 UTC+1, Anthony wrote:

This:

join = db.ntw_edge.with_alias('alias_edge').on('ntw_edge.inID = alias_edge.outID')
db((db.ntw_edge.outID == 1622) & 'ntw_edge.labelID = alias_edge.labelID').select(join=join)

will produce this:

SELECT "ntw_edge"."id", "ntw_edge"."outID", "ntw_edge"."inID", "ntw_edge"."labelID", "ntw_edge"."networkkey"
FROM "ntw_edge"
JOIN "ntw_edge" AS "alias_edge" ON ntw_edge.inID = alias_edge.

outID
WHERE (("ntw_edge"."outID" = 1622) AND ntw_edge.labelID = alias_edge.labelID)

[Anthony]

On Monday, February 19, 2018 at 3:12:19 AM UTC-5, Annet wrote:

Hi Anthony,

Thanks for your reply.

I get the results I want. Two more questions, using this syntax, how would I join

ntw_edge_inID on vtx_vertex.id

and

ntw_edge_labelID on ntw_edge_label_set.id

without ending up with more records than I have now.

What is the SQL you are trying to produce?

 

Second, up to now I have been writing joins using this syntax:

rows = ((db.ntw_edge.outID==vertexID) &  (db.ntw_edge.inID==alias_edge.outID) &
 (db.ntw_edge.labelID==alias_edge.labelID) .select()

However, this does not always give me the results I want for it mixes the join with the
where clause. Am I right to conclude that the way you coded the join is the best way
to do it?

I believe either method should be equivalent.

 

When I want to add a LEFT JOIN,  do I add it to the select() part as I did before:

left=[db.lct_address.on((db.ntw_edge.inID==db.lct_address.vertexID) &
                                    (db.lct_address.label==PHYSICALADDRESSID)),
                  db.app_settings.on((.ntw_edge.inID==db.app_settings.vertexID) & (db.app_settings.openID==PUBLIC))],

Yes, for left join you need to use the "left" argument to select().

Anthony

[Annet]

HI Anthony,

Thanks for your reply.

 

What is the SQL you are trying to produce?

SELECT ...
FROM ntw_edge
INNER JOIN ntw_edge_alias ON ntw_edge.inID=alias_edge.outID

INNER JOIN vtx_vertex ON ntw_edge.inID=vtx_vertex.id
INNER JOIN ntw_edge_label_set ON ntw_edge.labelID=ntw_edge_label_set.id

WHERE ntw_edge.outID=1622 AND (ntw_edge.labelID=alias_edge.labelID)


I hope I provided you with sufficient information to get this part of the join right.


Kind regards,

Annet

[Anthony]

Just add the additional joins:

db((db.ntw_edge.outID == 1622) & (db.ntw_edge.labelID == db.ntw_edge.with_alias('alias_edge').labelID)).select(
    join=[db.alias_edge.on(db.ntw_edge.inID == alias_edge.outID),
          db.vtx_vertex.on(db.ntw_edge.inID == db.vtx_vertex.id),
          db.ntw_edge_label_set.on(db.ntw_edge.labelID == db.ntw_edge_label_set.id)])

Anthony

[Annet]

Hi Anthony,

Thanks for helping me solve this problem, I very much appreciate your help.

Annet