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
-
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.
-
If there are easier ways of doing this (easier relationship, easier queries), please let me know.
Aucun commentaire:
Enregistrer un commentaire