android - How do I access individual _id columns when there are multiple _id columns from a join? -

using typical _id column , joining tables , selecting columns when creating cursor, result in cursor contains multiple _id columns.

to access specific _id column require using actual offset, opposed column name.

using hard coded offsets can problematic, makes code harder read , maintain.

for example 2 tables shops , aisles per

shops table has columns

• _id
• shopname

aisles has columns

• _id
• aisleshoplink
• aislename

then might want cursor containing aisle , associated shop (aisleshoplink holding _id of respective shop aisle in).

using select * aisles left join shops on aisleshoplink = shops._id

will result in cursor has columns

• _id (aisle's _id offset=0)
• aisleshoplink (value respective shop's _id, offset = 1)
• aislename (offset =2)
• _id (the shop's _id, should match aisleshoplink, offset = 3)
• shopname (offset =4)

the resultant cursor has nothing distinguish _id columns other offset. can't prefix table name in sql.

i.e cursor.getlong(_id) ambiguous (appears limited testing return last _id).

cursor.getlong("aisles._id") fails e/sqlitecursor: requesting column name table name -- aisles._id being issued (results inconsistent 1 fail shown, fails shown).

so how should appropriate _id retrieved cursor, without having resort using offset?

in short utilising as give duplicate columns names specific column names.

for example instead of

select * aisles left join shops on aisleshoplink = shops._id

you use

select aisles._id aisles_id, aisleshoplink, aislename, shops._id shop_id, shopname aisles left join shops on aisleshoplink = shops._id

however, not _id column may required (e.g. cursoradapter). list of columns may quite extensive , want most. use

select *, aisles._id aisles_id, shops._id shops_id aisles left join shops on aisleshoplink = shops._id

this has disadvantage again there 2 _id columns, using id returned, in onitemclick listener may not expected.

so perhaps using first, columns provided including including appropriate _id should used :-

select aisle._id, aisles._id aisles_id, aisleshoplink, aislename, shops._id shop_id, shopname aisles left join shops on aisleshoplink = shops._id

to use above via sqlite query method be:-

string query_tables = "aisles left join shops on aisleshoplink = shops._id"; string[] columns = {     "aisle._id".     "aisles._id aisles_id",     "aisleshoplink",     "aislename",     "shops._id shop_id",     "shopname"     }; cursor mycursor = db.query(query_tables,     columns,null,null,null,null,null ); 

the resultant cursor have columns :-

• _id
• aisles_id
• aisleshoplink
• aislename
• shops_id
• shopname