Asp Forum - AR: many to many with attributes

Christian Kerth

11/19/2007 5:13:00 PM

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
--
Posted via http://www.ruby-....

6 Answers

Christian Kerth

11/19/2007 5:14:00 PM

Christian Kerth 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

--
Posted via http://www.ruby-....

kevin cline

11/19/2007 5:26:00 PM

On Nov 19, 11:13 am, Christian Kerth <christian.ke...@dynamicmedia.at>
wrote:
> Christian Kerth 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???

In your design, a book doesn't have a single title. It has a title
for each language.

Christian Kerth

11/21/2007 12:48:00 PM

kevin cline wrote:
> On Nov 19, 11:13 am, Christian Kerth <christian.ke...@dynamicmedia.at>
> 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

--
Posted via http://www.ruby-....

Phrogz

11/21/2007 2:01:00 PM

On Nov 19, 10:13 am, Christian Kerth <christian.ke...@dynamicmedia.at>
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???

1) Do your models look like this:
http://matthewman.net/2006/01/06/rails-activerecord-goe...

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

Christian Kerth

11/21/2007 2:13:00 PM

> 1) Do your models look like this:
> http://matthewman.net/2006/01/06/rails-activerecord-goe...
>
> 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...
--
Posted via http://www.ruby-....

Phrogz

11/21/2007 9:48:00 PM

On Nov 21, 7:12 am, Christian Kerth <christian.ke...@dynamicmedia.at>
wrote:
> > 1) Do your models look like this:
> >http://matthewman.net/2006/01/06/rails-activerecord-goe...
>
> @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?

> > 2) 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

comp.lang.ruby

AR: many to many with attributes

Christian Kerth

Christian Kerth

kevin cline

Christian Kerth

Phrogz

Christian Kerth

Phrogz

x Login to ForumsZone