How to display query result in a Hash in view Ruby On rails

Question

My controller has the following query :

# reports_between_two_dates is already defined
@data_count_by_city = reports_between_two_dates
                        .joins('JOIN "places" ON "places"."id"="reported_regs"."reported_place_id" JOIN "cities" ON "places"."city_id" = "cities"."id" ')
                        .where('"reported_regs"."reported_place_id" IN (SELECT "places"."id" FROM "places") AND "places"."city_id" IN (SELECT "cities"."id" FROM "cities")')
                        .group('"display_name"')
                        .select('COUNT(*) AS count, "display_name" AS cities')
                        .order('count DESC')
                        .limit(3)

I also have this in the controller :

respond_to do |format|
  format.js { 
    render json: { 
    html: render_to_string(
      partial: 'data_stats', 
      locals: { 
        data_count_by_city: @data_count_by_city
    }) } }
  format.html
end

The query's result in psql is as follows :

 count |    cities     
-------+---------------
   409 | NYC
   244 | SF
   156 | LA

My end goal is to display the above result as a hash, having as keys the cities, and the count as values..

In the view I've tried doing :

<td><%= Hash[@data_count_by_city.map{ |r| [r.cities,r.count] }] %></td>

But all I get is an empty hash.. What am I missing?

What's the query result in Ruby: @data_count_by_city.inspect? — Leonel Galán
– Leonel Galán, Commented Sep 8, 2017 at 15:00
There are no results, and that's why you get an empty hash. So let's go back to the query and forget about the controller and view. What's the query in SQL? What's the query you see in the logs of your Rails Server? — Leonel Galán
– Leonel Galán, Commented Sep 8, 2017 at 15:08
You should not need a custom join string if your associations are properly defined. Can you include the models? — max
– max, Commented Sep 8, 2017 at 15:33

Leonel Galán · Accepted Answer · 2017-09-08 16:10:13Z

1

It appears the relation was returning no results (empty relation: #<ActiveRecord::Relation []>). OP find out he was running the query on Production and the code on a different environment.

Other than that, the code seems to work as expected, as long as @data_count_by_city stores an array-like structure with both cities and count. On that note, I think

.group('display_name').count shouldn't need the .select('COUNT(*) AS count, "display_name" AS cities')

answered Sep 8, 2017 at 16:10

Leonel Galán

7,1972 gold badges44 silver badges60 bronze badges

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

2 Comments

GeoSal Over a year ago

Thanks a lot Leito for the heads up :) Btw if I select certain dates I get an empty hash I assume it's because of the Inner Join, I tried doing the Outer Join but I still get no results. Any ideas?

Leonel Galán Over a year ago

Probably, but same advice goes: compare the queries in your server logs to the ones in psql. Then slowly deconstruct the query to see what causes it to be empty.

Collectives™ on Stack Overflow

How to display query result in a Hash in view Ruby On rails

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