Can't show array values multi query from the controller

Question

I'm trying to show array values using each month a year from the controller but is not showing anything.

Here is the information and demo:

CREATE TABLE player_scores (id INT,name text,date_score date,goals INT );
CREATE TABLE months (id INT,name text );

INSERT INTO player_scores VALUE
 ( 1, 'PIZARRO'   , '2015-01-02', 4),
 ( 2, 'ROBBEN'    , '2015-02-24', 2),
 ( 3, 'RIBERY'    , '2015-03-02', 4),
 ( 4, 'GOTZE'     , '2015-04-24', 2),
 ( 5, 'NEIWER'    , '2015-05-02', 4),
 ( 6, 'DANTE'     , '2015-06-24', 2),
 ( 7, 'LEWANDOSKI', '2015-07-02', 4),
 ( 8, 'RAFINHA'   , '2015-07-02', 4),
 ( 9, 'GUARDIOLA' , '2015-11-02', 3);

INSERT INTO months VALUE
 ( 1, 'jan'),
 ( 2, 'feb'),
 ( 3, 'mar'),
  ...      
 ( 12,'dec');

Here is the controller:

def germany_world_cup
   @months= Month.all

   ## THIS LINE WILL REPEAT THE QUERY 12 TIMES FOR EACH MONTH JANUARY TO DECEMBER 
   @months.each do |m|
      @query = PlayerScore.find_by_sql("SELECT count(*) AS count_all FROM player_scores WHERE YEAR(date_score)=2015 AND MONTH(date_score)='#[m.id}' ")
   end
end

Here is the view with the problem (is not showing values):

<% @query.each do |q| %> 
  <%= q.count_all %>  ### IS SHOWING only 0 as result
<% end %>

Here is my log actually working but is not showing in the view:

select count(*) as count_all from player_scores where month(date_score)=1 and year(date_score)=2015
select count(*) as count_all from player_scores where month(date_score)=2 and year(date_score)=2015
...
select count(*) as count_all from player_scores where month(date_score)=12 and year(date_score)=2015

According to rails sintaxis to show values from controller is

<% @var.each do |v| %>
   <%= v.column_name %>
<% end %>

I tried this code to inspect values but got nothing:

<%= @query.inspect %>  ### I got " [#] " as result

I tried this code:

<% @query[0].each do |q| %> 
  <%= q.count_all %>   ### I GOT THIS ERROR "undefined method `each' "
<% end %>

I want to show valus from arrays in the controller

Please somebody can tell me to show values?

Thanks in advance.

Your loop will repeat the query 12 times, but each time it loops it just overwrites the @query instance variable — j-dexx
– j-dexx, Commented Jan 13, 2015 at 15:41
You can do @queries = [] then do your loop but assign the result to the queries variable, so you're putting them all into an array e.g. @queries << PlayerScore.find_by_sql(my_query) in your loop — j-dexx
– j-dexx, Commented Jan 13, 2015 at 15:56
Why not do PlayerScore.select("Month(date_score) as in_month, Count(id) as count_all").where("Year(date_score)=2015").group("Month(date_score)").order("Month(date_score)"). This will execute 1 query and allow you iterate appropriately giving access to in_month and count_all. — engineersmnky
– engineersmnky, Commented Jan 13, 2015 at 16:07

Малъ Скрылевъ · Accepted Answer · 2015-01-18 19:39:45Z

1

The way to avoid the @query variable overwrite is to use #map:

@query = @months.map do |m|
   PlayerScore.find_by_sql("SELECT count(*) AS count_all FROM player_scores WHERE YEAR(date_score)=2015 AND MONTH(date_score)='#[m.id}' ")
end

answered Jan 18, 2015 at 19:39

Малъ Скрылевъ

16.5k5 gold badges59 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Малъ Скрылевъ Over a year ago

@EzioAuditore yes, but that is not quite ruby approach ;)

Ezio Auditore Over a year ago

Thanks it was a good advice, it worked for me +1 for your help.

Collectives™ on Stack Overflow

Can't show array values multi query from the controller

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related