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)
>
> Not sure if that will get you the results you want, but it does produce
> the SQL you provided.
>
> Anthony
>
> On Sunday, February 18, 2018 at 11:04:19 AM UTC-5, Annet wrote:
>>
>> 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
>>
>>
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
