CODEWITHSUNDEEP
androidsql
Manvis
Manvis

Reputation: 632

Combining data from three tables for display in a ListView

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.

Upvotes: 0

Views: 783

Answers (2)

Manvis
Manvis

Reputation: 632

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).

Upvotes: 1

hoomi
hoomi

Reputation: 1912

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

Upvotes: 0

Related Questions