Hello List
I have a Milestone model as follows:
class Milestone < ActiveRecord::Base
belongs_to :goal
belongs_to :user
has_many :progresses, :order => ''created_at DESC''
has_many :views
Which has an association with a model called views:
class View < ActiveRecord::Base
set_primary_keys :milestone_id, :user_id
belongs_to :user
belongs_to :milestone
The logic goes as follows:
Each milestone belongs only to one user, but may be viewed by many
users. The views are there to track when last a user viewed a milestone
(whether the milestone belonged to the user viewing it, or to another
user). When the user goes to the "milestones" UI, I want to display
all
milestones (including those that haven''t been viewed by the user at
all). I also want to track when last, if at all, the currently logged in
user has viewed each milestone.
Because I will have many milestones and many users, it seems rather
wasteful to pull all the views for each milestone, and then loop through
the views looking for a view belonging to the current user. What I would
like to do is pull all the milestones, plus any views for the currently
logged in user (the @user object stores the logged in user''s details).
In raw SQL, this translates to something like:
SELECT milestones.name, views.view_time
FROM milestones LEFT JOIN views ON views.milestone_id = milestones.id
WHERE views.user_id = #{@user.id} /* Currently logged in user */
I would prefer to avoid using the raw SQL avenue if possible, so does
anybody have any suggestions as to how to achieve this with Rails?
Regards
Rory
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
> In raw SQL, this translates to something like: > > SELECT milestones.name, views.view_time > FROM milestones LEFT JOIN views ON views.milestone_id = milestones.id > WHERE views.user_id = #{@user.id} /* Currently logged in user */ >Create the view in the database, and attach a model to it. From the Rails perspective, it''s just another model. migration: self.up sql = "CREATE OR REPLACE VIEW `milestoneview` AS SELECT" sql += "`m`.`milestone_id` AS `milestone_id`" sql += "`v`.`view_time` AS `view_time`" # add whatever other fields may be useful sql += "FROM milestones m" sql += "LEFT JOIN views v ON v.milestone_id = m.id" execute sql end save the WHERE to be applied in the Rails app as an adder to your find method just like a regular table model: class Milestoneview < ActiveRecord::Base belongs_to :goal belongs_to :user has_many :progresses, :foreign_key => ''milestone_id'', :order => ''created_at DESC'' has_many :views, :foreign_key => ''milestone_id'' end and off you go (or something close to the above) -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Ar Chron wrote:> >> In raw SQL, this translates to something like: >> >> SELECT milestones.name, views.view_time >> FROM milestones LEFT JOIN views ON views.milestone_id = milestones.id >> WHERE views.user_id = #{@user.id} /* Currently logged in user */ >> > > Create the view in the database, and attach a model to it. From the > Rails perspective, it''s just another model.<snip> Thanks Ar That was a route I hadn''t thought of, and definitely one that I will consider for both this problem and future problems of a similar nature. As a quick fix, I decided just to split the views out of the model, and retrieve them on their own using View.find. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---