Forum: Ruby on Rails HABTM selection conditions

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Ecf21acef6f0e0d734051c7392207d2f?d=identicon&s=25 Noé Cuneo (Guest)
on 2006-03-12 22:07
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
C5b6840775639751136a320d3cb47b12?d=identicon&s=25 Cheltis (Guest)
on 2006-03-12 23:27
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/As...

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
F3dc06f587d1ff4c7366b102bfda9204?d=identicon&s=25 David Mitchell (Guest)
on 2006-03-12 23:32
(Received via mailing list)
For things like that, I generally resort to using find_by_sql

Regards

Dave M.
Ecf21acef6f0e0d734051c7392207d2f?d=identicon&s=25 Noé Cuneo (Guest)
on 2006-03-12 23:52
> 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
F3dc06f587d1ff4c7366b102bfda9204?d=identicon&s=25 David Mitchell (Guest)
on 2006-03-13 00:42
(Received via mailing list)
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.
C5b6840775639751136a320d3cb47b12?d=identicon&s=25 Cheltis (Guest)
on 2006-03-13 07:05
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/As...
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
F3dc06f587d1ff4c7366b102bfda9204?d=identicon&s=25 David Mitchell (Guest)
on 2006-03-13 07:14
(Received via mailing list)
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.
Ecf21acef6f0e0d734051c7392207d2f?d=identicon&s=25 Noé Cuneo (Guest)
on 2006-03-13 07:14
> David Mitchell

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é
C5b6840775639751136a320d3cb47b12?d=identicon&s=25 Cheltis (Guest)
on 2006-03-13 07:17
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é
Ecf21acef6f0e0d734051c7392207d2f?d=identicon&s=25 Noé Cuneo (Guest)
on 2006-03-13 07:18
> 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
Ecf21acef6f0e0d734051c7392207d2f?d=identicon&s=25 Noé Cuneo (Guest)
on 2006-03-13 07:32
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é
Ecf21acef6f0e0d734051c7392207d2f?d=identicon&s=25 Noé Cuneo (Guest)
on 2006-03-13 11:15
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
This topic is locked and can not be replied to.