How to Count the Number of Records That Have a Unique Value in a Particular Field in Ror

How can I count the number of records that have a unique value in a particular field in ROR?

What you're going for is the following SQL:

SELECT COUNT(DISTINCT date) FROM records

ActiveRecord has this built in:

Record.count('date', :distinct => true)

Error counting the number of records with a unique value in a field

You're almost there:

DistributedHealth.distinct('deviceID').count

You want to grab the distinct deviceIDs using distinct and then count them using Array#count (or Array#length if you want to remind yourself that you're really counting the elements in an array rather than directly querying the database for the count).

Count distinct pairs of attributes in Rails

Did you try?

Restaurant.pluck(:url, :name).uniq.count

More here http://api.rubyonrails.org/classes/ActiveRecord/Calculations.html#method-i-pluck

And also check this question Rails: select unique values from a column

rails COUNT SELECT DISTINCT

You can use distinct.count(:attribute_name).

(In Rails 3 use: count(:user_id, distinct: true) instead)

Thus:

UserVideoWatching.where("created_at >= ? AND user_id != ?", 1.month.ago, User.elephant.id)
.group("DATE(created_at)").reorder('created_at').distinct.count(:user_id)

Not able to test but I think that'll produce the SQL you're after.

How do I count the number of records that have one or more associated object?

Since all you want is the Propertys with Photos then an INNER JOIN is all you need.

Property.joins(:photos) 

That is it. If you want a scope then

class Property < ActiveRecord::Base
scope :with_photos, -> {joins(:photos)}
end

To get the count using rails 3.2

Property.with_photos.count(distinct: true)  

You could also use: in rails 3.2

Property.count(joins: :photos, distinct: true) 

ActiveRecord::Calculations#count Doc

This will execute

SELECT 
COUNT(DISTINCT properties.id)
FROM
properties
INNER JOIN photos ON photos.property_id = properties.id

Count method to total up the records with a boolean field set to a particular state. RoR

There is no automatic method created by ActiveRecord to do this, but you could use a scope for this purpose, by adding this to your Post model:

scope :posted, -> { where.not(posted_at: nil) }

Then, you will be able to do this anywhere:

@posts.posted.count

Rails: select unique values from a column

Model.select(:rating)

The result of this is a collection of Model objects. Not plain ratings. And from uniq's point of view, they are completely different. You can use this:

Model.select(:rating).map(&:rating).uniq

or this (most efficient):

Model.uniq.pluck(:rating)

Rails 5+

Model.distinct.pluck(:rating)

Update

Apparently, as of rails 5.0.0.1, it works only on "top level" queries, like above. Doesn't work on collection proxies ("has_many" relations, for example).

Address.distinct.pluck(:city) # => ['Moscow']
user.addresses.distinct.pluck(:city) # => ['Moscow', 'Moscow', 'Moscow']

In this case, deduplicate after the query

user.addresses.pluck(:city).uniq # => ['Moscow']

Rails: how can I get unique values from column

Two more ways:

Product.select(:category).map(&:category).uniq # Ruby does the work

Product.uniq.pluck(:category) # DB does the work (superior)

For Rails >= 5.1 use:

Product.distinct.pluck(:category) # DB does the work (superior)

...because Relation#uniq was deprecated.

Find all records which have a count of an association greater than zero

joins uses an inner join by default so using Project.joins(:vacancies) will in effect only return projects that have an associated vacancy.

UPDATE:

As pointed out by @mackskatz in the comment, without a group clause, the code above will return duplicate projects for projects with more than one vacancies. To remove the duplicates, use

Project.joins(:vacancies).group('projects.id')

UPDATE:

As pointed out by @Tolsee, you can also use distinct.

Project.joins(:vacancies).distinct

As an example

[10] pry(main)> Comment.distinct.pluck :article_id
=> [43, 34, 45, 55, 17, 19, 1, 3, 4, 18, 44, 5, 13, 22, 16, 6, 53]
[11] pry(main)> _.size
=> 17
[12] pry(main)> Article.joins(:comments).size
=> 45
[13] pry(main)> Article.joins(:comments).distinct.size
=> 17
[14] pry(main)> Article.joins(:comments).distinct.to_sql
=> "SELECT DISTINCT \"articles\".* FROM \"articles\" INNER JOIN \"comments\" ON \"comments\".\"article_id\" = \"articles\".\"id\""


Related Topics



Leave a reply



Submit