Hi,
I am currently trying to learn RoR and there is something I cannot
figure out.
I have two models: Article and Category, that are HABTM-linked.
Everything works fine if I want to select all articles corresponding to
a category, with something like
@articles = @category.find(@params[:id]).articles
However, I have a ‘published’ field in my ‘articles’ table, which is ‘1’
if the article is to be displayed. What I would like is a query that
will return all articles corresponding to one particular category, AND
whose ‘published’ value is ‘1’. And I have absolutely no idea how to do
such a thing…
In other words, I would like to find a way of mixing conditions on the
category and conditions on the article itself…
I hope someone will be able to help me soon 
Thank you
Noé Cuneo
You can use something like this in your model
has_and_belongs_to_many :categories, :conditions => ‘published=1’
for more info -
http://api.rubyonrails.com/classes/ActiveRecord/Associations/ClassMethods.html#M000474
Noé Cuneo wrote:
Hi,
I am currently trying to learn RoR and there is something I cannot
figure out.
I have two models: Article and Category, that are HABTM-linked.
Everything works fine if I want to select all articles corresponding to
a category, with something like
@articles = @category.find(@params[:id]).articles
However, I have a ‘published’ field in my ‘articles’ table, which is ‘1’
if the article is to be displayed. What I would like is a query that
will return all articles corresponding to one particular category, AND
whose ‘published’ value is ‘1’. And I have absolutely no idea how to do
such a thing…
In other words, I would like to find a way of mixing conditions on the
category and conditions on the article itself…
I hope someone will be able to help me soon 
Thank you
Noé Cuneo
For things like that, I generally resort to using find_by_sql
Regards
Dave M.
David M.
I would like to use find_by_sql only on last-hope-case if you see what I
mean, but if it is the only possibility, could you give me a hint on
what SQL query to use?
Cheltis
Interesting approach… However, my real situation is a little bit more
complex. I gave the ‘published=1’ as an example, but there are more
conditions.
For example, what if I want to combine a LIKE condition and a condition
on the category? The research part should be managed by the controller,
not by the model. I believe that your approach is only suitable if you
have just one never-changing condition, which is not necessarely the
case here.
In other words, what if I would like my user to pick a category in a
input and a searched string? How can the controller retrieve
all articles bound to the chosen category with the join table and whose
title match the string?
Thank you very much
Noé Cuneo
OK, this now looks different to original question. In this case “find”
is your friend.
Article.find(:all, :include => :categories, :conditions =>
‘categories.status =1 and articles.published=1’)
http://api.rubyonrails.com/classes/ActiveRecord/Associations/ClassMethods.html
check “Eager loading of associations” section
Noé Cuneo wrote:
Cheltis
Interesting approach… However, my real situation is a little bit more
complex. I gave the ‘published=1’ as an example, but there are more
conditions.
For example, what if I want to combine a LIKE condition and a condition
on the category? The research part should be managed by the controller,
not by the model. I believe that your approach is only suitable if you
have just one never-changing condition, which is not necessarely the
case here.
In other words, what if I would like my user to pick a category in a
input and a searched string? How can the controller retrieve
all articles bound to the chosen category with the join table and whose
title match the string?
Thank you very much
Noé Cuneo
Agreed - now that I think about it, that’s the way I’d do it as well
Just proves how tough it is to describe a possible solution when the
question posed isn’t sufficiently detailed.
Regards
Dave M.
If you decide to use find_by_sql, your controller code would probably
look something like this:
def published_articles(category_name)
Article.find_by_sql(“select * from articles where id in
(select article_id from articles_categories
where published = ‘1’ and category_id in
(select id from categories where category_name = ?))”,
category_name)
end
Can’t be 100% sure that works since I don’t have Ruby or Rails docs at
my fingertips at the moment.
It’s a big ugly SQL statement, but it’s “standard” SQL so should work
on any database and you can put a few comments around it saying what
it does. It’s also a single line of code in a function, which should
be relatively easy to support.
I’ll be interested if you come up with a more elegant solution.
Regards
Dave M.
David M.
Oh, this is huge SQL…
I’ll be interested if you come up with a more elegant solution.
However, the night gave me an idea… What about keeping a sort of index
in a ‘categories_ids_string’ field that would contain for example “1, 2,
7, 12” and that would be updated everytime the row is edited. With this
kind of solution, it would be very easy to search for an article with a
"categories_ids_string LIKE ‘%id, %’, or something like this? What do
you thing?
Thank you!
Noé
Just proves how tough it is to describe a possible solution when the
question posed isn’t sufficiently detailed.
Indeed, I am sorry. I tried to simplify the problem so as to make the
question as understandable as possible (English is not my primary
language as you can see), and I now understand that it was not such a
good idea…
I’ll try Cheltis’ :include (thank you!) and keep you informed!
Noé Cuneo
Me again 
I am now trying to use the :include with the paginate method, and it
does not work properly… I suspect it is a problem with RoR
My code looks like this:
@article_pages, @articles = paginate :articles, :per_page => 3, :include
=>:categories, :conditions =>“categories.id=1 AND articles.titre LIKE
‘%u%’”
This generates a count query which does not include the LEFT OUTER JOINs
as expected:
SELECT COUNT(*) FROM articles WHERE (categories.id=1 AND articles.titre
LIKE ‘%u%’)
Which of course makes an error, instead of the expected query
SELECT COUNT( * )
FROM articles
LEFT OUTER JOIN articles_categories ON articles_categories.article_id =
articles.id
LEFT OUTER JOIN categories ON articles_categories.category_id =
categories.id
WHERE (
categories.id =1
AND articles.titre LIKE ‘%u%’
)
And this request works… Is it a problem in RoR? Or am I missing
something?
Thank you for your most precious help!
Noé Cuneo
Ok, I am going to be late for work, but I have just tried the :include,
and it WORKS!
Thank you all so much! For one second, I thought RoR was not so good in
the end
Have a nice day
Noé
