Mike Wiesenhart Mike Wiesenhart - 12 days ago 4
SQL Question

Using .where in Rails Controller Method

I have a Statistics page and currently have an

Items Bought
and an
Items Sold
table. The
Items Bought
table is working just fine, however I am having some issues with the
Items Sold
table. There is a boolean in my DB for
SOLD
and I want the count to display for all those items marked as
TRUE
. The current
.where
statements have date statements as well and I'm not sure how to incorporate both of them with it erroring out.

items_controller:

def statistics
@items = Item.all
@items_day = Item.all.where('created_at >= ? AND created_at <= ?', Time.zone.now.beginning_of_day, Time.zone.now.end_of_day)
@items_week = Item.all.where('created_at >= ? AND created_at <= ?', Time.zone.now.beginning_of_week, Time.zone.now.end_of_week)
@items_month = Item.all.where('created_at >= ? AND created_at <= ?', Time.zone.now.beginning_of_month, Time.zone.now.end_of_month)

@items_sold = Item.all.where(:sold => true)
@items_sold_day = Item.all.where(:sold => true 'created_at >= ? AND created_at <= ?', Time.zone.now.beginning_of_day, Time.zone.now.end_of_day)
@items_sold_week = Item.all.where(:sold => true 'created_at >= ? AND created_at <= ?', Time.zone.now.beginning_of_week, Time.zone.now.end_of_week)
@items_sold_month = Item.all.where(:sold => true 'created_at >= ? AND created_at <= ?', Time.zone.now.beginning_of_month, Time.zone.now.end_of_month)
end


statistics.html.erb

<h3 id="subtitle">Items Bought</h3>
<table align="center" style="width: 95%" class="table table-striped table-bordered">
<thead>
<tr>
<th>Total</th>
<th>Today</th>
<th>Week</th>
<th>Month</th>
</tr>
</thead>

<tbody>
<tr>
<td><%= number_with_delimiter(@items.count) %></td>
<td><%= @items_day.count %></td>
<td><%= @items_week.count %></td>
<td><%= @items_month.count %></td>
</tr>
</tbody>
</table>

<h3 id="subtitle">Items Sold</h3>
<table align="center" style="width: 95%" class="table table-striped table-bordered">
<thead>
<tr>
<th>Total</th>
<th>Today</th>
<th>Week</th>
<th>Month</th>
</tr>
</thead>

<tbody>
<tr>
<td><%= number_with_delimiter(@items_sold.count) %></td>
<td><%= @items_sold_day.count %></td>
<td><%= @items_sold_week.count %></td>
<td><%= @items_sold_month.count %></td>
</tr>
</tbody>
</table>

Answer

You can chain several where statements:

Item.where(:sold => true).where('created_at >= ? AND created_at <= ?', Time.zone.now.beginning_of_day, Time.zone.now.end_of_day)
Comments