How do I combine data from two separate tables into a single Cursor?

Question

Currently, I have a table of posts and a table of users. Naturally, each user can be associated with multiple posts. Each row in the post table stores the user ID of the user that created the post. Here are example rows:

Post row: post_id headline user_id

User row: user_id user_name

I want to return a Cursor containing both the post row and its corresponding user row by matching the user ID in the post table to the user ID in the user table. What type of query would I use to achieve this? The result should be:

Combined row: post_id headline user_id user_name

More generally: How do I combine data from two separate tables based on a shared piece of data into a single Cursor?

ErikR · Answer 1 · 2013-07-24T20:22:29.243

10

You can use a CursorJoiner to get something similar to merging two Cursors into one. The CursorJoiner doesn't actually perform a merge. As you iterate over it, it moves the original two Cursors such that their rows will match up on the specified column(s). This is why it's necessary that both Cursors be sorted on the columns that are to be used in the join.

Link to documentation: http://developer.android.com/reference/android/database/CursorJoiner.html

Code example:

CursorJoiner joiner = new CursorJoiner(userCursor, new String[]{ "user_id" }, postCursor, new String[] {"user_id"});

while (joiner.hasNext()) {
    CursorJoiner.Result result = joiner.next();
        switch (result) {
            case LEFT:
                // don't care about this case
                break;

            case RIGHT:
                // nor this case
                break;

            case BOTH:
                // here both original Cursors are pointing at rows that have the same user_id, so we can extract values
                int postId = postCursor.getInt(...);
                String headline = postCursor.getString(...);
                int userId = userCursor.getInt(...);        
                String userName = userCursor.getString(...);

                // do something with above values

                break;

        }
}

edited Jul 24 '13 at 20:22

answered Jul 24 '13 at 19:33

ErikR

1,052
6
12

One user corresponds to multiple posts, though, so would sorting on the ID still be acceptable, or would things get out of order? – cheese1756 Jul 24 '13 at 19:50
2

Yes, this is fine. I updated my answer with an example. If you have direct access to both database tables in your app, you're probably better off using TronicZomB's answer. CursorJoiner is best for cases where you only have access to Cursors and not their underlying data sources (e.g. Cursors obtained from ContentProviders of different apps) – ErikR Jul 24 '13 at 20:29
Thank you for the example. I do have direct access to the database so I used TronicZomB's answer, but yours is great for future reference (or for someone else with a similar problem). – cheese1756 Jul 24 '13 at 20:46
@ErikR +1 for "CursorJoiner is best for cases where you only have access to Cursors and not their underlying data sources (e.g. Cursors obtained from ContentProviders of different apps)" – me_ Jun 23 '18 at 20:55

score 8 · Accepted Answer · answered Jul 24 '13 at 19:41

8

You can also use raw SQLite statements in your Android code like such:

SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery("SELECT serials.id, serials.cabinet_number, serials.serial_number, " +
            "configuration.frequency, configuration.ag, configuration.number_alarms, configuration.failed_rapper, configuration.max_mv, configuration.max_nav " +
            "FROM serials JOIN configuration ON serials.id = configuration.serial_id WHERE serials.id = '" + current_id + "'", null);
    cursor.moveToFirst();

In the SELECT the format is table_name.column_name. The ON is where you would combine the data based on a shared piece of data.

answered Jul 24 '13 at 19:41

TronicZomB

8,667
7
35
50

Is it possible for me to join them without specifying a current_id? I would want to combine all rows in the post table with the user table, not just one ID. – cheese1756 Jul 24 '13 at 19:47
1

Yea you do not need a WHERE clause, I just copied this from some of my code for an example. – TronicZomB Jul 24 '13 at 19:48
The query works very well, but it appears to be duplicating entries for some rows. Do you know of a way to fix this? Here is my query: `SELECT post._id, post.headline, post.url, post.user_id, post.expiration, post.timestamp, user._id, user.name, user.about, user.email, user.phone FROM post JOIN user ON post.user_id = user._id` Thank you for your help! – cheese1756 Jul 24 '13 at 20:40
That looks good to me... Are you sure there aren't duplicate entries in the database? – TronicZomB Jul 24 '13 at 20:41
1

I checked that there aren't, but switching from SELECT to SELECT DISTINCT solved my problem regardless. Thanks again! – cheese1756 Jul 24 '13 at 20:43

How do I combine data from two separate tables into a single Cursor?

2 Answers2

Linked