How would you represent this sql query in a ActiveRecord relationship?

Question

I have a slightly complicated query that I'd like to have as a natural ActiveRecord relationship. Currently I call @calendar.events and @calendar.shared_events then join the arrays and sort them. The consolidation is able to be done with this sql:

SELECT calendar_events.* FROM calendar_events left outer join calendar_events_calendars on calendar_events_calendars.calendar_event_id = calendar_events.id where calendar_events.calendar_id = 2 or calendar_events_calendars.calendar_id = 2

but I'm not sure how to represent this as an ActiveRecord relationship. I know I could use a custom sql finder but I'd like to be able to use scopes on the results.

Currently an event belongs to one calendar and also belongs to many other calendars via a habtm join table:

  has_and_belongs_to_many :shared_events, :class_name => 'CalendarEvent', :order => 'beginning DESC, name'
  has_many :events, :class_name => 'CalendarEvent', :dependent => :destroy, :order => 'beginning DESC, name'

Any pointers would be greatly appreciated :)

Answer 1

Can you help us understand your data structure a bit more? What are you trying to achieve with the two relationships (the has_many/belongs_to and the HABTM) that you can't achieve through the HABTM or a has_many, :through? It looks to me like a simplification of your data model is likely to yield the results you're after. (sorry - not enough points or would have added as a comment)

--UPDATED AFTER COMMENT

I think that what you've suggested in your comment is an infinitely better solution. It is possible to do it how you've started implementing it, but it's unnecessarily complex - and in my experience, you can often tell when you're going down the wrong path with ActiveRecord when you start having crazy complex and duplicate relationship names.

Why not a) have it all in a has_many, through relationship (in both directions) b) have an additional field on the join table to specify that this is the main/primary attachment (or vice versa).

You can then have named scopes on the Event model for shared events, etc. which do the magic by including the condition on the specified join. This gives you:

@calendar.events #returns all events across the join
@calendar.shared_events #where the 'shared' flag on the join is set
@calendar.main_events #without the flag