What tips do you have for running complex SQLite queries to find files based on tags?

Question

What tips do you have for running complex SQLite queries to find files based on tags?

What Is My Current Project?

Currently, I am working on developing a file tagging program using JavaScript with Electron. I have decided to incorporate SQLite into the project for data management. However, I am facing a challenge when it comes to implementing tag-based searches. Since SQL and SQLite are relatively new to me, I am unsure if it is feasible to achieve this solely through queries. Can anyone provide guidance on how I can execute searches based on tags as outlined below?

Specifics of Searching:

After exploring FTS3/4 options, I have discovered that while they meet my requirements, wildcard searches seem to be unsupported.

Find files with all specified tags: blue_sky AND green_grass
Discover files without specific tags: NOT blue_sky AND NOT green_grass
Detect files with any of the given tags: green_sky OR blue_sky
Look for files with wildcards anywhere in a tag: *sky AND *grass AND *bl*e*
Combine the above search criteria: blue_sky AND green* /
```
green_grass AND blue_sky OR green_sky
```

Database Schema:

The structure may undergo modifications

CREATE TABLE files (
  id INTEGER PRIMARY KEY,
  name TEXT
);

CREATE TABLE tags (
  id INTEGER PRIMARY KEY,
  name TEXT
);

CREATE TABLE file_tags (
  id INTEGER PRIMARY KEY,
  file_id INTEGER,
  tag_id INTEGER
);

Examples:

INSERT INTO files (name) VALUES ('file_1.png');
INSERT INTO files (name) VALUES ('file_2.png');
INSERT INTO files (name) VALUES ('file_3.png');
INSERT INTO files (name) VALUES ('file_4.png');

INSERT INTO tags (name) VALUES ('blue_sky');
INSERT INTO tags (name) VALUES ('green_sky');
INSERT INTO tags (name) VALUES ('green_grass');
INSERT INTO tags (name) VALUES ('blue_grass');
INSERT INTO tags (name) VALUES ('greenish_blue_sky');


INSERT INTO file_tags (file_id, tag_id) VALUES(file1_id, blue_sky_id);
INSERT INTO file_tags (file_id, tag_id) VALUES(file1_id, green_grass_id);

INSERT INTO file_tags (file_id, tag_id) VALUES(file2_id, blue_sky_id);
INSERT INTO file_tags (file_id, tag_id) VALUES(file2_id, blue_grass_id);

INSERT INTO file_tags (file_id, tag_id) VALUES(file3_id, greenish_blue_sky_id);

INSERT INTO file_tags (file_id, tag_id) VALUES(file4_id, green_sky_id);
INSERT INTO file_tags (file_id, tag_id) VALUES(file4_id, blue_grass_id);

Query: blue_sky and green_grass
result: file_1

Query: blue_sky or green_sky
result: file_1, file_2, file_4

Query:

blue_sky and green_grass or blue_grass

result: file_1, file_2

Query: *ish*
result: file_3

Query: *bl*e*
result: file_1, file_2, file_3, file_4

Query: *sky and not blue_grass
result: file_1, file3

Note: If SQLite does not appear suitable for solving this problem, I am open to alternative suggestions.

javascript sql database sqlite tagging

Answer 1

Answer №1

From my perspective, enhancing the database structure could simplify this process.
For example:

Consistently use 'file_id' and 'tag_id' instead of switching to 'id'
Implement foreign keys (although this feature may not be available)

You could potentially utilize tag_ids as input in initial cases, depending on the source of the keys (an "unprepared user" might naturally type colors). This approach can help minimize typos.

One strategy you could adopt includes:

Utilize joins from the 'file_tags' table, one for each tag needed in the logic
Merge the files table to access file names for output
Incorporate subqueries to use tag names rather than tag ids or increase join operations, as exemplified below
Directly transfer the search logic into the 'where' clause
Group by file names to ensure each file appears only once

Based on your detailed MCVE, here are proposed queries for your specified examples:

select fs.name from file_tags t1 
         inner join file_tags t2 on t1.file_id = t2.file_id
         inner join files fs on fs.id = t1.file_id
where t1.tag_id = (select id from tags where name = 'blue_sky')
  and t2.tag_id = (select id from tags where name = 'green_grass')
group by fs.name;

select fs.name from file_tags t1
         inner join file_tags t2 on t1.file_id = t2.file_id
         inner join files fs on fs.id = t1.file_id
where t1.tag_id = (select id from tags where name = 'blue_sky')
   or t2.tag_id = (select id from tags where name = 'green_sky')
group by fs.name;

-- note, here I had to derive from your desired output
-- that you want a '()' around the 'or'
select fs.name from file_tags t1
         inner join file_tags t2 on t1.file_id = t2.file_id
         inner join file_tags t3 on t1.file_id = t3.file_id
         inner join files fs on fs.id = t1.file_id
where t1.tag_id = (select id from tags where name = 'blue_sky')
 and (t2.tag_id = (select id from tags where name = 'green_grass')
   or t3.tag_id = (select id from tags where name = 'blue_grass')
     )
group by fs.name;

select fs.name from file_tags t1 
         inner join files fs on fs.id = t1.file_id
         inner join tags ts on ts.id = t1.tag_id
where ts.name like '%ish%'
group by fs.name;

select fs.name from file_tags t1 
         inner join files fs on fs.id = t1.file_id
         inner join tags ts on ts.id = t1.tag_id
where ts.name like '%bl%e%'
group by fs.name;

select fs.name from file_tags t1 
         inner join files fs on fs.id = t1.file_id
         inner join tags ts on ts.id = t1.tag_id
where ts.name like '%sky' and not ts.name = 'blue_grass'
group by fs.name;

select name from file_tags t1
         inner join files fs on t1.file_id = fs.id
where (select name from tags where id = t1.tag_id) like "%sky"
and not file_id in
        (select file_id from file_tags 
         where tag_id = (select id from tags where name = 'blue_grass')
        );

Result:

name
----------
file_1.png
name
----------
file_1.png
file_2.png
file_4.png
name
----------
file_1.png
file_2.png
name
----------
file_3.png
name
----------
file_1.png
file_2.png
file_3.png
file_4.png
name
----------
file_1.png

If I include the following additions:

INSERT INTO tags (name) VALUES ('greenish_blue_sky');
INSERT INTO file_tags (file_id, tag_id) VALUES(file3_id, greenish_blue_sky_id);

The updated output would be:

name
----------
file_1.png
file_3.png

Tested with SQLite 3.18.0

Answer 2

From my perspective, enhancing the database structure could simplify this process.
For example:

Consistently use 'file_id' and 'tag_id' instead of switching to 'id'
Implement foreign keys (although this feature may not be available)

You could potentially utilize tag_ids as input in initial cases, depending on the source of the keys (an "unprepared user" might naturally type colors). This approach can help minimize typos.

One strategy you could adopt includes:

Utilize joins from the 'file_tags' table, one for each tag needed in the logic
Merge the files table to access file names for output
Incorporate subqueries to use tag names rather than tag ids or increase join operations, as exemplified below
Directly transfer the search logic into the 'where' clause
Group by file names to ensure each file appears only once

Based on your detailed MCVE, here are proposed queries for your specified examples:

select fs.name from file_tags t1 
         inner join file_tags t2 on t1.file_id = t2.file_id
         inner join files fs on fs.id = t1.file_id
where t1.tag_id = (select id from tags where name = 'blue_sky')
  and t2.tag_id = (select id from tags where name = 'green_grass')
group by fs.name;

select fs.name from file_tags t1
         inner join file_tags t2 on t1.file_id = t2.file_id
         inner join files fs on fs.id = t1.file_id
where t1.tag_id = (select id from tags where name = 'blue_sky')
   or t2.tag_id = (select id from tags where name = 'green_sky')
group by fs.name;

-- note, here I had to derive from your desired output
-- that you want a '()' around the 'or'
select fs.name from file_tags t1
         inner join file_tags t2 on t1.file_id = t2.file_id
         inner join file_tags t3 on t1.file_id = t3.file_id
         inner join files fs on fs.id = t1.file_id
where t1.tag_id = (select id from tags where name = 'blue_sky')
 and (t2.tag_id = (select id from tags where name = 'green_grass')
   or t3.tag_id = (select id from tags where name = 'blue_grass')
     )
group by fs.name;

select fs.name from file_tags t1 
         inner join files fs on fs.id = t1.file_id
         inner join tags ts on ts.id = t1.tag_id
where ts.name like '%ish%'
group by fs.name;

select fs.name from file_tags t1 
         inner join files fs on fs.id = t1.file_id
         inner join tags ts on ts.id = t1.tag_id
where ts.name like '%bl%e%'
group by fs.name;

select fs.name from file_tags t1 
         inner join files fs on fs.id = t1.file_id
         inner join tags ts on ts.id = t1.tag_id
where ts.name like '%sky' and not ts.name = 'blue_grass'
group by fs.name;

select name from file_tags t1
         inner join files fs on t1.file_id = fs.id
where (select name from tags where id = t1.tag_id) like "%sky"
and not file_id in
        (select file_id from file_tags 
         where tag_id = (select id from tags where name = 'blue_grass')
        );

Result:

name
----------
file_1.png
name
----------
file_1.png
file_2.png
file_4.png
name
----------
file_1.png
file_2.png
name
----------
file_3.png
name
----------
file_1.png
file_2.png
file_3.png
file_4.png
name
----------
file_1.png

If I include the following additions:

INSERT INTO tags (name) VALUES ('greenish_blue_sky');
INSERT INTO file_tags (file_id, tag_id) VALUES(file3_id, greenish_blue_sky_id);

The updated output would be:

name
----------
file_1.png
file_3.png

Tested with SQLite 3.18.0

What tips do you have for running complex SQLite queries to find files based on tags?

What Is My Current Project?

Specifics of Searching:

Database Schema:

Examples:

Answer №1

Similar questions

Encountering the error message "Unable to instantiate User: constructor not found" while testing API functionality for

Tips for validating an email address using ReactJS

Tips for creating text that adjusts to the size of a div element

Embracing Laravel for Enhanced Functionality on an Established Website

There was an issue converting the value {null} to the data type 'System.Int32', resulting in a 400 error

Firefox causing issues with Rails Ajax functionality

Is there a way to transform a Phaser 3 game into an android-game application?

Interrogate the documents, make revisions, and then send them back to MongoDB

The Heroku app seems to be malfunctioning (Issue with deployment?)

What is the most effective way to send an email in PHP with a table format

Vue's computed property failing to compute

Using jQuery to include the value of an array index in the output

JQuery for Seamless Zoom Functionality

Exploring table iteration in Angular 7

Automated system is responsible for flagging and disabling comments

Transform a string into a boolean value for a checkbox

Transferring Information from Angular Interface to NodeJS through a JSON Document

What is the best way to fix the lint check error within a Vue file's styling?

Shifting axios requests outside of the React Component

Using optional chaining along with the methods toLowerCase and indexOf while iterating through an array using the map