CODEWITHSUNDEEP
androidandroid-sqliteandroid-contentprovider
Ariel Carbonaro
Ariel Carbonaro

Reputation: 1539

Content provider with multiple joins on same table

I'm working with a ListView that has a CursorAdapter and the query is done via LoaderManager to my ContentProvider. Everything seems to work fine, but when I try to do a multiple query with the same table I get a duplicated column error:

table rating:

 CREATE TABLE rating ( _id INTEGER PRIMARY KEY, message TEXT NOT NULL, 
      from_user_id INTEGER NOT NULL, to_user_id INTEGER NOT NUL);

table users:

 CREATE TABLE users ( _id INTEGER PRIMARY KEY, name TEXT NOT NULL, 
      avatar TEXT, GENDER TEXT);

QueryBuilder:

    queryBuilder = new SQLiteQueryBuilder();
    queryBuilder.setTables(rating LEFT JOIN users ON  
    rating.from_user_id = users._id LEFT JOIN users 
    ON rating.to_user_id = users._id );

Is there any way to use an alias or any similar approach?

Upvotes: 0

Views: 193

Answers (1)

Marten
Marten

Reputation: 3872

Yes, you can use the as keyword to set an alias for a table. Check out https://www.sqlite.org/syntax/join-clause.html and https://www.sqlite.org/syntax/table-or-subquery.html.

Your join clause would look like this:

rating LEFT JOIN users as from_users ON rating.from_user_id = from_users._id
LEFT JOIN users as to_users ON rating.to_user_id = to_users._id

Actually you only need to set an alias for one of both occurrences of users, but setting an alias for both makes it easier to read and understand.

Note that you also need to prefix all columns that come from the users table in your projection and all other parts of the query (e.g. where, order by).

So given that you're interested in the user names of both users (the rating user and the rated user), your projection would look like this:

new String[]{"from_users.name", "to_users.name"};

Upvotes: 1

Related Questions