AR: many to many with attributes

sealedsun · November 19, 2007, 6:13pm

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

sealedsun · November 19, 2007, 6:13pm

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

sealedsun · November 19, 2007, 6:30pm

On Nov 19, 11:13 am, Christian K. [email protected]
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.

sealedsun · November 21, 2007, 3:07pm

On Nov 19, 10:13 am, Christian K. [email protected]
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???

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

sealedsun · November 21, 2007, 3:12pm

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

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…

sealedsun · November 21, 2007, 1:48pm

kevin cline wrote:

On Nov 19, 11:13 am, Christian K. [email protected]
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

sealedsun · November 21, 2007, 10:52pm

On Nov 21, 7:12 am, Christian K. [email protected]
wrote:

Do your models look like this:
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?

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