Thanks for your reply, Niphlod.
I suppose I hadn't considered that this is just something that's not even
possible with SQL... although, in retrospect, it seems fairly obvious.
Could you perhaps elaborate on the custom queries you mentioned? Do you
mean that you'd run a different query depending on what type you wanted
returned? I'd imagine that you'd then combine those separate results
together afterwards into the structure that you want.
I took a similar approach to achieve what I wanted: I ran the query that
returns the null data and simply iterated through the resulting rows,
appending the desired data into a new dict per row and returning a list of
those new dicts:
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)
]
rows = db(db.log).select(
db.log.ALL,
db.call_log.ALL,
db.text_log.ALL,
left=left_join
)
logs = []
for row in rows:
log = {
'log': row.log,
}
if row.log.log_type == 'call':
log['call_log'] = row.call_log
elif row.log.log_type == 'sms':
log['sms_log'] = row.sms_log
logs.append(log)
return dict(content=logs)
It's a little more manual than I would've liked but it seems to be the only
way. Thanks again for your elucidation of this.
Henry
On Saturday, April 12, 2014 11:48:39 AM UTC-7, Niphlod wrote:
>
> simply put, you can't .
> All single-shot queries return strictly a 2D resultsets, where columns
> hold values of a unique static type (or NULLs).
> There's no way in T-SQL of returning a "thing" that can be originated from
> 3 different sources, with different types.
> The thing is, with custom queries you could return just what you want
> (structure-wise), but then you'll have problem parsing those results
> because you can't know at query time what type of data a particular column
> would be. I'd bet its an issue even with NOSQL backend, but I'm not really
> an expert on those.
>
>
> On Saturday, April 12, 2014 12:40:57 AM UTC+2, Henry Nguyen wrote:
>>
>> 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.
