There are times when you'll need to perform custom and complex queries in your rails application. Performing queries on a table column can be straight forward. With the new model that rails 6 provides, ActionText; not quite so.

I'm reviving my side project, Zired, that uses custom queries to query jobs by its job title or by the content of the body. The job description column uses the Trix editor where the data is saved as HTML which uses the has_rich_text class method – a separate model called action_text_rich_texts. Feel free to explore the ActionText module which Rails 6 now includes by default.

To query jobs on Zired, I needed to join two tables:

  1. Company - You must have a company before making job posts
  2. Description - The Trix's Rich Text model

I've added a special query where you can query everything that makes up a job. There are a lot more but for now, I've only included company and description. When you search for, say, "Monzo", you should see all current job posts for that company, Monzo. If the job description has key words: "skills required Go Lang, Rails, etc", then searching for "rails" should return all jobs with "rails" in the HTML description. This now calls for custom queries.

Zired's job model

# job.rb
belongs_to :company, dependent: :destroy, required: false

has_rich_text :description

Say we need to query the description column*:

Job.where("description ILIKE ?", "%rails%")

# Will produce an error message =>
ActiveRecord::StatementInvalid (PG::UndefinedColumn: ERROR:  column "description" does not exist)

My database of choice is PostgreSQL. If you are not familiar PostgreSQL, ILIKE is like LIKE but this disregards case-sensitivity.

*As you know, we do not have a column called description. The has_rich_text created an ActionText column that is separated from our Job model. In Rails, you can join models.

# job.rb

def self.query
  .joins(:description)
end

# Will produce an error:
ActiveRecord::ConfigurationError (Can't join 'Job' to association named 'description'; perhaps you misspelled it?)

No, nothing was misspelled, there is simply no association between description and job.

Looking on the migration file for action text, we can see a table called action_text_rich_texts. We are now getting warmer:

.joins(:action_text_rich_text)

# Another error 😡 🤬

Inner Join to the rescue

.joins("INNER JOIN action_text_rich_texts ON action_text_rich_texts.record_id = jobs.id").where("action_text_rich_texts.body ILIKE ?", "%rails%")

How did I came up with this query? Looking back on the class ActionText::RichText, we can see all associated models so ActionText::RichText.first.record will give us the first associated job as we only have one has_rich_text in our model, job. Setting more has_rich_text in other models, will display others. A record is the associated model you set your has_rich_text on. That inner join will display duplicate results so you may want to append .uniq at the end:

def self.query(query)
  .joins("INNER JOIN action_text_rich_texts ON action_text_rich_texts.record_id = jobs.id").where("action_text_rich_texts.body ILIKE ?", "%#{query}%").uniq
end

# Then
Job.query('rails')

To join more than one models:

.joins("JOIN companies ON companies.id = jobs.company_id INNER JOIN action_text_rich_texts ON action_text_rich_texts.record_id = jobs.id")

Zired uses pretty much everything here. You should have a read and implement your very own custom queries.

I hope this was useful to you. ✌🏼