[Digikam-devel] [Bug 96388] show number of images in the album

[chiefaua]

------- You are receiving this mail because: -------
You are the assignee for the bug, or are watching the assignee.
         
http://bugs.kde.org/show_bug.cgi?id=96388         




------- Additional Comments From tom_t gmx at  2007-12-10 19:01 -------
Hello

I have just read your comment, and it looks like you are looking for a join in your code.

Try this:

SELECT ImageTags.tagid, Images.name FROM ImageTags, Images WHERE ImageTags.imageid = Images.id; 

This gives you a list of tagid/name pairs of all images which have tags. If you want the list sorted (by tagid), try

SELECT ImageTags.tagid, Images.name FROM ImageTags, Images WHERE ImageTags.imageid = Images.id ORDER BY ImageTags.tagid; 

To get a list of all tags and the number of associated images, try

SELECT ImageTags.tagid, COUNT(Images.id) FROM ImageTags, Images WHERE ImageTags.imageid = Images.id GROUP BY ImageTags.tagid;

As you have written, that you need to check against the filename, this could be done in this way (if I have understood you right):

SELECT ImageTags.tagid, COUNT(Images.id) FROM ImageTags, Images WHERE ImageTags.imageid = Images.id AND
(Images.name LIKE '%.jpg' OR
Images.name LIKE '%.bmp')
GROUP BY ImageTags.tagid;     

(add OR clauses as you need them for the mime-type, but I am not sure if this approach is more performant than your's with Regex and QMap)

again, to get a sorted list, try:

SELECT ImageTags.tagid, COUNT(Images.id) FROM ImageTags, Images WHERE ImageTags.imageid = Images.id AND
(Images.name LIKE '%.jpg' OR
Images.name LIKE '%.bmp')
GROUP BY ImageTags.tagid
ORDER BY ImageTags.tagid;

I don't know if you also want tagid's with no images in the list. These querries only return tagid's which have at least one image assigned to it, but it would be possible to return a list of all tagid's, even those with no images.

I tried these querries against my own digikam3.db file, they all work for me.

I am not a big SQL guru neither, but maybe I could help.

Thomas