Languages
[Edit]
EN

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

5 points
Created by:
Marcin
2462

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:

Search by one hashtag with MySQL example
Search by one hashtag with MySQL example

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:

Search by two hashtags with MySQL example
Search by two hashtags with MySQL example

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:

Search by three hashtags with MySQL example
Search by three hashtags with MySQL example

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);

 

Hey ūüĎč
Would you like to know what we do?
  • Dirask is a friendly IT community for learners, professionals and hobbyists to share their knowledge and help each other in extraordinary easy way.
  • We welcome everyone,
    no matter what the experience,
    no matter how basic the question is,
    this community will help you.