I am designing a certification model for the following purpose. A user will have a set of certifications say [A,B,C,D]. An article will have a set of certifications as well, say [B,D]. I want to be able to find articles where their certifications are some subset (read: any subset) of a user's certifications. That is, if the article were to have certs [A,B,E] it would not be returned but if it had [A] or [A,D] or whatever it would return fine. Really the way I envision doing it is taking the user.certs intersection with art.certs and ensuring the resulting set equals art.certs.size. I implemented this as a HABTM as I dont really need to access the join object, but am having trouble working out the logic. I ended up doing something like this:
@certs = @user.certifications.collect{|c| c.id}
Article.all(:joins=>:certifications,
:group=>Article.attributes_for_sql,
:select=>'articles.*',
:conditions => ['certifications.id in (?)', @certs],
:having=> "count(articles.id)=(select (count(article_id)) from articles_certifications where article_id = articles.id group by article_id)")
This seems to work but is exceedingly ugly.
In any case, how should I design this relationship to make this as easy as possible and what would an example solution look like?
Thanks for any help, I have really be开发者_Python百科en banging my head against the wall on this one.
OK, having clarified the situation a bit, here's my official attempt at an answer, :D
From what I gather, you want articles with all the certifications the user has. The certifications are the same record across models so IDs should work fine.
Why not try a simple IN statement, like this:
@user_certs = @user.certifications.all(:select => "id")
Article.all(:conditions => ["certification_id IN ?", @user_certs])
This should pull back any article that has the same certification as the user you're looking at.
I hope I've grokked your question.
The idea is to drop from query all articles which have certificates the user doesn't have. The SQL is pretty straitforward:
SELECT *
FROM articles a
WHERE NOT EXISTS
(SELECT NULL
FROM articles_certificates ac
WHERE ac.article_id = a.id AND ac.certificate_id NOT IN
(SELECT cu.certificate_id
FROM certificates_users cu INNER JOIN users u ON cu.user_id = u.id
WHERE u.id = #{user_id})
)
I'm not an expert in ActiveRelations query, so my query resembles my SQL very much :), sorry
Article.joins(:certificates).
all(:conditions => "NOT EXISTS (SELECT NULL FROM articles_certificates WHERE articles_certificates.certificate_id NOT IN ? AND articles.id = articles_certificates.article_id)", #{user.certificates})
精彩评论