CODEWITHSUNDEEP
ruby-on-railsrubypostgresql
avetshnoir
avetshnoir

Reputation: 31

ActiveRecord group with alias

First question ever on here, and pretty new to coding full apps/Rails.

I was creating a method to get the counts for titles by author, and noticed that if the author is cased differently, it would count as different authors. I wanted to place some sort of validation/check to disregard the casing and count it together. I don't care about the casing of the book titles in this particular case.

So I have table like this:

Author                Book Title                               Year    Condition
William Shakespeare   Hamlet                                   1599    Poor
Stephen King          The Shining                              1977    New
Edgar Allen Poe       The Raven                                1845    Good
JK Rowling            Harry Potter and the Sorcerer's Stone    2001    New
edgar allen poe       The Tell-Tale Heart                      1843    Good
JK Rowling            Fantastic Beasts and Where to Find Them  2001    New

I want to output this:

Author                 Count
William Shakespeare    1
Stephen King           1
Edgar Allen Poe        2                              
JK Rowling             2

My method was originally something like this:

  def self.book_counts
    distinct_counts = []
    Book.group(:author).count.each do |count|
      distinct_counts << count
    end
    distinct_counts
  end

To ignore casing, I referenced this page and came up with these, which didn't end up working out, unfortunately:

1) With this one I get "undefined method lower":

    Book.group(lower('author')).count.each do |count|
      distinct_counts << count

2) This runs, but with the select method in general, I get a bunch of ActiveRecord results/Record id: nil. I am using Rails 6 and it additionally notes "DEPRECATION WARNING: Dangerous query method (method whose arguments are used as raw SQL) called with non-attribute argument(s) ... Non-attribute arguments will be disallowed in Rails 6.1. This method should not be called with user-provided values, such as request parameters or model attributes. Known-safe values can be passed by wrapping them in Arel.sql(). (called from irb_binding at (irb):579)":

Book.select("lower(author) as dc_auth, count(*) as book_count").group("dc_auth").order("book_count desc")

3) I even tried to test a different, simplified function to see if it'd work, but I got "ActiveRecord::StatementInvalid (PG::GroupingError: ERROR: column "books.author" must appear in the GROUP BY clause or be used in an aggregate function)":

    Book.pluck('lower(author) as dc_auth, count(*) as book_count')

4) I've tried various other ways, with additional different errors, e.g. "undefined local variable or method 'dc_auth'", "undefined method 'group' did you mean group_by?", and "wrong number of arguments (given 1, expected 0)" (with group_by), etc.

This query works exactly how I want it to in postgresql. The syntax actually populates in the terminal when I run #2, but as mentioned, unfortunately due to ActiveRecord doesn't output properly in Rails.

SELECT lower(author) as dc_auth, count(*) as book_count FROM books GROUP BY dc_auth;

Is there even a way to run what I want through Rails??

Upvotes: 3

Views: 1808

Answers (3)

Yakov
Yakov

Reputation: 3201

Why do you store authors in the same table with books. The better solution is to add a separate table for authors and add a foreign key to author_id to books table. With counter_cache you can easily count the number of books for each author.

Here is a guide with books and authors examples https://guides.rubyonrails.org/association_basics.html

Upvotes: 1

user11544535
user11544535

Reputation:

You can execute your query using ActiveRecord. And I will suggest to go with SQL block

book_count_query = <<-SQL 
       SELECT lower(author) as dc_auth, count(*) as book_count
       FROM books
       GROUP BY dc_auth;
SQL

1- result = ActiveRecord::Base.connection.execute(book_count_query)

or 
2- result = ActiveRecord::Base.connection.exec_query(book_count_query)

What difference between line 1 and line 2?

exec_query it returns an ActiveRecords::Result object which has handy methods like .columns and .rows to access headers and values.

The array of hashes from .execute can be troublesome to deal with and gave me redundant results when I ran with a SUM GROUP BY clause.

If you need read more about this topic

This Resource have example for query and output .

Upvotes: 1

Thuy Nguyen
Thuy Nguyen

Reputation: 362

Maybe you can try

Book.group("LOWER(author)").count

Upvotes: 3

Related Questions