This should work. No guarantees that it will be fast. Also, limit_by
would be problematic.
Maybe you could cache the row set and just update the cache when there is a
change.
Otherwise there is a discussion here about processing the results of
executesql.
with_alias() and as_list() are well documented in the Web2py manual.
For sorting, Google "Python sorting list of dictionaries."
## use aliases so all the fields have the same name
foo = db.table.field.with_alias('bar')
fee = db.table.otherfield.with_alias('baz')
## get some data
rows = db(some_arbitrary_query).select(foo, fee).as_list()
## reset the aliases
foo = db.other_table.other_field.with_alias('bar')
fee = db.other_table.yet_another_field.with_alias('baz')
## extend the list with rows from the other table
rows.extend(db(otherquery)select(foo, fee).as_list())
## loop through the result
for row in rows:
print row['bar']
print row['baz']
On Wednesday, July 11, 2012 6:47:06 PM UTC-4, Najtsirk wrote:
>
> Hello,
>
> I have a database design dilema. I want to do a e-learning system for a
> course. The course soould have severeal lessons, each lesson can be of
> different type.
>
> Let's say we have following model:
>
> #for definiton of each course
> db.define_table('course',
>
> Field('title', 'string'))
>
>
>
>
> #for the video lesson
> db.define_table('video',
>
> Field('title', 'string'),
>
> Field('video_url', 'string'),
>
> Field('course_id', db.course),
>
> Field('weight', 'integer'))
>
>
> #for the tekst lesson
> db.define_table('text',
>
> Field('title', 'string'),
>
> Field('content', 'text'),
>
> Field('course_id', db.course),
>
> Field('weight', 'integer'))
>
>
> I know how to do this with a raw sql using UNION, like:
>
> db.executesql('SELECT title, course_id, weight FROM video UNION SELECT
> title, course_id, weight FROM text WHERE course_id = *some_value* ORDER
> BY weight')
>
> But i wondering if there is any way I can achieve that with DAL? Maybe I
> should design the database model in a different way?
>
> Thank you for your replies, ideas, thoughts..
>
> Kristjan
>
>
>
>
>
>
