I have a set of normalized "log" tables consisting of a single "log" table
and multiple "x_log" tables where x is some different log type. The "log"
table contains the data that is shared among all logs such as create_date.
Each "x_log" table contains the data specific to that log type in addition
to a log_id from the log table. For example, "call_log" contains a duration
whereas "text_log" contains a body. Each table contains a log_id.
How would I write a query using the DAL that returns an array of logs,
where each row contains the log data as well as only the log details for
that specific log type? For example, if I query for all logs, I would
expect back something like:
[{
log: {
id: 1,
type: 'call',
create_date: '2014-01-01 01:01:01'
},
call_log: {
id: 1,
log_id: 1,
duration: 10
}
},
{
log: {
id: 2,
type: 'text',
create_date: '2014-02-02 02:02:02'
},
text_log: {
id: 1,
log_id: 2,
body: 'hello'
}
}]
The only way I've been able to get all the required data back is if I
specifically SELECT from ALL tables, like so:
left_join = [
db.call_log.on(db.call_log.log_id == db.log.id),
db.text_log.on(db.text_log.log_id == db.log.id)
]
db(db.log).select(
db.log.ALL,
db.call_log.ALL,
db.text_log.ALL,
left=left_join
)
However, this results in a lot of empty data:
[{
log: {
id: 1,
type: 'call',
create_date: '2014-01-01 01:01:01'
},
call_logs: {
id: 1,
log_id: 1,
duration: 10
}
text_logs: {
// Nothing here because log with id=1 is not a text log.
id: null,
log_id: null,
body: null
}
},
{
log: {
id: 2,
type: 'text',
create_date: '2014-02-02 02:02:02'
},
call_logs: {
// Nothing here because log with id=2 is not a call log.
id: null,
log_id: null,
duration: null
}
text_logs: {
id: 1,
log_id: 2,
body: 'hello'
}
}]
How would I write a query to return only the relevant information for any
given log? This is important for, say, returning the data for a GET request
efficiently instead of returning a large number of empty fields.
Thank you for your time and any help on this. The rest of web2py is proving
to be a dream to work with.
Henry
--
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 [email protected].
For more options, visit https://groups.google.com/d/optout.
