How to Count the Elements in an SQL Array using Ruby on Rails Queries

Question

I want to count the number of elements in an array specific to a user id and then display that number. The last_viewed array keeps track of the event the user last visited.

The schema.db looks like this (its not the whole schema I just included what I think is necessary to solve my problem.)

create_table "users", id: :serial, force: :cascade do |t|
   t.integer "last_viewed", default: [], array: true
end

I tried

<%= ((Users.last_viewed.where("id = ?", current_user.id)).count) %>

but it seems to think that last_viewed is a method and not part of the Users table.

I know how to use real SQL but these queries in Ruby on Rails are confusing. Essentially I want to turn this SQL code into something that works on Ruby.

SELECT array_length(u.last_viewed, 1) 
FROM users u 
WHERE u.id = <user's id>;

I think correct query should be Users.where("id = ?", current_user.id)).last_viewed.count — Ninh Le
– Ninh Le, Commented Aug 4, 2020 at 5:36
Have you looked at using Arel to define the array_length function so you can reference it in a model? The benefits would vary, but for a very large array you would not have to retrieve it to Rails to process it. — David Aldridge
– David Aldridge, Commented Aug 15, 2020 at 15:45

Sebastián Palma · Accepted Answer · 2020-08-04 06:05:18Z

1

last_viewed is a column in the users table, which acts as a method in an instance of the User model. If you don't have an instance of that class you can't use that method unless you defined your own.

To get the total last_viewed from the user with id equal to current_user.id you can simply use find, invoke that method and as it returns an array you can use size/count/length to have the total of elements:

Users.find(id: current_user.id)&.last_viewed&.length

& is used to avoid a NoMethodError exception in case a user with that id doesn't exist in the database and find returns nil.

answered Aug 4, 2020 at 6:05

Sebastián Palma

33.6k6 gold badges45 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to Count the Elements in an SQL Array using Ruby on Rails Queries

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related