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.
--
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To view this discussion on the web visit
https://groups.google.com/d/msg/rubyonrails-talk/-/sxrH9_YQfNMJ.
For more options, visit https://groups.google.com/groups/opt_out.
On 15 October 2012 21:08, Noxx <mailnoxx-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org> wrote:> 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) }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> 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. > > -- > 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to > rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To view this discussion on the web visit > https://groups.google.com/d/msg/rubyonrails-talk/-/sxrH9_YQfNMJ. > For more options, visit https://groups.google.com/groups/opt_out. > >-- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit https://groups.google.com/groups/opt_out.
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.
On Tuesday, October 16, 2012 4:55:10 PM UTC+2, Colin Law
wrote:>
> On 15 October 2012 21:08, Noxx
<mail...-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org <javascript:>>
> wrote:
> > 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) }
>
> 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
>
> > 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.
> >
>
--
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To view this discussion on the web visit
https://groups.google.com/d/msg/rubyonrails-talk/-/oj8a84k7LAoJ.
For more options, visit https://groups.google.com/groups/opt_out.