How to define and use many_to_many relationships in DBIx::Class?

Question

I have 3 tables in DB, simplified as that:

book        book_language       language
=====  <->> ============== <<-> ========
bookID      book_languageID     languageID
title       bookID              language
            languageID

With DBIx::Class::Schema::Loader I generated schema, where are corresponding Result classes:

Book
BookLanguage
Language

For some reasons Loader did not detect many_to_many relationship between those tables, so I defined relations myself like this in Language class:

package R::RMT::Result::Language;
...
__PACKAGE__->many_to_many('books' => 'book_language_rel', 'bookid_rel');

and in Book class:

package R::RMT::Result::Book;
...
__PACKAGE__->many_to_many('languages' => 'book_language_rel', 'languageid_rel');

Now I hoped to access all related languages as this:

my $dsn = "DBI:mysql:database=rkBook";
my $schema = R::RMT->connect( $dsn, 'user', 'pwd' );

my $book_rs = $schema->resultset('Book');
say $book_rs->languages();

But I got error:

Can't locate object method "languages" via package "DBIx::Class::ResultSet" at ...

What I got wrong? I tried clue together pieces from docs, but obviously I got something wrong. I never seen one complete example of how many_to_many relationship should work.

AFAIU, defining relationship in Result class should make an accessor in this class. How could I see all genereated accessors? If I try to dump ResultSet object with Data::Printer I see only accessors for columns, but no accessors for relationships.

If I try list relations with:

say $schema->source('Book')->relationships; 

I don't see here many_to_many relations (also not those which are picked up by DBIx::Class::Schema::Loader), only has_manys and belongs_tos.

Edit. Added simplest testcase:

Create tables and populate with data

CREATE TABLE `book` (
  `bookID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8_estonian_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`bookID`),
  KEY `title` (`title`)
) ENGINE=InnoDB;

CREATE TABLE `language` (
  `languageID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `language` varchar(255) COLLATE utf8_estonian_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`languageID`),
  KEY `language` (`language`)
) ENGINE=InnoDB;

CREATE TABLE `book_language` (
  `book_languageID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `bookID` int(10) unsigned DEFAULT NULL,
  `languageID` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`book_languageID`),
  UNIQUE KEY `book_language` (`bookID`,`languageID`),
  CONSTRAINT `book_language_ibfk_1` FOREIGN KEY (`languageID`) REFERENCES `language` (`languageID`) ON DELETE SET NULL,
  CONSTRAINT `book_language_ibfk_2` FOREIGN KEY (`bookID`) REFERENCES `book` (`bookID`) ON DELETE SET NULL
) ENGINE=InnoDB;

INSERT INTO language (language) VALUES ('estonian'), ('english'), ('polish');
INSERT INTO book (title) VALUES ('Eesti rahva ennemuistsed jutud'), ('Estonska-polska slovar'), ('21 facts about...'), ('Englis-Polish Dictionary');
INSERT INTO book_language (bookID, languageID) VALUES (1,1), (2,1), (2,3),(3,1),(3,2),(3,3),(4,2),(4,3);

Generate schema with defaults:

dbicdump -o dump_directory=./lib -o debug=1 My::Schema 'dbi:mysql:dbname=testbook' user password

Added many_to_many-definitions in Book.pm

# Created by DBIx::Class::Schema::Loader v0.07046 @ 2017-03-21 18:49:05
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:ipamXRkSe+HLXGdTGwzQ9w
__PACKAGE__->many_to_many('languages' => 'book_languages', 'languageid');

And in Language.pm

# Created by DBIx::Class::Schema::Loader v0.07046 @ 2017-03-21 18:49:05
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:nZyaWdriRpgEWDAcO3+CFw
__PACKAGE__->many_to_many('books' => 'book_languages', 'bookid');

Run this script:

#!/usr/bin/env perl

use strict; use warnings; use 5.014; use utf8::all;
use My::Schema;

my $dsn = "DBI:mysql:database=testbook";
my $schema = My::Schema->connect( $dsn, 'user', 'password' );

my $book_rs = $schema->resultset('Book');
say $book_rs->languages();

Answer 1

First note that many_to_many only generates helper methods (accessors) but are no 'real' relationships in the sense that you can't use them in join and prefetch parameters.

The problem with your sample code is that you try to call a result method (the generated 'languages') on a resultset object.

Calling $rs->first->languages for example will work.

If you want all languages of all books in your $book_rs you'd have to use search_related on the two relationships forming the many_to_many. This won't hit the database if you prefetch the two or don't fetch the rs before in which case an optimized query is built and executed.

Answer 2

Many to many relationships build on the existing "has_many" and "belongs_to" relationships. If they aren't set up correctly, the many_to_many relationships won't work either. In your situation, I'd expect DBIC::Schema::Loader to generate the following relationships:

In Book.pm

__PACKAGE__->has_many( 'book_languages'
                       'Your::DBIC::Schema::BookLanguages',
                       'bookID' );

In Language.pm

__PACKAGE__->has_many( 'book_languages'
                       'Your::DBIC::Schema::BookLanguages',
                       'languageID' );

In BookLanguage.pm

__PACKAGE__->belongs_to( 'book'.,
                         'Your::DBIC::Schema::Book' );
__PACKAGE__->belongs_to( 'language',
                         'Your::DBIC::Schema::Language' );

Then you can manually add the following:

In Book.pm

__PACKAGE->many_to_many( 'languages',
                         'book_languages',
                         'language' );

In Language.pm

__PACKAGE->many_to_many( 'books',
                         'book_languages',
                         'book' );