Hi J Decker,
`alias_name` can only be identifiers without qualifiers I believe. Hence
`select 1 1` isn't possible.
Changing values to arrays to allow multi values on same column be able to
co-exist is a workaround. It works perfect on orphan columns such as `select 1
a, 2 a`. Though it cannot reflect the field metadata clearly enough. In your
previous example, the value of name column is `name: ['orange', 'orange',
fruit: 'orange', color: 'orange']`. I believe this isn't valid JSON. It's
possible in JavaScript since Array is just special Object but it doesn't mean
it's easy to process.
On the other hand, formatting the result as an array of objects by default is
the right thing to do. I haven't got any intention to oppose that. What I
suggested is, the results can be array of arrays or objects nested with table
names. Postgres does that by `.query(sql, { rowMode: 'array' })`. MySQL does
that by `.query(sql, { nestTables: true })`.
Oh, I think these discussions are a bit off topic. I sent this email mostly to
request the `sqlite3_column_table_alias_name()` API. I've got the original
patch working
<https://github.com/dotnil/node-sqlite3/blob/master/deps/sqlite3.patch> in my
fork of node-sqlite3.
> On 16 Jan 2018, at 10:11 AM, J Decker <d3c...@gmail.com> wrote:
>
> On Mon, Jan 15, 2018 at 5:34 PM, Jake Chen <jakep...@gmail.com> wrote:
>
>> 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 } ]
>>
>
> That is `select 1`
> yes, if they happen to be the same value you can collapse them. But if you
> end up with 1 columns with the same alias (a few things I tried were
> prevented by having to specify the query a certain way).
>
> (although I don't think aliases work as numbers either)
> select 1 1, 2 1
> [ { 1: [ 1, 2 ] } ]
>
> select 1 a, 2 a
> [ { a: [ 1, 2 ] } ]
>
> (and there's no table named to specify keyed values in that result.)
>
>
>
>>
>> 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 } } ]
>>
>>
> This is what it would be, other than '' would be '1' because that IS the
> column name there... it just happens that two values have the same alias
> name (bad query writing; )
>
> When I was implementing test case I couldn't do 'select 1 as foo.id' of
> course then I'd want to be able to alias the database name it's in too....
>
> I'm not a fan of arrays; that the rows are returned is enough :)
> If you never have to column names the same all values would be flat.
> I took the overlap as an array from `mssql` node package(s).
>
> And then just decided to throw in all of the options so you can reference
> it in many ways... testing if( record.field.length ) as required; but if
> you can use USING() you only get 1 column back in a * query (those will be
> the same value sorted by sqlite nicely)
>
>
>
>
>
>
>> That is what I've implemented in the PR I sent to node-sqlite3.
>>
>>> On 16 Jan 2018, at 3:59 AM, J Decker <d3c...@gmail.com> 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 <d3c...@gmail.com> wrote:
>>>
>>>>
>>>>
>>>> On Mon, Jan 15, 2018 at 7:05 AM, J Decker <d3c...@gmail.com> wrote:
>>>>
>>>>>
>>>>>
>>>>> On Mon, Jan 15, 2018 at 5:14 AM, Clemens Ladisch <clem...@ladisch.de>
>>>>> 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
>>>>>> sqlite-users@mailinglists.sqlite.org
>>>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>>>>
>>>>>
>>>>>
>>>>
>>> _______________________________________________
>>> sqlite-users mailing list
>>> sqlite-users@mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
