Welcome to the Treehouse Community
Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.
Looking to learn something new?
Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.
Start your free trial
tfurukawa
9,844 PointsQuery on joined association
I have a basic 1:M association where a "post" has many "comments". And the comments table has a "title" column.
Now, i'm trying to do a query that returns all posts where all comments associated with that post have a title of "" or nil.
I tried:
Post.joins(:comments).where(comments: { title: ['', nil] })
but this returns a post if just ONE of the comments have a blank title. That is say if a post has 3 comments and only one has a title of "" and the other two have non-blank titles, it'll still return that post. I want it so that a post is returned ONLY IF ALL 3 comments have a blank title.
I've been googling around forever but can't seem to find a solution. Any help is appreciated!!!
2 Answers
jason phillips
18,131 Pointshow about something like this:
set-up a scope in post to select the count for comments where the title is empty
then something like
Post.include(:comments).each do |p|
if p.comments.count = p.emptyTitleComments
#do something
end
end
tfurukawa
9,844 PointsThank you for your response!! But i have thousands of records so i have to do a query on the database level...
jason phillips
18,131 PointsWhat are you going to do with the data once you get what you need? If it will not be something you display on the site it might be better to do the query in sql and just work there. If it something that needs to be done periodically you can set up some kind of a job to do the work for you.
jason phillips
18,131 Pointstfurukawa : I think this is a good place to start, this is a mysql query.
select
b.idblog as blog_id,
b.title as blog_title,
b.content as blog_content,
c.idcomments as comment_id,
c.title as content_title,
c.comment as comments_comment
from comments c
inner join blog b on c.blog_id = b.idblog
inner join (
select c2.blog_id, c2.title, c2.comment
from comments c2
where c2.title is null) as c3 on c.blog_id = c3.blog_id
group by c.blog_id, c.title, c.comment
I am guessing you are trying to do an update on these empty fields:
update comments c
inner join blog b on c.blog_id = b.idblog
inner join (
select c2.blog_id, c2.title, c2.comment
from comments c2
where c2.title is null) as c3 on c.blog_id = c3.blog_id
set c.title = 'title'
I hope that helps, if you have further questions feel free to reach out to me jphillips05@gmail.com
John Magee
Courses Plus Student 9,058 PointsJohn Magee
Courses Plus Student 9,058 PointsSo - you want a post that has comments - but the length of the title of ALL those comments = 0?
A join is a SQL query at its heart, you might be doing the wrong thing.
I'm not really sure WHY one would want to do this - but i'd look into getting the length of all the titles of the comments, if it's >0 then it doesn't fit your requirement.