Languages
[Edit]
EN

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

5 points
Created by:
Marcin
15740

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

 

Native Advertising
50 000 ad impressions - 449$
ūüöÄ
Get your tech brand or product in front of software developers.
For more information contact us:
Red dot
Dirask - friendly IT community for everyone.

‚̧ԳŹūüíĽ ūüôā

Join