How to make my DB query method in Rails more efficient

Question

I am doing a query over my POSTGRESQL DB. My app has Articles and the Articles can have a number of Hashtags. Those relations are saved in a joined table of Hashtags and Articles.

I have a working method which gives me back Articles which have certain hashtags, or gives me back all articles who do not contain certain hashtags

  def test(hashtags, include = true)
    articles= []
    hashtags.split(' ').each do |h|
      articles+= Article.joins(:hashtags).where('LOWER(hashtags.value) LIKE LOWER(?)', "#{h}")
    end
    if include
      articles.uniq
    else 
      (Article.all.to_set - articles.uniq.to_set).to_a
    end
  end

I could call it like this:

test("politics people china", true)

And it would give me all Articles who have one of those hashtags related to

Or I could call it like that

test("politics people china", false)

And it would give me all Articles EXCEPT those who have one of these hashtags

It works well, but I dont think this is very efficient as I do so much in Ruby and not on DB level.

I tried this:

def test2(hashtags, include = true)
    articles= []
    pattern = ''
    hashtags.split(' ').each do |h|
      pattern += "#{h}|"
    end
    pattern = '(' + pattern[0...-1] + ')'

    if include
      articles = Article.joins(:hashtags).where('hashtags.value ~* ?', "#{pattern}")
    else 
      articles = Article.joins(:hashtags).where('hashtags.value !~* ?', "#{pattern}")
    end

    articles.uniq
  end

But it does not behave like I thought it would. First of all if I call it like that:

test2("politics china", true)

It wouldn't only give me all Articles who have a hashtags politics or china, but also all artcles who have a hashtag containing one of the letters in politics or china like so:

(p|o|l|i|t|c|s|h|n|a)

but it should check for this actually, and the pattern looks actually like this, what I can see in the console:

(politics|china)

which it doesnt what I find is strange tbh...

And with

test2("politics", false)

It only gives me articles who have one or more hashtags associated to, BUT leaves out the ones who have no hashtag at all

Can someone help me make my working method more efficient?

EDIT: Here is my updated code like suggested in an answer

def test2(hashtags, include = false)    
    hashtags = 
    if include 
      Hashtag.where("LOWER(value) iLIKE ANY ( array[?] )", hashtags)
    else
      Hashtag.where("LOWER(value) NOT iLIKE ANY ( array[?] )", hashtags)
    end
    Slot.joins(:hashtags).merge(hashtags).distinct
  end

It still lacks to give me Articles who have NO hashtags at all if incude is false unfortunately

Ilya Konyukhov · Accepted Answer · 2018-09-28 14:43:58Z

2

You are right about

I dont think this is very efficient as I do so much in Ruby and not on DB level.

ActiveRecord works nice for simple queries, but when things are getting complex it's reasonable to use plain SQL. So let's try to build a query that matches your test cases:

1) For this call test("politics people china", true) the query may look like:

SELECT DISTINCT ON (AR.id) AR.*
FROM articles AR
  JOIN articles_hashtags AHSH ON AHSH.article_id = AR.id
  JOIN hashtags HSH ON HSH.id = AHSH.hashtag_id
WHERE LOWER(HSH.value) IN ('politics', 'people', 'china')
ORDER BY AR.id;

(I'm not sure how your join table is named, so assuming it is articles_hashtags).

Plain and simple: we take data from articles table using 2 inner joins with articles_hashtags and hashtags and where conditions, which filters hashtags we want to see; and eventually it brings us all articles with that hashtags. No matter on how many hashtags we want to filter: IN statement works well even if there is only one hashtag in the list.

Please note DISTINCT ON: it's necessary for removing duplicate articles from resultset, in case the same article has more than one hashtag from given hashtag list.

2) For the call test("politics people china", false) the query is a bit more complex. It needs to exclude articles which have given hashtags. Hence it should return articles with different hashtags, as well as articles without hashtags at all. Trying to keep things simple we could use the previous query for that:

SELECT A.*
FROM articles A
WHERE A.id NOT IN (
    SELECT DISTINCT ON (AR.id) AR.id
    FROM articles AR
      JOIN articles_hashtags AHSH ON AHSH.article_id = AR.id
      JOIN hashtags HSH ON HSH.id = AHSH.hashtag_id
    WHERE LOWER(HSH.value) IN ('politics', 'people', 'china')
    ORDER BY AR.id
);

Here we're fetching all articles, but those who have any of given hashtags.

3) Converting these queries to a Ruby method gives us the following:

def test3(hashtags, include = true)
  # code guard to prevent SQL-error when there are no hashtags given
  if hashtags.nil? || hashtags.strip.blank?
    return include ? [] : Article.all.to_a
  end

  basic_query = "
    SELECT DISTINCT ON (AR.id) AR.*
    FROM #{Article.table_name} AR
      JOIN articles_hashtags AHSH ON AHSH.article_id = AR.id
      JOIN #{Hashtag.table_name} HSH ON HSH.id = AHSH.hashtag_id
    WHERE LOWER(HSH.value) IN (:hashtags)
    ORDER BY AR.id"

  query = if include
            basic_query
          else
            "SELECT A.*
            FROM #{Article.table_name} A
            WHERE A.id NOT IN (#{basic_query.sub('AR.*', 'AR.id')})"
          end

  hashtag_arr = hashtags.split(' ').map(&:downcase) # to convert hashtags string into a list

  Article.find_by_sql [query, { hashtags: hashtag_arr }]
end

The method above will return an array of articles matching your conditions, empty or not.

edited Sep 28, 2018 at 14:43

answered Sep 28, 2018 at 13:58

Ilya Konyukhov

2,7911 gold badge15 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ilja KO Over a year ago

It works thank you. But isnt plain sql querying a bit dangerous? Also in WHERE A.id NOT IN (#{basic_query.sub('AR.*', 'AR.id')}) T" when include is false the T is misplaced, I had to remove it else I got an error...But thank you for this answer. I wish it wouldn't do plain raw SQL queries though but I will end up taking this if I dont find anything better as it leaves me vulnureable to SQL injections I think

Ilya Konyukhov Over a year ago

Raw SQL query is dangerous when user input is inserted into query without sanitizing. However in this example find_by_sql performs sanitizing, so it's all good.

Ilya Konyukhov Over a year ago

Oh, sorry about T : haven't tested the query in psql. Corrected my answer

Rodrigo · Accepted Answer · 2018-09-28 13:45:15Z

1

Try this:

def test(hashtags, include = true)
  hashtags = 
    if include 
      Hashtag.where("LOWER(value) iLIKE ANY ( array[?] )", hashtags)
    else
      Hashtag.where("LOWER(value) NOT iLIKE ANY ( array[?] )", hashtags)
    end
  Article.joins(:hashtags).merge(hashtags).distinct
end

edited Sep 28, 2018 at 13:45

answered Sep 28, 2018 at 13:31

Rodrigo

4,8125 gold badges44 silver badges78 bronze badges

4 Comments

Pavel Mikhailyuk Over a year ago

...rge(hashtags).distinct

Ilja KO Over a year ago

It works all right BUT it still leaves out all the other articles which have NO hashtags associated to

Rodrigo Over a year ago

Please add the generated queries for both cases

Ilja KO Over a year ago

Here it is for the both queries(the only thing that is different is NOT iLIKE and iLIKE) :

SELECT  DISTINCT "articles".* FROM "articles" INNER JOIN "hashtags_articles" ON "hashtags_articles"."articles_id" = "articles"."id" INNER JOIN "hashtags" ON "hashtags"."id" = "hashtags_articles"."hashtag_id" WHERE (LOWER(value) NOT iLIKE ANY ( array['politics china'] )) LIMIT $1 OFFSET $2

Collectives™ on Stack Overflow

How to make my DB query method in Rails more efficient

2 Answers 2

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related