如何将两个独立表中的数据合并到一个游标中?

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表中的每一行存储创建post的用户的用户ID。下面是示例行:

Post row: post_id headline user_id

Post行:post_id标题user_id

User row: user_id user_name

用户行: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:

通过将post表中的用户ID与user表中的用户ID匹配，我希望返回一个包含post行及其对应的用户行的游标。我将使用什么类型的查询来实现这一点?结果应该是:

Combined row: post_id headline user_id user_name

合并行:post_id标题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?

更一般地说:如何将基于共享数据块的两个独立表中的数据合并到一个游标中?

2 个解决方案

#1

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

您还可以在Android代码中使用原始的SQLite语句:

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.

在SELECT中，格式是table_name.column_name。ON是根据共享数据块组合数据的地方。

#2

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.

您可以使用一个CursorJoiner来获得类似于将两个游标合并为一个的方法。CursorJoiner实际上并不执行合并。当您对它进行迭代时，它将移动原始的两个游标，以便它们的行在指定的列上匹配。这就是为什么必须对要在连接中使用的列对这两个游标进行排序。

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

链接到文档: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;

        }
}

#1