Preface:

• The following is a rails sql query that allows me to find the 5 most frequent book searches in the last week, grouped by a few things.

time_range = (Time.now - 7.day)..Time.now

frequent = BookSearch.select("count(book_id) as id_count, short_name, isbn, genre").limit(5).where(created_at: time_range).group(:short_name).group(:isbn).group(:genre).order("id_count DESC")

Detail:

• ISBN is a 13 char. string, but I'd like to group by the first two characters.

• Example: if there are two strings, ab1234568901 and ab98765432109, I'd like them to be grouped because they both start with ab

• I've tried replacing .group(:isbn) with .group(:isbn[0,2]) but then it looks for a column named is (the first two char's of isbn) and fails out.

• Note: I want to keep the whole isbn in the db because it's possible we want to later on group by the first 4, 6, etc. digits.

Question:

• How do I transform this query to do everything it's currently doing, but only group by the first two characters of isbn?

CodePudding user response：

I think this should work:

BookSearch.select(
  "count(book_id) as id_count, short_name, genre", 
  "LEFT(book_searches.isbn,2) AS isbn_selector"
).limit(5)
 .where(created_at: time_range)
 .group(:short_name)
 .group("isbn_selector")
 .group(:genre)
 .order("id_count DESC")