mardi 23 février 2016

android sqlite...Get count in many-many relationship

I'm trying to achieve a particular data structure in my android app. A many to many relationship in sqlite.

In my app an Album is a collection of Images (basically a named grouping, Like a sort of tag) which means an Image can be part of different Albums and hence the many to many relationship.

My representation however is:

-- Album table
_id | name
-------------
1   | Ankara
2   | Laces
3   | Scarfs
4   | Agbada

-- Image table
_id | name      | uri
-----------------------
1   |           | uri1
2   | name2     | uri2
3   |           | uri3
4   |           | uri4
5   |           | uri5
6   |           | uri6
7   |           | uri7
8   |           | uri8
9   | imagename | uri9

-- AlbumImage table
-- This table contains the relationship between albums and images
_id | album_id | image_id
-----------------------
1   | 1        | 1
2   | 2        | 2
3   | 2        | 3
4   | 2        | 4
5   | 1        | 4
6   | 3        | 4
7   | 3        | 6
8   | 1        | 9
9   | 1        | 8

I want to query for album data and append a column with the number of images each one has. So the required result will look like:

-- Desired result after query
_id | name   | num_photos
--------------------
1   | Ankara | 4
2   | Laces  | 3
3   | Scarfs | 2
4   | Agbada | 0

This is my current SQL query which is INCORRECT (note the commented out part)

SELECT
    _id,
    name,
    (SELECT
--1     Albums.name  -- When using only subquery uncomment to add albumName column
        COUNT(*)
        FROM 
            Albums,
            AlbumImages,
            Images
        WHERE
--2         Albums._id = 2 AND
            album_id = Albums._id AND
            image_id = Images._id

    ) AS image_count
FROM 
    Albums 
--3WHERE
--4 _id = 2;

Which gives me something like:

_id | name   | num_photos
--------------------
1   | Ankara | 9
2   | Laces  | 9
3   | Scarfs | 9
4   | Agbada | 9

When querying for a single item (uncomment 2, 3 and 4) the mainquery-subquery combo works perfectly.

When querying for a single item with ONLY subquery (uncomment 1 and 2 only, run subquery) it works for album entries that have relationships in the AlbumImages table (id 1, 2 and 3 like

1 | Ankara | 4

)but returns a null Album.name for id 4 which does not have a relationship like

| NULL | 0

  1. I'm not exactly sure how to phrase the entire SQLite statement to give me the desired results. I am trying my possible best to use only a single query statement as the data is meant to be used by an adapter to populate a recyclerview.

  2. If there are easier ways of doing this (easier relationship, easier queries), please let me know.

Aucun commentaire:

Enregistrer un commentaire