join and count()

[Annet]

In a function I have the following query:


labels = db(db.vtx_label.vertexID==vertexID)._select(db.vtx_label.label)

join = db.vtx_vertex.on((db.vtx_label.vertexID==db.vtx_vertex.id) & (db.vtx_vertex.typeID==IOTHINGID))

rowset = db((db.vtx_label.label.belongs(labels)) & (db.vtx_label.vertexID!=vertexID))

count = rowset.count()
rows = rowset.select(join=join)


The problem is that the rowset needs the join to count right, is there a way to
solve this issue other then this:


labels = db(db.vtx_label.vertexID==vertexID)._select(db.vtx_label.label)

rowset = db((db.vtx_label.label.belongs(labels)) & (db.vtx_label.vertexID!=vertexID) &
                     (db.vtx_label.vertexID==db.vtx_vertex.id) & (db.vtx_vertex.typeID==IOTHINGID))

count = rowset.count()
rows = rowset.select()

I'd like to keep the join separate from the where clause.


Best,

Annet

[Anthony]

On Friday, April 13, 2018 at 8:21:25 AM UTC-4, Annet wrote:

In a function I have the following query:


labels = db(db.vtx_label.vertexID==vertexID)._select(db.vtx_label.label)

join = db.vtx_vertex.on((db.vtx_label.vertexID==db.vtx_vertex.id) & (db.vtx_vertex.typeID==IOTHINGID))

rowset = db((db.vtx_label.label.belongs(labels)) & (db.vtx_label.vertexID!=vertexID))

count = rowset.count()
rows = rowset.select(join=join)

Does this work:

label_count = db.vtx_label.id.count()
count = rowset.select(label_count, join=join).first()[label_count]

Anthony

[Annet]

Hi Anthony,

Thanks for your reply.

Does this work:

label_count = db.vtx_label.id.count()
count = rowset.select(label_count, join=join).first()[label_count]

Yes, that works, but I don't understand why it works, could you please explain
how your solution works? The table vtx_labels contains lots of labels, does
label_count count them all?


This is the working code:


labels = db(db.vtx_label.vertexID==vertexID)._select(db.vtx_label.label)

join = db.vtx_vertex.on((db.vtx_label.vertexID==db.vtx_vertex.id) & (db.vtx_vertex.typeID==IOTHINGID))

rowset = db((db.vtx_label.label.belongs(labels)) & (db.vtx_label.vertexID!=vertexID))

label_count = db.vtx_label.id.count()
count = rowset.select(label_count, join=join).first()[label_count]

rows = rowset.select(join=join)


Best,

Annet

[Anthony]

vtx_label is one of the tables involved in the join, so this query just counts one of the fields in that table, which should give a single count per row returned by the query. Should work the same if you use db.vtx_vertex.id.count() or any other field in either table. The point is just to do a count on one of the fields in the returned result.

Anthony