Forum: Ruby on Rails Generated SQl-Query using joins and default_scope

Hi there.
I hope someone can help me and give a tipp how to solve this issue.

I have three models, which you can see below (I pasted only the 
necessary
parts):

*class User
  has_many :attachments
end

class Folder
  has_many :attachments
end

class Attachment
  belongs_to :folder
  belongs_to :user
  scope :valid, lambda { where("attachments.expires_at IS NULL or
attachments.expires_at >= ?", Time.now) }
  default_scope valid
end*

Now I want to select only folders which have at least one attachment
belonging to the selected user(s).
I'm using:

*users = -List-of-Users-
Folder.joins(:attachments).where(:attachments => {:user_id =>
users}).group("folders.id").having("COUNT(DISTINCT(attachments.user_id))=#{users.size}")
*

The resulting sql-query is:

*SELECT `folders`.* FROM `folders`
  INNER JOIN `attachments` ON*
*    `attachments`.`folder_id` = `folders`.`id` AND
    attachments.expires_at IS NULL or attachments.expires_at >= 
'2012-10-15 21:56:10 +0200'*
*  WHERE `attachments`.`user_id` IN (1)
  GROUP BY folders.id
  HAVING COUNT(DISTINCT(attachments.user_id))=1

*The bold text hilights my issue.
As you can see, there is an OR-statement which renders the join on 
attachment.folder_id=folders.id useless.
That's not really what I expected.

Now my question is: Is this the normal behavior how rails should react? 
Is there a way to work around?
I already tried to get brackets around the OR-statement by using

*scope :valid, lambda { where("(attachments.expires_at IS NULL or
attachments.expires_at >= ?)", Time.now) }*
but this didn't fix this issue. Rails seems to delete them.

Any help would be very appreciated.
Greetings.

On 15 October 2012 21:08, Noxx <mailnoxx@googlemail.com> wrote:
> class Folder
>   has_many :attachments
> end
>
> class Attachment
>   belongs_to :folder
>   belongs_to :user
>   scope :valid, lambda { where("attachments.expires_at IS NULL or
> attachments.expires_at >= ?", Time.now) }

I wonder if it is confused by the fact that you have explicitly put
"attachments" in the where clause.  Does it make a difference if you
put where("expires_at IS NULL or expires_at >= ?", Time.now)

You have not told us which version of Rails you are using.

Colin

Hi Colin,

Thank you for your reply. Sorry that I forgot to mention the versions 
I'm
using:
My Ruby version is "ruby 1.9.3p194 (2012-04-20 revision 35410)
[x86_64-linux]" (installed via rvm) and I'm using Rails v3.2.8.

However, I found the problem.
I tried to test out if something changes if I delete the explicit 
mention
of "attachments." - but got the exactly same result (I mean the 
generated
sql).
Because this cannot be true I take a look again and again at my models 
and
found that I used
*:conditions => lambda { |y|
   "attachments.expires_at IS NULL or attachments.expires_at >=
'#{Time.now}'"
}*
in my Folder model.
Adding brackets to this statement fixes my issue.

I really need to find another way to sort out 
expired-but-not-yet-deleted
attachments.. those many scopes and conditions are not really a nice way 
to
go :/
But to run every minute a rake task to delete expired attachments is a
little bit too ressource-consuming, isn't it?

But, thanks for your help and time.