ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR: syntax error at or near "AS"

I am wondering if anyone can explain why I get this error in ActiveRecord SQL injection, but tests pass when I add .to_a? I am using PostgreSQL with Ruby on Rails and RSpec.

def top_5_authors
  books.joins(review: :author)
    .where('books.title = ?', 'reviewed')
    .select("CONCAT(authors.first_name,' ', authors.last_name) AS full_name, COUNT(books.id) AS         book_count")
    .group('full_name')
    .order('book_count DESC')
    .limit(5)
    # .to_a
end

This is the error message when I comment out .to_a:

ActiveRecord::StatementInvalid:
     PG::SyntaxError: ERROR:  syntax error at or near "AS"
     LINE 1: ...AT(authors.first_name,' ', authors.last_name) AS full_na...
                                                                    ^

It seems that returning the query as an array overwrites the syntax error, possibly having something to do with the SQL injection at .select where I am using CONCAT. However I've tried multiple combinations without CONCAT and without the alias full_name and it then throws an error at AS book_count. I've tried it without that alias as well and refactored accordingly.

In both situations, when I add .to_a it works fine.

I believe I'm querying with correct syntax, could be wrong as both SQL and ActiveRecord are new to me, but when using .to_a it works fine and I can access the data I need. I'm just failing to understand why at the moment. Any ideas would be welcome. Thank you.

Solution

Based on your statement that your tests pass when you use .to_a, that would suggest that your test is chaining another method onto top_5_authors like .count. You can safely call .count on an Array. But you can't always call .count on an ActiveRecord relation depending on the SQL aggregations you are performing.

If you try to run your test and then you look at what is in log/test.log you should be able to see the full SQL query it was trying to run. If you were using .count in your test it might have something like:

SELECT COUNT(CONCAT(authors.first_name,' ', authors.last_name) AS full_name, COUNT(books.id) AS book_count) AS ...

In this case, ActiveRecord wrapped the select value within a COUNT function. Using a column alias within COUNT isn't allowed.

I would suggest going into the Rails console and seeing how ActiveRecord behaves for these simpler queries to get a feel for what is going on:

# This will count all books
Book.count

# This will count all books with non-null titles
Book.select('title').count

# This will crash on a StatementInvalid error because you can't 
# alias a column within a count
Book.select('title AS x').count

# This will load all books into an array and then compute the array length
Book.select('title AS x').to_a.count