I was trying a more complex query where the table name isn't a simple table
name...
select * from ( select * from addresses join user on user.address_id=
addresses.id ) address;
but that gives like really strange column names...
# sqlite3 output
id|name|id:1|address_id|name:1
1|there|1|1|bob
# my output
[ { id: 1, name: 'there', 'id:1': 1, address_id: 1, 'name:1': 'bob' } ]
so if I were to try an do like select * from ( select * from addresses
join user on user.address_id=addresses.id ) address join pet on pet.user_id=
address.id:1;
is a syntax error.... ok...
select * from ( select count(*)count,* from addresses join users on
users.address_id=addresses.id ) address join pets pet on
pet.user_id=address.'id:1';
-- I guess that doesn't really make a 'table' to pull from....
1,1,there,1,1,bob,1,1,odif
Column 0 : table_name:(null) origin_name:(null) table_alias:(null)
Column 1 : table_name:addresses origin_name:id table_alias:addresses
Column 2 : table_name:addresses origin_name:name table_alias:addresses
Column 3 : table_name:users origin_name:id table_alias:users
Column 4 : table_name:users origin_name:address_id table_alias:users
Column 5 : table_name:users origin_name:name table_alias:users
Column 6 : table_name:pets origin_name:id table_alias:pet
Column 7 : table_name:pets origin_name:user_id table_alias:pet
Column 8 : table_name:pets origin_name:name table_alias:pet
[
{
addresses: { id: 1, name: 'there' },
users: { 'id:1': 1, address_id: 1, 'name:1': 'bob' },
pet: { id: 1, user_id: 1, name: 'odif' },
count: 1,
id: [ 1, 1, addresses: 1, pet: 1 ],
name: [ 'there', 'odif', addresses: 'there', pet: 'odif' ]
}
]
On Thu, Aug 15, 2019 at 11:51 AM J Decker <[email protected]> wrote:
> I would really like to get the table alias specified in the query, with an
> unpatched version of Sqlite.
>
>
> So here's a script...
>
> ------
> .headers on
>
> create table addresses( id, name );
> create table users( id, address_id, name );
> create table pets(id, user_id, name );
>
> insert into addresses(id,name) values ( 1, "there" );
> insert into users(id,address_id,name) values ( 1, 1, "bob" );
> insert into pets(id,user_id,name) values ( 1,1, "odif" );
>
> select * from users user join addresses address on address.id=user.address_id
> join pets pet on pet.user_id=user.id;
>
> ------
> This is the output, which you can see in the one case for suer multiple
> values come back that are 'id'
>
> id|address_id|name|id|name|id|user_id|name
> 1|1|bob|1|there|1|1|odif
>
> Sqlite command line tool doesn't really provide all of the information
> available though...
>
> ---
> This is the returns of
> sqlite3_column_table_name
> sqlite3_column_origin_name
> , and the function that doesn't exist yet : sqlite3_column_table_alias
>
> data : 1,1,bob,1,there,1,1,odif
> Column 0 : table_name:users origin_name:id table_alias:user
> Column 1 : table_name:users origin_name:address_id table_alias:user
> Column 2 : table_name:users origin_name:name table_alias:user
> Column 3 : table_name:addresses origin_name:id table_alias:address
> Column 4 : table_name:addresses origin_name:name table_alias:address
> Column 5 : table_name:pets origin_name:id table_alias:pet
> Column 6 : table_name:pets origin_name:user_id table_alias:pet
> Column 7 : table_name:pets origin_name:name table_alias:pet
>
>
> Desired output:
> [
> {
> user: { id: 1, address_id: 1, name: 'bob' },
> address: { id: 1, name: 'there' },
> pet: { id: 1, user_id: 1, name: 'odif' },
> id: [ 1, 1, 1, user: 1, address: 1, pet: 1 ],
> name: [
> 'bob',
> 'there',
> 'odif',
> user: 'bob',
> address: 'there',
> pet: 'odif'
> ]
> }
> ]
>
> // in the resulting object returned to JS, I keep column names that are
> duplicated as both an array...
> id[0], id[1], id[2] but also related to the table (alias) they come
> from. id.name, id.pets, id.address... but conversely by table (alias)
> name... name.id, address.id, pets.id
>
> -----------
>
> I would really like to get the table alias specified in the query, with an
> unpatched version of Sqlite.
>
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
