Top community members
All Wiki Articles Create Wiki Article

Many years me and my friends we were volunteers on my university doing programming classes for young people.

I got main conclusion: Young people are awesome, especially students, because they always bring new and fresh ideas :)
* I loved to do it but my time is limited

  I decided to create something that will help millions of students

Now I am here

co-founder

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

0 contributions
5 points

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

 

0 contributions

Checkout latest Findings & News:

Checkout latest questions:

Checkout latest wiki articles:

Hey ūüĎč
Would you like to know what we do?
  • Dirask is IT community, where we share coding knowledge and help each other to solve coding problems.
  • We welcome everyone,
    no matter what the experience,
    no matter how basic the question is,
    this community will help you.
Read more