0

I'm working for a company on an Android app that's supposed to download their product catalogs using a REST API and store them on device for offline access.

I download all the data and store it in such tables:

Sellers: CREATE TABLE seller (_id INTEGER PRIMARY KEY NOT NULL, sellername TEXT, profileimglink TEXT, profileimgfilepath TEXT, profileimgfilesize INTEGER )

Items:CREATE TABLE item (_id INTEGER PRIMARY KEY NOT NULL, sellerid INTEGER, dateadded INTEGER, description TEXT, FOREIGN KEY (sellerid) REFERENCES seller (_id));

Photos of the items:CREATE TABLE photo (_id INTEGER PRIMARY KEY NOT NULL, itemid INTEGER, medialink TEXT, filepath TEXT, filesize TEXT, thumbnailpath TEXT, thumbnailsize TEXT, FOREIGN KEY (itemid) REFERENCES item (_id));

Now, I need to display the data in a ListView, so I'm implementing a custom cursor adapter and a custom cursor loader (based on https://stackoverflow.com/a/7422343/1723459).

Getting sellers and items together in Cursor loadInBackground() is fairly easy using this query SELECT item._id, item.dateadded, item.description, seller.sellername, seller.profileimgfilepath FROM item, seller WHERE seller._id = item.sellerid; but I need to somehow add thumbnails of photos, if there are any (can be any number between 0 and 10), from the "photo" table into the mix.

I thought about using a separate query (SELECT thumbnailpath FROM photos WHERE itemid=SomeIdThatIJustGotFromTheCursor;) in cursor adapter's void bindView(View view, Context context, Cursor cursor), but from what I've gathered, it runs on the UI thread and I'd rather not do any database operations there.

I've also thought about extending the query inside the Cursor loadInBackground() method, but I don't have much experience with SQL and I'm not quite sure how to do it, especially because it's possible to have no images related to the item. Actually, even if I knew how to do it, I'm certain it would generate a row for each image, with seller's and item's data repeating. Now, correct me if I'm wrong, but isn't void bindView(View view, Context context, Cursor cursor) called once per row in a cursor?

So, the question is: how (and where) should I get the data from these 3 tables, so that I could display it in a ListView.

Community
  • 1
  • 1
Manvis
  • 602
  • 1
  • 7
  • 13
  • 1
    you should use a join to get the three table results in just one query and don't use the first peace of code you find in stockoverflow. you have cursorLoader that make all for you only add a Join query. – Xenione Aug 23 '14 at 10:40

2 Answers2

1

I've managed to solve the problem using this query inside the cursor loader's Cursor loadInBackground():

SELECT item._id, item.description, item.dateadded, seller.sellername, 
seller.profileimgfilepath, group_concat(photo.thumbnailpath, ":") AS photopaths
FROM item
JOIN seller ON seller._id = item.sellerid
LEFT OUTER JOIN photo ON item._id=photo.itemid
GROUP BY item._id ORDER BY item.date DESC;

The LEFT OUTER JOIN leaves out empty cells if no photos related to the item exist, but if they do, a nifty little feature, called group_concat(columnname, "separator"), together with the GROUP BY clause fill the contents of the cell with a list of path strings separated by an indicated separator (I use the Android's File.pathSeparator).

Manvis
  • 602
  • 1
  • 7
  • 13
0

You should make your queries in the loadInBackground() using this class.And then return the cursor.

In your onLoadFinished you should start populating your listview

hoomi
  • 1,882
  • 2
  • 16
  • 17