Help constructing a complex DB query in rails

I’m hoping that some of you sql gurus can help me construct a relatively complex (for me anyway) query in rails. I have two relevant models, a Story model and a Post model.

class Story < ActiveRecord::Base

attr_accessible :title

  belongs_to    :user
  has_many  :posts,
        :dependent  =>  :nullify


class Post < ActiveRecord::Base

  attr_accessible :contents

  belongs_to    :story, :touch => true
  belongs_to    :user, :touch => true

Each story instance serves as a wrapper for multiple post instances. I want to be able to search the DB for stories that have posts that were created within a certain time-frame, and if there is a story that had posts created in the given time-frame, return all of the story attributes along with that story’s most recently created post (also all attributes) that was created within the time-frame. I can get the story attributes and the post_id, but I can’t figure out how to get the rest of the post attributes.

Here is what I’ve got in Post.rb:

  scope :within_user_preferred_date_range, lambda { |user| 
    where("posts.created_at BETWEEN ? AND ?", 
        (Time.now.midnight - user.preferences[:start_story_date_offset_in_days].days), 
        (Time.now - user.preferences[:end_story_date_offset_in_days].days )) } 

  #####################开发者_运维问答###############

  def self.close_timely_posts(user) 
    Post.includes(:story).within_user_preferred_date_range(user).select("DISTINCT(story_id)")   
  end

This generates the following sql query, which, obviously, isn’t exactly what I need:

←[1m←[35mPost Load (0.0ms)←[0m  SELECT DISTINCT(story_id) FROM `posts` WHERE 
(posts.created_at BETWEEN '2011-06-03 07:00:00' AND
'2011-06-06 19:34:40') ORDER BY posts.created_at DESC
  ←[1m←[36mStory Load (0.0ms)←[0m  ←[1mSELECT `stories`.* FROM `stories` WHERE (`stories`.`id` IN (70,57,53,55,16,54,51,56,52,60,59,58))←[0m

Any help you can give with this query would be greatly appreciated!

---

Try modifying your code to:

  def self.close_timely_posts(user) 
    Post.includes(:story).within_user_preferred_date_range(user).select("DISTINCT(story_id), posts.*")   
  end