2

I have a simple calendar application.

Models:

class Day < ActiveRecord::Base self.primary_key = "sdate" has_many :items, foreign_key: :sdate end class Item < ActiveRecord::Base belongs_to :day, :foreign_key => :sdate belongs_to :calendar end class Calendar < ActiveRecord::Base has_many :items end

Requirements: Fetch ALL rows of days within a certain date range. Fetch associated rows of items with certain Calendar-IDs.

With SQL I could use the following query:

SELECT * FROM days LEFT OUTER JOIN ( SELECT * FROM items WHERE calendar_id IN (1, 4) ) AS items ON days.sdate = items.sdate WHERE days.sdate BETWEEN '2015-03-01' AND '2015-03-31';

I have tried several ways in Rails but I couldn't find a solution.

How can I do this in Rails 4? Eager loading would be great.

Improve this question
1
  • those sound like 2 different requirements, are they ? Commented Apr 4, 2015 at 10:14

2 Answers 2

Reset to default
2

I was just trying to figure this out and I don't know of any way to do it with pure Rails. But this will do what you want:

Day.joins("LEFT OUTER JOIN ( SELECT * FROM items WHERE calendar_id IN (1, 4) ) AS items ON days.sdate = items.sdate WHERE days.sdate BETWEEN '2015-03-01' AND '2015-03-31'");

I'm not using dates in the query in my code, so I'm not sure if you need the single quotes around the dates or not. But that is the syntax. I think Left joins are very handy, I wish Rails had an actual LEFT JOIN method.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thank you Beartech! I will try it later today.
Sorry for being late! I will try it within the next two days.
The solution works. But unfortunately while accessing items it produces a lot of additional database queries.
I see that with Rails a lot. Or at least it looks like a lot of queries, because Rails doesn't do a lot of eager loading. You are getting an ActiveRecord::Relation here, not just the results of a query. If you are concerned about DB queries, then the proper thing to do is create a database view using this query. Complex queries are the domain of the DB, and it is what the DB is designed to do. If you basically want a temporary table to see all of this query at once in one call, a DB view is the way to go. I'd appreciate it being marked as the correct answer unless a better one shows up.
Thank you for suggesting database views! Using a view could be a suitable solution.
0

Something along these lines:

Day.joins('LEFT OUTER JOIN items ON days.sdate = items.sdate') .where('items.calendar_id IN (1, 4)') .where('days.sdate' => start_time..end_time)

http://guides.rubyonrails.org/active_record_querying.html

Improve this answer

4 Comments

Thank you for your proposal! Unfortunately there is one problem: If there would be a day with just one item with calendar_id 3, this day would be missing in the results.
Another problem: days without items are missing too.
Try running it in the console with .to_sql and tweaking it to produce the query you want.
Relation#to_sql returns an SQL statement. But I already have an SQL statement. So I don't understand why I should do this.