calculation not interpreted correctly: NoMethodError (undefined method `-' for "3":Arel::Nodes::SqlLiteral)

[roscom]

I have 2 tables and a join table

class Keyword < ApplicationRecord
  has_many :semrush_related_keywords, dependent: :destroy
  has_many :semrush_phrases, through: :semrush_related_keywords
end

class SemrushPrase < ApplicationRecord
  has_many :semrush_related_keywords, dependent: :destroy
  has_many :keywords, through: :semrush_related_keywords
end

# and the join table 
class SemrushRelatedKeyword < ApplicationRecord
  belongs_to :keyword
  belongs_to :semrush_phrase
end

The following SQL works correctly

SELECT keywords.phrase, semrush_related_keywords.used_count, COUNT(*), (3 - semrush_related_keywords.used_count) * count(*)
FROM keywords 
INNER JOIN semrush_related_keywords ON semrush_related_keywords.keyword_id = keywords.id 
GROUP BY keywords.phrase, semrush_related_keywords.used_count 
HAVING semrush_related_keywords.used_count < 3 and count(*) = 100 LIMIT 10

The equivalent rails code (markdown annotated) generated through scuttle is

Keyword.select(
  [
    Keyword.arel_table[:phrase], SemrushRelatedKeyword.arel_table[:used_count], Arel.star.count, **Arel::Nodes::Group.new(
      Arel::Nodes::SqlLiteral.new('3') - SemrushRelatedKeyword.arel_table[:used_count]
    ) * Arel.star.count**
  ]
).having(
  SemrushRelatedKeyword.arel_table[:used_count].lt(3).and(Arel.star.count.eq(100))
).joins(
  Keyword.arel_table.join(SemrushRelatedKeyword.arel_table).on(
    SemrushRelatedKeyword.arel_table[:keyword_id].eq(Keyword.arel_table[:id])
  ).join_sources
).group(
  Keyword.arel_table[:phrase], SemrushRelatedKeyword.arel_table[:used_count]
).limit(10)

The resulting error produced in rails console using ruby 2.6.6

NoMethodError (undefined method `-' for "3":Arel::Nodes::SqlLiteral)

Could you please advise the correct method to do the calculation?

[roscom]

ruby 2.6.6 rails 6.1.7.6

[camertron]

Hey @roscom, thanks for bringing this to my attention. It looks like what you want is Arel::Nodes::InfixOperation eg:

Keyword.select(
  [
    Keyword.arel_table[:phrase],
    SemrushRelatedKeyword.arel_table[:used_count],
    Arel.star.count,
    Arel::Nodes::InfixOperation.new(
      '-', 3, SemrushRelatedKeyword.arel_table[:used_count]
    ) * Arel.star.count
  ]
).having(
  SemrushRelatedKeyword.arel_table[:used_count].lt(3).and(Arel.star.count.eq(100))
).joins(
  Keyword.arel_table.join(SemrushRelatedKeyword.arel_table).on(
    SemrushRelatedKeyword.arel_table[:keyword_id].eq(Keyword.arel_table[:id])
  ).join_sources
).group(
  Keyword.arel_table[:phrase], SemrushRelatedKeyword.arel_table[:used_count]
).limit(10)

I just pushed an update to scuttle.io that should do this correctly now, let me know if you're still having problems 😄