Databases
StackOverflow2013
Postsdbo
PORails 3.2.2 Query on sub sub attribute advise
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PORails 3.2.2 Query on sub sub attribute advise
    text
    copied!<p>Currently have a very inefficient view partial that lists groups of users by their predicted score.</p> <p>group controller</p> <pre><code>def show @sfs_ordered = ScoreFootballSimple.order("home_score DESC, away_score ASC") @live_games = Game.find(:all, :conditions =&gt; ['kickoff &lt; ? AND completed != true AND game_state is NOT NULL', Time.now]) </code></pre> <p>group#show (relevant section)</p> <pre><code>&lt;% @live_games.each do |game| %&gt; &lt;% @sfs_ordered.each do |sfs| %&gt; &lt;% got_this_score = Array.new %&gt; &lt;% game_points = nil %&gt; &lt;% @group.members.each do |member| %&gt; &lt;% if pred = member.prediction_set.predictions.where('game_id = ?',game.id).first %&gt; &lt;% game_points = pred.points if !pred.points.nil? &amp;&amp; pred.score_type == sfs.score_type %&gt; &lt;% got_this_score &lt;&lt; member.user.user_detail.display_name if pred.score_type == sfs.score_type %&gt; &lt;% end %&gt; &lt;% end %&gt; &lt;% if got_this_score.count &gt; 0 %&gt; &lt;tr&gt;&lt;td&gt;&lt;%= sfs.home_score %&gt;-&lt;%=sfs.away_score%&gt;&lt;/td&gt; &lt;td&gt;&lt;% if !game_points.nil? %&gt; &lt;div class="preds-show-points-div"&gt;&lt;%= game_points %&gt;pts&lt;/div&gt; &lt;% else %&gt; - &lt;% end%&gt;&lt;/td&gt; &lt;td&gt;&lt;%= got_this_score.to_sentence %&gt;&lt;/td&gt;&lt;/tr&gt; &lt;% end%&gt; &lt;% end %&gt; &lt;% end %&gt; </code></pre> <p>Obviously this is loops within loops that means the that for every @sfs_ordered (round 50 records) it is iterating over every group member (for the largest group about 5000) and this means the page is taking seconds to load.</p> <p>Don't flame me, this was the POC to show how it could look but it has exposed my lack of ability with ActiveRecord. Now I could go around creating a hash of users, predictions sets etc but I wondered if anyone could point me to a better way of selecting information with more precision using Rails queries.</p> <p>The entity relationship is something like this</p> <ul> <li><strong>Group</strong> has many <strong>Member</strong></li> <li><strong>Member</strong> belongs to <strong>User</strong> and <strong>PredictionSet</strong></li> <li><strong>PredictionSet</strong> has many <strong>Prediction</strong></li> <li><strong>Prediction</strong> belongs to <strong>Game</strong> and <strong>ScoreType</strong> </li> <li><strong>ScoreTypeSimple</strong> has one <strong>ScoreType</strong></li> </ul> <p>The predicted score is in <strong>ScoreTypeSimple</strong> - this is how I want the list organised</p> <p>e.g 1:1 - Joe, Fred and Jane 1:0 - Sue, Dave and Helen</p> <p>Then I want to grab the <strong>Group</strong>.member.user.name for the <strong>Group</strong>.member.prediction_set.prediction - where the prediction.game.id == game.id AND score_type == sfs.score_type </p> <p>I know I can improve this by pure SQL joins and INs and build hashes but I wondered if any one could give me any pointers if there was a Rails/Ruby efficient way of doing this. I know the answer is probably out there in lambda but my ActiveRecord knowledge is stretch to the limit here!</p> <p>Any help gratefully received.</p> <p>Peter</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

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