CODEWITHSUNDEEP
rubyruby-on-rails-3
Kobes
Kobes

Reputation: 35

Iterate through items on a given date within date range rails

I kind of have the feeling this has been asked before, but I have been searching, but cannot come to a clear description.

I have a rails app that holds items that occur on a specific date (like birthdays). Now I would like to make a view that creates a table (or something else, divs are all right as well) that states a specified date once and then iterates over the related items one by one.

Items have a date field and are, of course, not related to a date in a separate table or something.

I can of course query the database for ~30 times (as I want a representation for one months worth of items), but I think it looks ugly and would be massively repetitive. I would like the outcome to look like this (consider it a table with two columns for the time being):

Jan/1 | jan1.item1.desc
      | jan1.item2.desc
      | jan1.item3.desc
Jan/2 | jan2.item1.desc
      | etc.

So I think I need to know two things: how to construct a correct query (but it could be that this is as simple as Item.where("date > ? < ?", lower_bound, upper_bound)) and how to translate that into the view.

I have also thought about a hash with a key for each individual day and an array for the values, but I'd have to construct that like above(repetition) which I expect is not very elegant.

Using GROUP BY does not seem to get me anything different (apart from the grouping, of course, of the items) to work with than other queries. Just an array of objects, but I might do this wrong.

Sorry if it is a basic question. I am relatively new to the field (and programming in general).

Upvotes: 2

Views: 352

Answers (1)

tadman
tadman

Reputation: 211560

If you're making a calendar, you probably want to GROUP BY date:

SELECT COUNT(*) AS instances, DATE(`date`) AS on_date FROM items GROUP BY DATE(`date`)

This is presuming your column is literally called date, which seeing as how that's a SQL reserved word, is probably a bad idea. You'll need to escape that whenever it's used if that's the case, using ``` here in MySQL notation. Postgres and others use a different approach.

For instances in a range, what you want is probably the BETWEEN operator:

@items = Item.where("`date` BETWEEN ? AND ?", lower_bound, upper_bound)

Upvotes: 1

Related Questions