Topic: Help with joins in ROR using gruff

Help with joins in ROR using gruff


I have 2 tables in my database("rtdb"), i need to get a query that  it brings to me the total of tickets by users. I'm using postgresql, and the library gruff, the problem is how get the records with g.data using gruff.

So I have the table : Users, and the table Tickets ( The schema of RT Esencials , tickets System)
then:

I put require 'gruff' in my enviroment.rb

My model is:

class Users < ActiveRecord::Base
  set_table_name "users"
end

My controller is:

class UsersController < ApplicationController

def grafico
 
   
      g = Gruff::Bar.new("600x300")
      g.title = "Grafico #1"
     
     # @usuarios = Users.find(:all)
      @usuarios = Users.find_by_sql("SELECT us.name as Usuario,count(*) AS total FROM users as us RIGHT outer JOIN tickets as t ON  t.owner=us.id  group by us.name")
      @usuarios.each {|usuarios| g.data(usuarios.usuario, [usuarios.total])}
     

     
       #g.labels = {0 => '2003', 1 => '2004'}
       g.minimum_value = 0
       g.legend_font_size = 14
       g.marker_font_size = 14
       g.title_font_size = 24
      # --------------------------
      #Para mandar generar el archivo y guardarlo en la carpeta:
      #filename = 'imagen.png'
      # g.write(filename)
      # send_file filename, :type => 'image/png', :disposition => 'inline'
      # --------------------------
      # Para mandar la imagen directamente a una pagina:
    send_data(g.to_blob, :type => 'image/png',:disposition => 'inline')

   
  end
  def ver_users
    @usuarios = Users.find_by_sql("SELECT us.name as Usuario,count(*) AS total FROM users as us RIGHT outer JOIN tickets as t ON  t.owner=us.id  group by us.name")
       
  end
end


This query:
 SELECT us.name as Usuario,count(*) AS total FROM users as us RIGHT outer JOIN tickets as t ON  t.owner=us.id  group by us.name

it brings to me
usuario     | total
--------------------
lucks       |   2
root        |  10
other_user  |  15

In my view I had :
<img src="<%= url_for :controller=>'users', :action=>'grafico' %>">
<%for ver_users in @usuarios %>
<<%=ver_users.usuario %>
  <%=ver_users.total %>
<% end%>

but my graphic don't appear. Only the table, with the records. #sad#

I thing that the problem is my controller, here:

 @usuarios = Users.find_by_sql("SELECT us.name as Usuario,count(*) AS total FROM users as us RIGHT outer JOIN tickets as t ON  t.owner=us.id  group by us.name")

@usuarios.each {|usuarios| g.data(usuarios.usuario, [usuarios.total])}

.: All the luck that you can imagine :.

Re: Help with joins in ROR using gruff

Ok, I found a solution, I don't know if this is the best, but it works:

only put in my controller:

 sqltext ="SELECT us.name as Usuario,count(*) AS total FROM users as us RIGHT outer JOIN tickets as t ON  t.owner=us.id  group by us.name"
      cuenta = Users.connection.select_all(sqltext)
      numero = cuenta[0]["total"].to_i
     
      @usuarios = Users.find_by_sql("SELECT us.name as Usuario,count(*) AS total FROM users as us RIGHT outer JOIN tickets as t ON  t.owner=us.id  group by us.name")
      @usuarios.each {|usuarios| g.data(usuarios.usuario, [numero])}

If you see, I'm repeat the query sentence, but this works to generate the graphic, with gruff. #lol#

Looks like this:

http://lucks17.googlepages.com/reporte1.jpg

Last edited by Lucks (2006-12-06 21:13:36)

.: All the luck that you can imagine :.

Re: Help with joins in ROR using gruff

dam it, this don't work, this repeat, my first record.

Last edited by Lucks (2006-12-06 21:16:14)

.: All the luck that you can imagine :.

Re: Help with joins in ROR using gruff

Ok, I got it. Only put at the end of the sentence of "usuarios.total"  ".to_i" and works.#lol#

Modification:

 @usuarios = Users.find_by_sql("SELECT us.name as Usuario,count(*) AS total FROM users AS us RIGHT outer JOIN tickets AS t ON  t.owner=us.id  group by us.name")
@usuarios.each {|usuarios| g.data(usuarios.usuario, [usuarios.total.to_i])}

now , this works.
http://lucks17.googlepages.com/reporte1modify.jpg

.: All the luck that you can imagine :.