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 -- Posted via http://www.ruby-forum.com/.
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-- Posted via http://www.ruby-forum.com/.
For things like that, I generally resort to using find_by_sql Regards Dave M. On 13/03/06, No? Cuneo <noe.cuneo@laposte.net> 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 > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
> David MitchellI 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?> CheltisInteresting 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 <select> 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 -- Posted via http://www.ruby-forum.com/.
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.
On 13/03/06, No? Cuneo <noe.cuneo@laposte.net>
wrote:> > David Mitchell
>
> 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
> <select> 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
>
> --
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
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 > <select> 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-- Posted via http://www.ruby-forum.com/.
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. On 13/03/06, Cheltis <cheltis@gmail.com> wrote:> 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 > > <select> 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 > > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
> David MitchellOh, 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? -- Posted via http://www.ruby-forum.com/.
Sorry, terrible idea. No? Cuneo wrote:> > 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?-- Posted via http://www.ruby-forum.com/.
> 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 -- Posted via http://www.ruby-forum.com/.
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? -- Posted via http://www.ruby-forum.com/.
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 -- Posted via http://www.ruby-forum.com/.