Generated SQl-Query using joins and default_scope

Rails
1

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.

2

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 :confused:
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.

3

On 15 October 2012 21:08, Noxx [email protected] 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