app/controllers/analytics_controller.rb
# Get the object we need from query.
Get the object we need from query.
Get the object we need from query .
def helper_query(prod_q, user_q)
    con = ActiveRecord::Base.connection
    prod = prod_q.map{|p| p["id"]}
    user = user_q.map{|u| "'" + u.first + "'"}
    state_str =
    "SELECT up.state, up.product_unique_name, SUM(up.price * coal.quantity) as total
    FROM
    (
      SELECT u.state as state, p.unique_name as product_unique_name,
             u.id AS user_id, p.id AS product_id, p.price as price
      FROM
        (
          SELECT ui.state, ui.id
          FROM Users ui
        ) AS u,
        (
          SELECT pi.unique_name, pi.id, pi.price
          FROM Products pi
          WHERE pi.id IN (#{prod.join(", ")})
        ) AS p
    ) AS up
    LEFT OUTER JOIN
    (
      SELECT purchin.user, purchin.product, SUM(purchin.quantity) AS quantity
      FROM Purchases purchin
      WHERE purchin.product IN (#{prod.join(", ")})
      GROUP BY purchin.user, purchin.product
    ) AS coal
    ON
    up.user_id = coal.user AND
    up.product_id = coal.product
    GROUP BY up.state, up.product_unique_name
    ORDER BY up.state, up.product_unique_name;"
    return con.execute(state_str)
    rescue
      return []
  end
def helper_query ( prod_q , user_q ) con = ActiveRecord :: Base . connection prod = prod_q . map { | p | p [ "id" ] } user = user_q . map { | u | "'" + u . first + "'" } state_str = "SELECT up.state, up.product_unique_name, SUM(up.price * coal.quantity) as total
    FROM
    (
      SELECT u.state as state, p.unique_name as product_unique_name,
             u.id AS user_id, p.id AS product_id, p.price as price
      FROM
        (
          SELECT ui.state, ui.id
          FROM Users ui
        ) AS u,
        (
          SELECT pi.unique_name, pi.id, pi.price
          FROM Products pi
          WHERE pi.id IN (#{prod.join(", ")})
        ) AS p
    ) AS up
    LEFT OUTER JOIN
    (
      SELECT purchin.user, purchin.product, SUM(purchin.quantity) AS quantity
      FROM Purchases purchin
      WHERE purchin.product IN (#{prod.join(", ")})
      GROUP BY purchin.user, purchin.product
    ) AS coal
    ON
    up.user_id = coal.user AND
    up.product_id = coal.product
    GROUP BY up.state, up.product_unique_name
    ORDER BY up.state, up.product_unique_name;" return con . execute ( state_str ) rescue return [ ] end
Get the object we need from query.
Get the object we need from query .
# TODO: Refactor user_q and remove. # SQL INJECTION ON OWN CODE, 1337 # TODO: Add insert from select statement # TODO: Check SUM runtime.
prod_q
<not_specific>
user_q
<not_specific>