How to show the related (suggested) keywords

performancesqlite

We do a program for cataloging images by assigning keywords (tags) to them.

An image has the following keywords "John", "Jane", "Portrait".

Another image has the following keywords "John", "Jane", "Wedding".

When the user tries to catalog the next image and adds the "John" keyword, we want to appear in the 'Suggested' area the keyword "Jane".

How can we extract (using SQL) the suggested keywords based on the most possible combinations?

Note that Images can have more keywords assigned and in 'Suggested' area is best to have more suggestions.

The relevant table is called TagsTree and it has the following creation DDL:

CREATE TABLE TagsTree(ImageID INTEGER NOT NULL, TagID INTEGER NOT NULL, UNIQUE (ImageID, TagID))

…where ImageID is FK to the photo entity and TagID is FK reference to the keyword entity.

Also, any other hints regarding schema etc. are greatly appreciated.

PS: The DB engine is SQLite3, if it matters.

Best Answer

I am assuming that your tags are stored in the "Tag" table, but if not it should be an easy fix for the below SQL. I have not used SQLite3 before, but this should be generic SQL. I used the literal "John," but just replace with a variable for whatever the user enters. Also, if you wanted to limit the count to at least x number then put in a having clause.

select t2.keyword
from TagsTree tt
join Tag t on tt.TagID = t.TagID
join TagsTree tt2 on tt.ImageID = tt2.ImageID
join Tag t2 on tt2.TagID = t2.TagID and t.TagID <> t2.TagID
where t.keyword = 'John'
group by t2.keyword
order by count(*) desc, t2.keyword
Related Question