CODEWITHSUNDEEP
androidsqlitecursor
ashays
ashays

Reputation: 1154

Many-to-Many Query in One Cursor Without Repeating Data

So, I'm going to say that I have three tables as follows:

       POSTS             POSTS_TAGS           TAGS
+-----+-----------+ +---------+--------+ +-----+-------+
| _id |   title   | | post_id | tag_id | | _id | title | 
+-----+-----------+ +---------+--------+ +-----+-------+
|  0  |    foo    | |    0    |    1   | |  0  |  baz  |
+-----+-----------+ +---------+--------+ +-----+-------+
|  1  |    bar    | |    0    |    2   | |  1  | quux  |
+-----+-----------+ +---------+--------+ +-----+-------+
                    |    1    |    0   | |  2  | corge |
                    +---------+--------+ +-----+-------+
                    |    1    |    2   |
                    +---------+--------+

Is there any way to formulate a query with SQLite such that I could then have a cursor with the following data in it:

row1 = <foo, <quux, corge>>
row2 = <bar, <baz, corge>>

As opposed to:

row1 = <foo, quux>
row2 = <foo, corge>
row3 = <bar, baz>
row4 = <bar, corge>

However, I severely doubt that there is anything that will give me precisely that, so I guess my real question is, what is the best way to formulate a query such as this, so that I can pass it back to my activity, and it would be able to return all this data to the activity? Or am I really going to need to iterate through my cursor again afterwards to "pick up" all the extra data and reorganize it myself.

Upvotes: 1

Views: 1004

Answers (1)

StErMi
StErMi

Reputation: 5469

Got the solution:

SELECT posts._id, posts.title, GROUP_CONCAT(tags._id) AS tags_id, GROUP_CONCAT(tags.tag_name) AS tags_name FROM posts 
LEFT OUTER JOIN posts_tags ON posts_tags.post_id=posts.post_id
LEFT OUTER JOIN tags ON posts_tags.tag_id=tags.tag_id
GROUP BY posts._id;

Please adapt the query to your problem :)

You can find SQLite documentation about concat(x) and concat(x,separator) here.

Anyway you can also follow my problem on google group.

Upvotes: 2

Related Questions