Easily searching across an application’s data is a pervasive need. If you are lucky, you can get away with simple sorting or searching on a single column, but it is more likely that you need full text search across multiple models, all from a single search field.
Search has been a problem for content related systems. But nowadays, we have many solutions for this. In Sociozat project, for search, i implemented full text search for polymorphic relationship documents.

Gorm is fantastic ORM library for Golang. It supports postgres, mysql and sqlite. The solution for getting results polymorphic relationship is simple with rdbms. Lets Get Started …
SQL Changes
We need different where statements for different kind of rdbms, and also we will look for different types of documents (ie topic, user, channel). To do that, we need to construct a database view which presents a polymorphic relationship to the individual result and the text column being searched.
Lets create a view table first.
CREATE VIEW searches AS
SELECT
'topic' AS type,
topics.name as title,
topics.slug
FROM topics UNION SELECT
'user' as type,
users.username as title,
users.slug
FROM users UNION SELECT
'channel' as type,
channels.name as title,
channels.slug
FROM channels
;
We can use this sql for both postgres and mysql. Unfortunately, creating view table is not enough. We also need full-text indexes.
Postgres
CREATE INDEX index_channels_on_name ON channels USING gin(to_tsvector('simple', name));
CREATE INDEX index_topics_on_name ON topics USING gin(to_tsvector('simple', name));
CREATE INDEX index_users_on_name ON users USING gin(to_tsvector('simple', username));From here, we add ginindices to the columns on which we are searching. In our case similar indices to these made the difference between a 3–5 second lookup and ~100ms.
P.S. you can also use
gisttype, consider there are some performance differences. it depends on your usage. More details are in documentation.
Mysql
ALTER TABLE `channels` ADD FULLTEXT INDEX `index_channels_on_name` (`name`);
ALTER TABLE `topics` ADD FULLTEXT INDEX `index_topics_on_name` (`name`);
ALTER TABLE `users` ADD FULLTEXT INDEX `index_users_on_name` (`username`);Implementing in Go.
We need a gorm model to search in our searches view table. Lets create a gorm model.
type SiteSearchResult struct {
Type string `json:"type"`
Slug string `json:"slug"`
Title string `json:"title"`
}
func (s SiteSearchResult) TableName() string {
return "searches"
}now we can do search. this is for mysql where clause for full text search.
results := []models.SiteSearchResult{}//for mysql
if err := db.Table("searches").Where("WHERE MATCH (title) AGAINST (? IN BOOLEAN MODE)", term).Find(&results).Error; err != nil {
return results
}//for postgres
if err := db.Table("searches").Where("title @@ to_tsquery(?)", term).Find(&results).Error; err != nil{
return results
}//for sqlite
if err := db.Table("searches").Where("title MATCH ?", term).Find(&results).Error; err != nil{
return results
}
In Sociozat, I used an adapter pattern to support all rdbms. You can use for your database.
Additional
UNION might not be a good approach if you have too much search request.
In this case you should switch to elasticsearch or solr or similar soulutions.
Conclusion
For polymorphic relationship documents, creating a view table supported with full-text indexes might be a good approach if you dont have too many requests or you have limited resources.
For single search query this might be the easiest solution for multi table searches.
