SQL - how to search by hashtags / tags in MySql / MariaDB

In this article, we're going to have a look at how to create SELECT SQL query that searches for rows by assigned tags or hashtags in MySQL or MariaDB. The article was splitted in to 2 seactions:

• first one describes how to make proper SELECT query,
• second one describes how to prepare database. 

1. Searching by tags / hashtags

Presented in this section solution uses aggreation function to check, there are some rows with related tags / hashtags. Aggreation functions are specific functions that allows to make special oprations on data and use results as conditions.

Most intuitive approach and that we can see in many popular web pages is to find only rows that have assigned all tags / hashtags about what we were looking for - presented below examples work this way but it is very easy to chage operators from AND to OR.

1.1. Single tag / hashtag criterion example

This example shows how to find rows that have assigned one tag / hashtag.

SELECT p.* 
FROM `posts` p
INNER JOIN `hashtags` t ON p.`id` = t.`post_id`
GROUP BY p.`id`
HAVING
	SUM(t.`name` = 'type');

Result:

1.1. Two tags / hashtags criterion example

This example shows how to find rows that have assigned two tags / hashtags.

SELECT p.* 
FROM `posts` p
INNER JOIN `hashtags` t ON p.`id` = t.`post_id`
GROUP BY p.`id`
HAVING
		SUM(t.`name` = 'array')
	AND
		SUM(t.`name` = 'type');

Result:

1.1. Three tags / hashtags criterion example

This example shows how to find rows that have assigned three tags / hashtags.

Note: to use more hashtags during making SELECT query use more AND SUM(...) conditions.

SELECT p.* 
FROM `posts` p
INNER JOIN `hashtags` t ON p.`id` = t.`post_id`
GROUP BY p.`id`
HAVING
		SUM(t.`name` = 'javascript')
	AND
		SUM(t.`name` = 'type')
	AND
		SUM(t.`name` = 'input');

Result:

2. Data base preparation

Execute following SQL codes to prepare database.

Tables creations code:

CREATE TABLE `posts` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(100) NOT NULL,
	`content` TEXT NOT NULL,
	PRIMARY KEY (`id`)
)
ENGINE=InnoDB;

CREATE TABLE `hashtags` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(30) NOT NULL,
	`post_id` INT(10) UNSIGNED NOT NULL,
	PRIMARY KEY (`id`),
	INDEX `name_key` (`name`),
	INDEX `post_id_key` (`post_id`),
	CONSTRAINT `FK_hashtags_posts` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`)
)
ENGINE=InnoDB;

Example data insertion code:

INSERT INTO `posts`
	(`id`, `name`, `content`)
VALUES
	/* #programming #javascript #js #jquery #input #value */
	(1, 'jQuery - how to get value of input field?', 'Post content...'),
	
	/* #programming #javascript #js #input #form */
	(2, 'JavaScript - how to access input element?', 'Post content...'),
	
	/* #programming #javascript #js #type #typeof */
	(3, 'JavaScript - how to check type of object?', 'Post content...'),
	
	/* #programming #javascript #js #array #type */
	(4, 'JavaScript - is variable array type?', 'Post content...');
	
INSERT INTO `hashtags`
	(`name`, `post_id`)
VALUES
	/* jQuery - how to get value of input field? */
	('programming', 1), ('javascript', 1), ('js', 1), 
	('jquery', 1), ('input', 1), ('value', 1),
	
	/* JavaScript - how to access input element? */
	('programming', 2), ('javascript', 2), ('js', 2), 
	('input', 2), ('form', 2),
	
	/* JavaScript - how to check type of object? */
	('programming', 3), ('javascript', 3), ('js', 3), 
	('type', 3), ('typeof', 3), 
	
	/* JavaScript - is variable array type? */
	('programming', 4), ('javascript', 4), ('js', 4), 
	('type', 4), ('array', 4);