# AR: many to many with attributes

**URL:** <https://www.ruby-forum.com/t/ar-many-to-many-with-attributes/120671>\
**Category:** Ruby\
**Created:** [November 19, 2007, 6:13pm UTC](https://www.ruby-forum.com/t/ar-many-to-many-with-attributes/120671 "2007-11-19T18:13:10Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![sealedsun](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/e68b1a/32.png) [@sealedsun](https://www.ruby-forum.com/u/sealedsun)\
**Post date:** [November 19, 2007, 6:13pm UTC](https://www.ruby-forum.com/t/ar-many-to-many-with-attributes/120671/1 "2007-11-19T18:13:10Z")

</div>

Hey i have the following SQL Database Structure:

| books |
| --- |

id  
author

| books\_languages |
| --- |

book\_id  
language\_id  
book\_title

| languages |
| --- |

languages\_id  
langcode

I have ActiveRecord set up with has\_many through and it sheems to work.

But how do i query the book\_title of a certain book???

e.g. input is the id of a book and i want the corresponding title

---

<div class="post-metadata">

**Author:** ![sealedsun](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/e68b1a/32.png) [@sealedsun](https://www.ruby-forum.com/u/sealedsun)\
**Post date:** [November 19, 2007, 6:13pm UTC](https://www.ruby-forum.com/t/ar-many-to-many-with-attributes/120671/2 "2007-11-19T18:13:53Z")

</div>

Christian K. wrote:

> Hey i have the following SQL Database Structure:
> 
> | books |
> | --- |
> 
> id  
> author
> 
> | books\_languages |
> | --- |
> 
> book\_id  
> language\_id  
> book\_title
> 
> | languages |
> | --- |
> 
> languages\_id  
> langcode
> 
> I have ActiveRecord set up with has\_many through and it sheems to work.
> 
> But how do i query the book\_title of a certain book???
> 
> e.g. input is the id of a book and i want the corresponding title

These are the correct tables:

| books |
| --- |

id  
author

| books\_languages |
| --- |

book\_id  
language\_id  
book\_title

| languages |
| --- |

id  
langcode

---

<div class="post-metadata">

**Author:** ![sealedsun](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/e68b1a/32.png) [@sealedsun](https://www.ruby-forum.com/u/sealedsun)\
**Post date:** [November 19, 2007, 6:30pm UTC](https://www.ruby-forum.com/t/ar-many-to-many-with-attributes/120671/3 "2007-11-19T18:30:53Z")

</div>

On Nov 19, 11:13 am, Christian K. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
wrote:

> > book\_id  
> > But how do i query the book\_title of a certain book???  
> > In your design, a book doesn’t have a single title. It has a title  
> > for each language.

---

<div class="post-metadata">

**Author:** ![sealedsun](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/e68b1a/32.png) [@sealedsun](https://www.ruby-forum.com/u/sealedsun)\
**Post date:** [November 21, 2007, 3:07pm UTC](https://www.ruby-forum.com/t/ar-many-to-many-with-attributes/120671/4 "2007-11-21T15:07:35Z")

</div>

On Nov 19, 10:13 am, Christian K. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
wrote:

> language\_id  
> book\_title
> 
> | languages |
> | --- |
> | languages\_id |
> | langcode |
> 
> I have ActiveRecord set up with has\_many through and it sheems to work.
> 
> But how do i query the book\_title of a certain book???

1. Do your models look like this:  
[http://matthewman.net/2006/01/06/rails-activerecord-goes-through/](http://matthewman.net/2006/01/06/rails-activerecord-goes-through/)

2. This is a rails question, not a Ruby one. Try the rails list.

---

<div class="post-metadata">

**Author:** ![sealedsun](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/e68b1a/32.png) [@sealedsun](https://www.ruby-forum.com/u/sealedsun)\
**Post date:** [November 21, 2007, 3:12pm UTC](https://www.ruby-forum.com/t/ar-many-to-many-with-attributes/120671/5 "2007-11-21T15:12:46Z")

</div>

> 1. Do your models look like this:  
> [http://matthewman.net/2006/01/06/rails-activerecord-goes-through/](http://matthewman.net/2006/01/06/rails-activerecord-goes-through/)
> 
> 2. This is a rails question, not a Ruby one. Try the rails list.

@1) yeah, i have set up the models with :through associations. The all  
work.  
I just want to know the best way (smallest number of actual sql  
queries?) to query e.g. the title for a certain book in a certain  
language.

@2) Why is this a Rails question? I’m using ActiveRecord without  
Rails…

---

<div class="post-metadata">

**Author:** ![sealedsun](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/e68b1a/32.png) [@sealedsun](https://www.ruby-forum.com/u/sealedsun)\
**Post date:** [November 21, 2007, 1:48pm UTC](https://www.ruby-forum.com/t/ar-many-to-many-with-attributes/120671/6 "2007-11-21T13:48:05Z")

</div>

kevin cline wrote:

> On Nov 19, 11:13 am, Christian K. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
> wrote:
> 
> > > book\_id  
> > > But how do i query the book\_title of a certain book???  
> > > In your design, a book doesn’t have a single title. It has a title  
> > > for each language.

I know, this is just an example.

But what is best practice to query attributes of a many to many  
relation?

In that case the title of a certain book in a certain language.

Thx Ck

---

<div class="post-metadata">

**Author:** ![sealedsun](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/e68b1a/32.png) [@sealedsun](https://www.ruby-forum.com/u/sealedsun)\
**Post date:** [November 21, 2007, 10:52pm UTC](https://www.ruby-forum.com/t/ar-many-to-many-with-attributes/120671/7 "2007-11-21T22:52:39Z")

</div>

On Nov 21, 7:12 am, Christian K. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
wrote:

> > 1. Do your models look like this:  
> > [http://matthewman.net/2006/01/06/rails-activerecord-goes-through/](http://matthewman.net/2006/01/06/rails-activerecord-goes-through/)
> 
> @1) yeah, i have set up the models with :through associations. The all  
> work.

My question, which I didn’t explain well, was: did you create a Model  
for the many-to-many table?

> > 1. This is a rails question, not a Ruby one. Try the rails list.
> 
> @2) Why is this a Rails question? I’m using ActiveRecord without  
> Rails…

Ah, OK then. As I suspect you know, most people using AR are using  
Rails. My apologies for the assumption. Nonetheless, given that fact,  
that list seems most likely to have people able to answer your  
question.

…if this doesn’t answer it:

C:\tmp\db\>sqlite3 development.sqlite3  
SQLite version 3.5.2  
Enter “.help” for instructions  
sqlite\> .dump  
BEGIN TRANSACTION;  
CREATE TABLE schema\_info (version integer);  
INSERT INTO “schema\_info” VALUES(3);  
CREATE TABLE books (“id” INTEGER PRIMARY KEY NOT NULL, “author”  
varchar(255) DEFAULT NULL);  
CREATE TABLE languages (“id” INTEGER PRIMARY KEY NOT NULL, “langcode”  
varchar(255) DEFAULT NULL);  
CREATE TABLE book\_publishings (“book\_id” integer DEFAULT NULL,  
“language\_id” integer DEFAULT NULL, “title” varchar(255) DEFAULT  
NULL);  
COMMIT;

C:\tmp\app\models\>type \*.rb

book.rb  
class Book \< ActiveRecord::Base  
has\_many :book\_publishings  
has\_many :languages, :through=\>:book\_publishings  
end

book\_publishing.rb  
class BookPublishing \< ActiveRecord::Base  
belongs\_to :book  
belongs\_to :language  
end

language.rb  
class Language \< ActiveRecord::Base  
has\_many :book\_publishings  
has\_many :books, :through=\>:book\_publishings  
end

c:\tmp\> ruby script\console  
Loading development environment.

> > cat\_book = Book.create :author=\>‘me’  
> > =\> #\<Book:0x474355c @errors=#\<ActiveRecord::Errors:0x46bcb9c  
> > @errors={}, @base=#\<Book:0x474355c …\>\>,  
> > @new\_record\_before\_save=true, @attributes={“author”=\>“me”, “id”=\>1},  
> > @new\_record=false\>

> > english = Language.create :langcode=\>‘en’  
> > =\> #\<Language:0x46a6900 @errors=#\<ActiveRecord::Errors:0x46a41c8  
> > @errors={}, @base=#\<Language:0x46a6900 …\>\>,  
> > @new\_record\_before\_save=true, @attributes={“id”=\>1, “langcode”=\>“en”},  
> > @new\_record=false\>

> > german = Language.create :langcode=\>‘de’  
> > =\> #\<Language:0x469fe84 @errors=#\<ActiveRecord::Errors:0x469f754  
> > @errors={}, @base=#\<Language:0x469fe84 …\>\>,  
> > @new\_record\_before\_save=true, @attributes={“id”=\>2, “langcode”=\>“de”},  
> > @new\_record=false\>

> > BookPublishing.create :book=\>cat\_book, :language=\>english, :title=\>‘My Cat is Awesome’

=\> #\<BookPublishing:0x4688608 @book=#\<Book:0x474355c  
@errors=#\<ActiveRecord::Errors:0x46bcb9c @errors={}, @base=#\<Book:  
0x474355c …

> > , @new\_record\_before\_save=true, @attributes={“author”=\>“me”, “id”=\>1}, @new\_record=false\>, @errors=#\<ActiveRecord::Errors:0x4684ec  
> > c @errors={}, @base=#\<BookPublishing:0x4688608 …\>\>,  
> > @language=#\<Language:0x46a6900 @errors=#\<ActiveRecord::Errors:  
> > 0x46a41c8 @error  
> > s={}, @base=#\<Language:0x46a6900 …\>\>, @new\_record\_before\_save=true,  
> > @attributes={“id”=\>1, “langcode”=\>“en”}, @new\_record=false\>, @  
> > attributes={“title”=\>“My Cat is Awesome”, “id”=\>1, “language\_id”=\>1,  
> > “book\_id”=\>1}, @new\_record=false\>

> > BookPublishing.create :book=\>cat\_book, :language=\>german, :title=\>‘Meine Katze ist genial’  
> > =\> #\<BookPublishing:0x467e158 @book=#\<Book:0x474355c  
> > @errors=#\<ActiveRecord::Errors:0x46bcb9c @errors={}, @base=#\<Book:  
> > 0x474355c …\>\>, @new\_record\_before\_save=true,  
> > @attributes={“author”=\>“me”, “id”=\>1}, @new\_record=false\>,  
> > @errors=#\<ActiveRecord::Errors:0x467d5c8 @errors={},  
> > @base=#\<BookPublishing:0x467e158 …\>\>, @language=#\<Language:0x469fe84  
> > @errors=#\<ActiveRecord::Errors:0x469f754 @errors={}, @base=#\<Language:  
> > 0x469fe84 …\>\>, @new\_record\_before\_save=true, @attributes={“id”=\>2,  
> > “langcode”=\>“de”}, @new\_record=false\>, @attributes={“title”=\>“Meine  
> > Katze ist genial”, “id”=\>2, “language\_id”=\>2, “book\_id”=\>1},  
> > @new\_record=false\>

> > cat\_book.languages.map &:langcode  
> > =\> [“en”, “de”]

> > cat\_book.book\_publishings.map &:title  
> > =\> [“My Cat is Awesome”, “Meine Katze ist genial”]

> > cat\_book.book\_publishings.find\_by\_language\_id( english.id )  
> > =\> #\<BookPublishing:0x4668038 @attributes={“title”=\>“My Cat is  
> > Awesome”, “language\_id”=\>“1”, “book\_id”=\>“1”}\>

Note that due to the crazy magic of AR, that last line is a single SQL  
query:  
SELECT \* FROM book\_publishings  
WHERE (book\_publishings.book\_id = 1) AND  
(book\_publishings.“language\_id” = 1)  
LIMIT 1