Postregres or ActiveRecord query to find records with most referenced as foreign key

Question

I need to return an array of records (from table Foo) by the number of times they are referenced as a foreign key in either one of two columns of another database table:

 id  | old_foo_id | new_foo_id
-------------------------------
   1 |          1 |          1 
   2 |          2 |          2 
   3 |          3 |          3 
   4 |          1 |          1 
   5 |          1 |          2 
   6 |          2 |          4 
   7 |          4 |          2 
   8 |          5 |          5 
   9 |          6 |          6 
  10 |          7 |          7

For example, using the data above, I need a ActiveRecord (or straight up SQL) query to return a hash ordered by most common foreign keys (counted only once per record if appearing in both old_foo_id and new_foo_id) like this:

{"2"=>4, "1"=>3, "4"=>2, "3"=>1,  "5"=>1, "6"=>1, "7"=>1}

I have no idea how to do this other than to loop over every Foo object and make a ton of queries - seems very inefficient and time consuming. Thanks so much in advance!

I found a similar question here: Get count of foreign key from multiple tables

What was the similar question? – onebree Jul 23 '15 at 19:35 — onebree, Jul 23 '15 at 19:35
I've edited the question, thanks Hunter – etayluz Jul 23 '15 at 19:37 — etayluz, Jul 23 '15 at 19:37

Zargold · Answer 1 · 2015-07-23T19:58:23.923

0

Sorry about previous answer: I would create an object in the app/models/foo.rb:

Create a method for that model which can return such a hash:

def self.frequency_count
...

So for example you want: {"2"=>4, "1"=>3, "4"=>2, "3"=>1, "5"=>1, "6"=>1, "7"=>1}

So I would do

foo has_many :bars

def self.counts
    self.bars.count
end

In app/controllers/foo

def freq_counter
   @foos = Foo.all
   myHash = {}
   @foos.each do |foo|
      myHash[foo.id]= foo.counts
   end
   myHash
end

So at least now we narrowed down that you'd like to do the above in the most optimized SQL command.

edited Jul 23 '15 at 19:58

answered Jul 23 '15 at 19:16

Zargold

1,892
18
24

does that help at all I'm not sure I understood the question – Zargold Jul 23 '15 at 19:30
That works, but it's a ton of database queries, will be very time consuming - I prefer a single call to Postgres with an SQL query. Suppose Foo has 1000+ records - it translates to 1000 queries – etayluz Jul 23 '15 at 19:33

score 0 · Answer 2 · answered Jul 23 '15 at 20:47

0

After a lot of trial and error, this SQL Query works great:

a = ActiveRecord::Base.connection.execute("SELECT foos.old_foo_id, count(*) FROM foos GROUP BY old_foo_id ORDER BY count DESC")
puts a.to_a

answered Jul 23 '15 at 20:47

etayluz

15,920
23
106
151

Postregres or ActiveRecord query to find records with most referenced as foreign key

2 Answers2