The example result is a demonstration of results nested with table alias right?
If that's the case I think the `select 1, 1` part should result in:
[ { '': { '1': 1 } } ]
There's no need to return both. If the results should not be nested, here the
result I'd expect:
[ { '1': 1 } ]
With corresponding fields:
[ { name: '1', table: '', orgTable: '' } ]
> unless something like `as tabname.newcol` can override it?
I believe alias_name can't have qualifiers.
Regarding the `name` part (same case as the `1` column I guess), this is
unnecessary ambiguity. Either returning rows in pure arrays or nest the rows
with table names, the ambiguity is gone. In pure arrays:
[ [ 1, 1, 1, 'apple', 1, 'red', 1, 1 ] ]
With corresponding fields:
[ { name: '1', table: '' },
{ name: '1', table: '' },
{ name: 'fruit_id', table: 'fruit' },
{ name: 'name', table: 'fruit' },
{ name: 'color_id', table: 'color' },
{ name: 'name', table: 'color' },
{ name: 'fruid_id', table: 'fruit_color' },
{ name: 'color_id', table: 'fruit_color' } }
Or if the result is nested by tables:
[ { '': { '1': 1 } },
{ fruit: { fruit_id: 1, name: 'apple' } },
{ color: { color_id: 1, name: 'red' } },
{ fruit_color: { fruit_id: 1, color_id: 1 } } ]
That is what I've implemented in the PR I sent to node-sqlite3.
> On 16 Jan 2018, at 3:59 AM, J Decker <[email protected]> wrote:
>
> One more example for the road...
>
> create table fruit ( fruit_id,name)
> create table color ( color_id,name)
> create table fruit_color ( fruit_id,color_id )
>
> insert into fruit (fruit_id,name) values (1,'apple'),(2,'orange'),(3,'b
> anana')"
> insert into color (color_id,name) values (1,'red'),(2,'orange'),(3,'yel
> low')"
> insert into fruit_color (fruit_id,color_id) values (1,1),(2,2),(3,3)"
>
> select 1,1,fruit.*,color.* from fruit join fruit_color on
> fruit_color.fruit_id=fruit.fruit_id join color on
> fruit_color.color_id=color.color_id
>
> (If the table has columns that overlap)
>
> [ { '1': [ 1, 1 ],
> fruit: { fruit_id: 1 },
> color: { color_id: 1 },
> fruit_id: 1,
> name: [ 'apple', 'red', fruit: 'apple', color: 'red' ],
> color_id: 1 },
> { '1': [ 1, 1 ],
> fruit: { fruit_id: 2 },
> color: { color_id: 2 },
> fruit_id: 2,
> name: [ 'orange', 'orange', fruit: 'orange', color: 'orange' ],
> color_id: 2 },
> { '1': [ 1, 1 ],
> fruit: { fruit_id: 3 },
> color: { color_id: 3 },
> fruit_id: 3,
> name: [ 'banana', 'yellow', fruit: 'banana', color: 'yellow' ],
> color_id: 3 } ]
>
> can be accessed with
> result[n].fruit.name
> result[n].name.fruit
> result[n].name[0]
>
> constants and expressions get put into the base object (unless something
> like `as tabname.newcol` can override it? If a column name overlaps a
> table name, <strike>it can orphan the table object and replace with the row
> value.</strike> throws an exception and gives up. (IE
> `select fruit.name as c,fruit.*,c.* from fruit join fruit_color
> USING(fruit_id) join color as c USING(color_id)` ).
>
> wouldn't make that the production version of data to rely on... and
> wouldn't want to query 1M records with triplicated values :)
>
>
> On Mon, Jan 15, 2018 at 7:07 AM, J Decker <[email protected]> wrote:
>
>>
>>
>> On Mon, Jan 15, 2018 at 7:05 AM, J Decker <[email protected]> wrote:
>>
>>>
>>>
>>> On Mon, Jan 15, 2018 at 5:14 AM, Clemens Ladisch <[email protected]>
>>> wrote:
>>>
>>>> J Decker wrote:
>>>>> What is the expected output?
>>>>
>>>> And just out of curiosity: what should the table name be for these
>>>> columns?
>>>>
>>>> SELECT articles.gmt_deleted+tags.type, 42, id FROM articles JOIN tags
>>>> USING (id);
>>>>
>>>>
>>> table name wouldn't matter.
>>>
>>> create table articles ( id, content,gmt_deleted ) " );
>>> create table tags ( id,type, text ) " );
>>> insert into articles (id,content,gmt_deleted) values ( 1, 'hello
>>> world','X')" );
>>> insert into tags (id,type,text) values ( 1, 'text', 'MOTD')" );
>>> SELECT articles.gmt_deleted+tags.type, 42, articles.id FROM articles
>>> JOIN tags on articles.id=tags.id;" ) );
>>> [ { '42': 42, 'articles.gmt_deleted+tags.type': 0, id: 1 } ]
>>>
>>>
>> SELECT * FROM articles JOIN tags USING(id)
>> [ { id: 1,
>> content: 'hello world',
>> gmt_deleted: 'X',
>> type: 'text',
>> text: 'MOTD' } ]
>>
>>>
>>>> Regards,
>>>> Clemens
>>>> _______________________________________________
>>>> sqlite-users mailing list
>>>> [email protected]
>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>>
>>>
>>>
>>
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
