[prev in list] [next in list] [prev in thread] [next in thread] 

List:       kde-bugs-dist
Subject:    [Bug 96388] show number of images in the album
From:       chiefaua <tom_t () gmx ! at>
Date:       2007-12-10 18:01:05
Message-ID: 20071210180105.30112.qmail () ktown ! kde ! org
[Download RAW message or body]

------- You are receiving this mail because: -------
You are on the CC list for the bug, or are watching someone who is.
         
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


[prev in list] [next in list] [prev in thread] [next in thread]