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

Question

Using the typical _id column and joining tables and selecting all columns when creating a cursor, will result in a cursor that contains multiple _id columns.

To access a specific _id column would then require using the actual offset, as opposed to the column name.

Using hard coded offsets can be problematic, it makes the code harder to read and thus to maintain.

For example two tables shops and aisles as per

shops Table has columns

• _id
• shopname

aisles has columns

• _id
• aisleshoplink
• aislename

Then you might want to get a cursor containing the aisle and the associated shop (aisleshoplink holding the _id of the respective shop that the aisle is in).

using SELECT * FROM aisles LEFT JOIN shops ON aisleshoplink = shops._id

Will result in Cursor that has columns

• _id (aisle's _id offset=0)
• aisleshoplink (value will be the 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 to distinguish the _id columns other than the offset. You can't prefix it with the table name as you could in SQL.

i.e cursor.getLong(_id) is ambiguous (appears from limited testing to return the last _id).

cursor.getLong("aisles._id") fails with E/SQLiteCursor: requesting column name with table name -- aisles._id being issued (results are also inconsistent sometimes only 1 fail will be shown, sometimes all fails will be shown).

So how should the appropriate _id be retrieved from the cursor, without having to resort to using the offset?

Answer 1

In short utilising AS to give the duplicate columns names specific column names.

For example instead of

SELECT * FROM Aisles LEFT JOIN Shops ON aisleshoplink = shops._id

you could use

SELECT aisles._id AS aisles_id, aisleshoplink, aislename, shops._id AS shop_id, shopname FROM aisles LEFT JOIN shops ON aisleshoplink = shops._id

However, you will then not get an _id column as may be required (e.g. for a CursorAdapter). The list of columns may also be quite extensive and you want most. So you could use

SELECT *, aisles._id AS aisles_id, shops._id AS shops_id FROM aisles LEFT JOIN shops ON aisleshoplink = shops._id

This has the disadvantage that again there are two _id columns, so using the id returned, say in an OnItemClick listener may not be as expected.

So perhaps using the first, with all columns specifically provided including specifically including the appropriate _id should be used :-

SELECT aisle._id, aisles._id AS aisles_id, aisleshoplink, aislename, shops._id AS shop_id, shopname FROM aisles LEFT JOIN shops ON aisleshoplink = shops._id

To use the above via the SQLite query method could be:-

String query_tables = "aisles LEFT JOIN shops ON aisleshoplink = shops._id";
String[] columns = {
    "aisle._id".
    "aisles._id AS aisles_id",
    "aisleshoplink",
    "aislename",
    "shops._id AS shop_id",
    "shopname"
    };
Cursor mycursor = db.query(query_tables,
    columns,null,null,null,null,null
);

The resultant cursor would have columns :-

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