AR and antijoin with habtm

Rails
1

Hi there !

I have a habtm relationship between books and keywords. Using the
database, books are added, deleted and modified and keywords also. For a
managing part of the website, I want to have a “find orphan” for the
keywords that are no more linked to any book.

The SQL query could be something like :

SELECT k.id
FROM keyword AS k
WHERE k.id NOT IN (
SELECT distinct keyword_id
FROM books_keywords
)

… Is there a “pretty” way to have this using ActiveRecord find method
?
Or must I use only a find_by_sql ?

Tony

2

Tony wrote:

I have a habtm relationship between books and keywords. Using the
database, books are added, deleted and modified and keywords also. For a
managing part of the website, I want to have a “find orphan” for the
keywords that are no more linked to any book.

The SQL query could be something like :

SELECT k.id
FROM keyword AS k
WHERE k.id NOT IN (
SELECT distinct keyword_id
FROM books_keywords
)

… Is there a “pretty” way to have this using ActiveRecord find method
?
Or must I use only a find_by_sql ?

I have a write-up on finding orphaned records using has_many on my blog:
http://blog.hasmanythrough.com/articles/2006/09/09/finding-unassociated-objects

You should be able to adapt that technique to work with habtm if you
expand the join clause to account for the join table.

–
Josh S.
http://blog.hasmanythrough.com

3

Josh S. wrote:

I have a write-up on finding orphaned records using has_many on my blog:
http://blog.hasmanythrough.com/articles/2006/09/09/finding-unassociated-objects

You should be able to adapt that technique to work with habtm if you
expand the join clause to account for the join table.

Thanks Josh.

In fact, I already read you’re article and find it interesting. But for
habtm, I would like not to specify the join table. And with your tip,
which is what I do from now, we have to write up the join table name.

Not a big trouble. Just that all the way learning Rails is so “magical”,
that I must forget dozens of tips and tricks. And I would like to try to
use Rails at the best. So that is why I put this question.

Tony

4

There isn’t a more ‘Railsey’ way that I’ve found, but you don’t need to
use find by SQL. You can just put the where clause in your conditions:
:conditions => [‘k.id NOT IN ( SELECT… )’ ]

Jason