Unions and select from tables

[Andrew Evans]

I am trying to work in Web2py's union method and am wondering a few things

if I have a query

db( (db.sample.id>0) and (db.example.id>0) and (db.random.id>0)).select()

I would like to know how to orderby the latest ids

as well when I am displaying a page link <a href="{{=URL('view', 'page1', args=row.id)}} or whatever I may have a another page to link to with in the loop <a href="{{=URL('view', 'page2', args=row.id)}}

How can I do a check to see what table I am selecting from

if row == table1:
   display_this_link
elif row == table2:
   display 2nd link

*cheers

and ty :-)

[Massimo Di Pierro]

I see what you are doing and I like it. I think it may be tricky to implement but I look forward to see what you come up to.

I think:

db( (db.sample.id>0) and (db.example.id>0) and (db.random.id>0)).select()

should be

db( (db.sample.id>0) & (db.example.id>0) & (db.random.id>0)).select()

since "and" is a reserved keyword and cannot be overloaded.

I think you are asking about select(orderby=~db.sample.id)

[Andrew]

Does that mean sample, example, random have to have identical structures ?
What if I just want the id and name columns ? How do I select just those in a union scenario?

I know massimo doesn't like unions, but they are required sometimes. I am visualizing object relationships in a graph (picture something like http://bost.ocks.org/mike/fisheye ) and I want to get one list of nodes. I've only done it with executesql so far.

[Cliff Kachinske]

You can avoid a union like so:

rows = db(q).select(r).as_list()
rows.extend(db(qq).select(rr).as_list()

You have to access attributes dictionary fashion, not in dot notation.  Could be a problem for SQLTABLE and descendants.

Google 'sort list of dictionaries' to see how to sort it.

[Massimo Di Pierro]

You can also to

db=DAL()db.define_table('a',Field('name'))

db.define_table('b',Field('name'))

db.a.insert(name='Alex')

db.a.insert(name='Max')

db.a.insert(name='Tim')

db.b.insert(name='John')

db.b.insert(name='Jack')

def union(x,y):

    y.colnames=x.colnames

    return x|y

rows = union(db().select(db.a.name),db().select(db.b.name)).sort(lambda row: row.name)

for row in rows: print row.name

This does not translate into a union and runs at the web2py level. Mind that while testing I notice a discrepancy between docs and implementation of the sort() method. I fixed it in trunk. This code will only work with trunk.

Massimo

[Cliff Kachinske]

This is great.

I've not tried it yet, but I'm assuming I can alias the field names if the tables have different field names with the same data type.

Is it possible to combine two field names into a single alias?

Thanks

[Massimo Di Pierro]

You do not have to. The line:

y.colnames=x.colnames

does it for you, as long as the list of fields maps 1 to 1.

[Cliff Kachinske]

I didn't ask the question correctly.

In raw sql it would be something like

SELECT name FROM table, CONCAT (first_name, last_name) FROM othertable AS name WHERE blah ...

Is it possible to do that without using raw sql?

[Massimo Di Pierro]

Not without some hacks I would not suggest.