[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 814
  • Last Modified:

Ruby on Rails - how to display mySQL results on html.erb page

I've attached the code snippets below

At the moment I'm displaying
Merchant | Product        | Request Type        | Success | Failure
22            | PRODUCT1 | CANCEL_CARD    | 8             |     0 |
22            | PRODUCT1 | REQUEST_CARD | 8             |     0 |
22            | PRODUCT2 | CANCEL_CARD    | 6             |     0 |
22            | PRODUCT2 | CANCEL_CARD    | 0             |     2 |
22            | PRODUCT2 | LOAD_CARD         | 1             |     0 |
22            | PRODUCT2 | REQUEST_CARD | 8             |     0 |
22            | PRODUCT3 | CANCEL_CARD    | 8             |    0 |
22            | PRODUCT3 | REQUEST_CARD | 10           |    0 |

Where as I really want the successes and failures on the same row:
Merchant | Product        | Request Type        | Success | Failure
22            | PRODUCT1 | CANCEL_CARD    | 8             |     0 |
22            | PRODUCT1 | REQUEST_CARD | 8             |     0 |
22            | PRODUCT2 | CANCEL_CARD    | 6             |    2 |
22            | PRODUCT2 | LOAD_CARD         | 1             |     0 |
22            | PRODUCT2 | REQUEST_CARD | 8             |     0 |
22            | PRODUCT3 | CANCEL_CARD    | 8             |    0 |
22            | PRODUCT3 | REQUEST_CARD | 10           |    0 |

<h1>Traffic Report</h1>
 
<% form_tag '/requests/traffic_report', :method => :get do%>
<dl>
    <dt>Merchant:</dt>
    <dd><%= select_tag(:merchant, "<option value=\"\">- Select merchant -</option>" + options_from_collection_for_select(Merchant.find(:all), :id, :name, @merchant.to_i)) %></dd>
 
    <dt>From date:</dt>
    <dd><%= calendar_date_select_tag "from_date", @from_date %></dd>
 
    <dt>To date:</dt>
    <dd><%= calendar_date_select_tag "to_date", @to_date %></dd>
 
    <dt>Request Type:</dt>
    <dd><%= select_tag(:request_type, "<option value=\"\">- Select request type -</option>" + options_from_collection_for_select(@request_types, :requestType,:requestType, @request_type)) %></dd>
 
    <dt></dt>
    <dd><%= submit_tag "Search" %></dd>
</dl>
<% end %>
 
<table border="1" style="border-collapse: collapse">
    <tr>
        <th>Merchant</th>
        <th>Product</th>
        <th>Provider</th>
        <th>Request Type</th>
        <th>Success</th>
        <th>Failure</th>
    </tr>
 
<%  if @requests.blank?
%>
    <tr>
        <td colspan="4">Sorry, there are no results</td>
    </tr>
</table>
<%
    else
        for request in @requests
%>
    <tr>
        <td><%=h Merchant.exists?(request.merchant) ? Merchant.find(request.merchant).name : "Merchant "+ request.merchant.to_s%></td>
        <td><%=h request.product  %></td>
        <td><%=h Product.exists?(request.product) ? Product.find(request.product).the_provider.name : "Unknown Provider"%></td>
        <td><%=h request.requestType  %></td>
        <td><%= request.rss == '0' ? request.total : '' %></td>
        <td><%= request.rss == '1' ? request.total : ''%></td>
    </tr>
 
<%      end %>
</table>
<%= will_paginate @requests %>
<%  end %>
 
#=========== snippet from request.rb ======================
    def self.search_traffic( from, to, request_type, merchant, page )
        conditions = self.create_conditions(from, to, request_type, merchant)
 
        Request.paginate_by_sql(["
            select merchant, product, requestType, '0' rss, count(*) total 
            from rb_request 
            where " + conditions + "
                 responseStatusCode=0
            group by merchant, product, requestType
 
            union
 
            select merchant, product, requestType, '1' rss, count(*) total 
            from rb_request 
            where " + conditions + " 
                  responseStatusCode <> 0
            group by merchant, product, requestType
 
            order by merchant, product, requestType, rss
        "], :page => page, :per_page => 10)
 
    end
 
#The above returns something that looks like in mySQL:
+----------+----------+--------------+-----+-------+
| merchant | product  | requestType  | rss | total |
+----------+----------+--------------+-----+-------+
|       22 | PRODUCT1 | CANCEL_CARD  | 0   |     8 | 
|       22 | PRODUCT1 | REQUEST_CARD | 0   |     8 | 
|       22 | PRODUCT2 | CANCEL_CARD  | 0   |     6 | 
|       22 | PRODUCT2 | CANCEL_CARD  | 1   |     2 | 
|       22 | PRODUCT2 | LOAD_CARD    | 0   |     1 | 
|       22 | PRODUCT2 | REQUEST_CARD | 0   |     8 | 
|       22 | PRODUCT3 | CANCEL_CARD  | 0   |     8 | 
|       22 | PRODUCT3 | REQUEST_CARD | 0   |    10 | 
+----------+----------+--------------+-----+-------+
The rss column is basically 1 if it's a failure count and 0 if it's a success count.

Open in new window

0
houndour
Asked:
houndour
1 Solution
 
JimOserCommented:
Your code has:
    order by merchant, product, requestType, rss

So in your view you could do something like:
  <%
    else
        i = 0
        for request in @requests
%>
        ...
<%  if ((@requests[i].merchant == @requests[i+1].merchant)
             &&
            (@requests[i].product == @requests[i+1].product)
             &&
            (@requests[i].request == @requests[i+1].total))
                   
%>
           <td>       <%=   @request[i+1].total %> </td>  <td> <%= @request[i].total %> </td>
                 i = i + 2
<% else %>
                 what you had before
                 <td><%= request.rss == '0' ? request.total : '' %></td>
        <td><%= request.rss == '1' ? request.total : ''%></td>
                 i = i + 1
<% end %>
           
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now