I'm using Rails 5.2.2 and I want to improve my db queries to not to overload the database with unnecessary queries, what is happening at the moment, besides code should be clear. I tried to figure out by myself how to implement query object (with little help of this article and that one too) without any positive results.

I want to improve these methods, any suggestions are welcome:

  def last_month
    Shareholder.
      non_free_account(beginning_of_this_month).
      where("created_at >= ? AND created_at <= ?", beginning_of_last_month, beginning_of_this_month)
  end

  def never_sub?(shareholder)
    ShareholderSubscription.where(shareholder_id: shareholder.id).empty? && shareholder.previously_subscribed == false
  end

  def owner_has_one_shareholder?(shareholder)
    ShareholdersUser.where(user_id: shareholder.owner_id).
               where.not(shareholder_id: shareholder.id).exists?
  end

I think if I use more complex sql query with joins and where conditions etc. in result the code will be more efficient and clear. Probably I will not need this complicated call method too.

  def call
    prop_shareholders
  end

  private

  def prop_shareholders
    shareholders_last_month = last_month
    shareholders_last_month.each_with_object([]) do |shareholder, apt|
      if never_sub?(shareholder) && owner_has_one_shareholder?(shareholder)
        apt << shareholder
      end
    end
  end

UPDATE:

Ok so after few days I made this query:

Shareholder.
      non_free_account(beginning_of_this_month).
      where("shareholders.created_at >= ?", beginning_of_last_month).
      where("shareholders.created_at <= ?", beginning_of_this_month).
      joins("LEFT JOIN shareholder_subscriptions ON shareholder_subscriptions.shareholder_id = shareholders.id").
      where(shareholder_subscriptions: { id: nil }).
      joins("RIGHT JOIN shareholders_users ON shareholders_users.shareholder_id = shareholders.id").
      where("shareholders_users.user_id IN (SELECT user_id FROM shareholders_users
                                      GROUP BY shareholders_users.user_id
                                      HAVING COUNT(shareholders_users.user_id) = 1)")

And this is the solution for this task. However I think it could be even more sophisticate if you applied ActiveRecords for all query - I mean with subquery either but I don't know how to do that. Maybe someone could give me some tips about it?

0 Answers