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.
