# AR and antijoin with habtm

**URL:** <https://www.ruby-forum.com/t/ar-and-antijoin-with-habtm/69895>\
**Category:** Rails\
**Created:** [September 25, 2006, 10:37am UTC](https://www.ruby-forum.com/t/ar-and-antijoin-with-habtm/69895 "2006-09-25T10:37:50Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![tony](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ee7513/32.png) [@tony](https://www.ruby-forum.com/u/tony)\
**Post date:** [September 25, 2006, 10:37am UTC](https://www.ruby-forum.com/t/ar-and-antijoin-with-habtm/69895/1 "2006-09-25T10:37:50Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![tony](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ee7513/32.png) [@tony](https://www.ruby-forum.com/u/tony)\
**Post date:** [September 25, 2006, 5:38pm UTC](https://www.ruby-forum.com/t/ar-and-antijoin-with-habtm/69895/2 "2006-09-25T17:38:46Z")

</div>

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](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](http://blog.hasmanythrough.com)

---

<div class="post-metadata">

**Author:** ![tony](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ee7513/32.png) [@tony](https://www.ruby-forum.com/u/tony)\
**Post date:** [September 26, 2006, 8:00am UTC](https://www.ruby-forum.com/t/ar-and-antijoin-with-habtm/69895/3 "2006-09-26T08:00:10Z")

</div>

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](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

---

<div class="post-metadata">

**Author:** ![tony](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ee7513/32.png) [@tony](https://www.ruby-forum.com/u/tony)\
**Post date:** [September 26, 2006, 2:06pm UTC](https://www.ruby-forum.com/t/ar-and-antijoin-with-habtm/69895/4 "2006-09-26T14:06:42Z")

</div>

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