Databases
StackOverflow2013
Postsdbo
POLoop within loop within loop on Ruby on Rails
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POLoop within loop within loop on Ruby on Rails
    primarykey
    data
    text
    <p>I want the user of my website to see the average of the answers (1 to 100) his friends have given him on different questions.</p> <p>The final result of this method is an array with [question, sum of all answers, answer count]</p> <p>3 tables are involved, each one with its own controller and model</p> <p>I have a table of 'friendships', which stores users and friends.</p> <p>Then there is a table of questions, which have just the questions and its ids.</p> <p>Finally I have an answers table, which stores the friendship id, the question id and the answer given.</p> <p>I found a way to do it (haven't tried it yet though): I look for the friendships of the user, then look for the answers given by each of these friendships, then going through each question to add it to a final array.</p> <pre><code>q_and_a = Array.new friendships = Friendship.find_by_user_id(current_user.id) friendships.each do |friendship| answers = Answer.find_by_friendship_id(friendship.id) answers.each do |answer| q_and_a.each do |array| question = Question.find_by_id(answer.question_id) if array[0] == statement.description array[1] = array[1] + answer.value array[2] = array[2] + 1 else q_and_a &lt;&lt; [ question.description, answer.value, 1 ] end end end end </code></pre> <p>Its horrible, I'm hoping there is a better way to do this? Even more considering that the friendships table, as well as the answers table, are bound to have hundreds of thousands of records and will continue to grow</p> <p><strong>Edit</strong> As stated below, I had the associations set up but i was not using them! rookie mistake. The final working code is:</p> <pre><code>@q_and_a_them = Array.new user_answers = current_user.friends.collect { |c| c.answers }.flatten user_answers.each do |a| question = Question.find_by_id(a.question_id) if @q_and_a_them.empty? @q_and_a_them &lt;&lt; [ question.description, a.value, 1 ] else @q_and_a_them.each do |q| if q[0] == question.description q[1] = q[1] + a.value q[2] = q[2] + 1 else @q_and_a_them &lt;&lt; [ question.description, a.value, 1 ] end end end end </code></pre> <p>Plus the config on friendship.rb</p> <pre><code>has_many :relationships has_many :answers, :through =&gt; :relationships </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USwachichornia
    1. USwachichornia
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POLoop within loop within loop on Ruby on Rails
      1. USNic
      1. VTFavorite
    2. VO
      singulars
      1. POLoop within loop within loop on Ruby on Rails
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POLoop within loop within loop on Ruby on Rails
      1. USwachichornia
      1. VTFavorite
    1. COIs your backend a traditional SQL database? This does potentially present a problem "at scale", which would lead to potentially optimizing this w/ a separate "friendship_stats" table that is eventually consistent/updated in "near" realtime.
      singulars
      1. POLoop within loop within loop on Ruby on Rails
      1. USPete
    2. COit is an sqlite3, I'm starting the development. You mean I should have a 'friendship_stats' table that is running the code above in the background all the time?
      singulars
      1. POLoop within loop within loop on Ruby on Rails
      1. USwachichornia
    3. CONot necessarily "all the time", but at some interval in the background such that users would see a roughly-up-to-date version of this. Much the same way a user's reputation on SO isn't recalculated every time you log in, some things make sense to compute once and cache.
      singulars
      1. POLoop within loop within loop on Ruby on Rails
      1. USPete
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload