You could use the group_concat aggregation function to concatenate the
category titles. That way you'll only have a single row per post.
Another alternative is to simply not use CursorAdapter but write your
own work-alike instead, possibly starting from the CursorAdapter source
code.
Pepijn
On 10/06/2011 09:24, Emanuele Ricci wrote:
Maybe you don't understand. I'll make an example
TABLE POSTS:
POST_ID | POST_TITLE
1 | My first post
2 | My second post! Yay!
TABLE CATEGORIES
CAT_ID | CATEGORY_TITLE
1 | General
2 | Blog
3 | Sport
4 | Comic
TABLE POSTS_CATEGORIES
POST_ID | CATEGORY_ID
1 | 1
1 | 2
1 | 3
2 | 2
2 | 4
What does it mean? That "My first post" have 3 categories: General,
Blog, Sport and "My second post! Yay!" have 2 categories: Sport and Comic
So with this query (where I want to load ALL POST with their categories):
SELECT posts.post_id, posts.post_title, categories.category_name FROM
posts LEFT OUTER JOIN posts_categories ON
posts_categories.post_id=posts.post_id LEFT OUTER JOIN categories ON
posts_categories.category_id=categories.category_id;
I'll get this results:
1 | My first post | General
1 | My first post | Blog
1 | My first post | Sport
2 | My second post! Yay! | Blog
2 | My second post! Yay! | Comic
And I think this is the only efficent way to load all my data using
only 1 query to optimize it.
But: how can I handle all data in my ListFragment with my own Adapter
that extend CursorAdapter?
I cannot display all 5 post because as you can see we have repetitions.
On Fri, Jun 10, 2011 at 2:15 AM, Zsolt Vasvari <[email protected]
<mailto:[email protected]>> wrote:
Use a WHERE clause so you only get 2 rows returned.
On Jun 10, 6:17 am, Emanuele Ricci <[email protected]
<mailto:[email protected]>> wrote:
> Good evening ( for me ), I'm Emanuele a young android developer.
> I want to share with you my problem because I'm not finding a
good solution.
>
> I've 3 tables (in reality 5)
>
> posts
> categories
> posts_categories
>
> to create the many-to-many relation between posts and categories
>
> I've created a ListFragment to show all my posts with their
categories.
>
> So I've created this query ( I don't write all the code [image:
:)] )
>
> SELECT posts._id, posts.post_title, categories.category_name
FROM posts LEFT
> OUTER JOIN posts_categories ON
posts_categories.post_id=posts.post_id LEFT
> OUTER JOIN categories ON
> posts_categories.category_id=categories.category_id;
>
> So if I have 2 post with 2 category each that query will return
4 records.
> Am I wrong?
> So in my Cursor I have 4 record but what I really need is to
collect datas
> from the cursor and display only what I really need.
>
> In this case I cannot use a CursorAdapter to display my data
because it will
> insert 4 item in the ListFragment.
>
> And I don't want to load only post and than for each post load
categories
> because if I have 100 posts I will do 1 query to select all
posts and 100
> query to select categories.
>
> What can I do? I need an expert advice! Which is the best way to
handle this
> situation?
> Have you ever faced this problem?
>
> Thank you very much. Emanuele Ricci.
--
You received this message because you are subscribed to the Google
Groups "Android Developers" group.
To post to this group, send email to
[email protected]
<mailto:[email protected]>
To unsubscribe from this group, send email to
[email protected]
<mailto:android-developers%[email protected]>
For more options, visit this group at
http://groups.google.com/group/android-developers?hl=en
--
You received this message because you are subscribed to the Google
Groups "Android Developers" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/android-developers?hl=en
--
You received this message because you are subscribed to the Google
Groups "Android Developers" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/android-developers?hl=en
