trueCamelType trueCamelType - 1 month ago 5
Ruby Question

How to create more complex SQL queries from Controller

First of all, let me say that I don't know that the current issue is the "correct" way to get what I want. Here's the problem.

I have a database named

users
that looks like this:

|__id__|__dateTime__|___ipAddress___|__userName__|
| 1 | time1 | 192.168.0.1 | Paul |
| 2 | time2 | 192.168.0.22 | Jessica |
| 3 | time1 | 192.168.0.22 | Jon |


I know that in the Controller, I can write things like

User.find(3)


and that's the equivalent of

SELECT * FROM users WHERE (users.id = 3) LIMIT 1


But, what if, in my logs, I want to do something like finding how many unique ip addresses there are, or if I want a query that returns how many events happened during
time1
.

Is there a way to do this in the Controller, or is this not where something like that should be done?

Answer

Active Record offers a query interface, which can be found on the guides here. You can execute arbitrary queries as well with #find_by_query, and don't need to depend on helper methods.

finding how many unique ip addresses

this would be accomplished with something like User.select(:ipAddress).distinct.count

I want a query that returns how many events happened during time1

I'm not sure what you mean by events, but if you want a count of the entries in the User table that match a given timestamp, you can do User.where(:dateTime=>time1).count

(I assume your given example is fake, but you wouldn't want to name a column dateTime)

Comments