kiz has asked for the wisdom of the Perl Monks concerning the following question:

Trying to get to grips with references and joins in DBIx

Let us assume I have the following three Schema classes set up:

A web-based service:

---- file 1 ----
package ORI::Schema::Result::Repo;
extends 'DBIx::Class::Core';
__PACKAGE__->table("repo");
__PACKAGE__->add_columns(
  "id"         => { data_type => "integer" },
  "system_url" => { data_type => "text" },
  "oaibaseurl" => { data_type => "text" },
  "lat"        => { data_type => "real" },
  "long"       => { data_type => "real" },
  "mandate"    => { data_type => "boolean" },
  "fulltext"   => { data_type => "boolean" },
  "openaccess" => { data_type => "boolean" },

);
__PACKAGE__->set_primary_key("id");
__PACKAGE__->has_many(
  "contentlinks" => "ORI::Schema::Result::Contentlink",
  { "foreign.repo_id" => "self.id" },
);
__PACKAGE__->many_to_many("contents", "contentlinks", "content");

[download]

A list of types of content the service deals with (can be multiple data types):

---- file 2 ----
package ORI::Schema::Result::Contenttype;
extends 'DBIx::Class::Core';
__PACKAGE__->table("contenttypes");
__PACKAGE__->add_columns(
  "id"   => { data_type => "integer" },
  "text" => { data_type => "text" },
);
__PACKAGE__->set_primary_key("id");
_PACKAGE__->has_many(
  "contentlinks"         => "ORI::Schema::Result::Contentlink",
  { "foreign.content_id" => "self.id" },
);
__PACKAGE__->many_to_many("repos", "contentlinks", "repo");

[download]

Finally, a table to link web services to data types:

---- file 3 ----
package ORI::Schema::Result::Contentlink;
extends 'DBIx::Class::Core';
_PACKAGE__->table("contentlinks");
__PACKAGE__->add_columns(
  "content_id" => { data_type => "integer" },
  "repo_id"    => { data_type => "integer" },
);
__PACKAGE__->set_primary_key("content_id", "repo_id");
__PACKAGE__->belongs_to(
  "content" => "ORI::Schema::Result::Contenttype",
  { id => "content_id" },
);
__PACKAGE__->belongs_to(
  "repo" => "ORI::Schema::Result::Repo",
  { id => "repo_id" },
);

[download]

So, in code (a Catalyst Controller, in my case), I can get the ORI::Schema::Result::Repo values using the Template Toolkit:

---- within controller ----
    # Get the first 10 results (getting all crashes the system! )
    $c->stash( repos => [ $c->model('ORIdatabase::Repo')->search(
                          {}
                          {
                             rows     => '10'
                          }
                        )
                      ] );

---- In Template ----
[% FOREACH repo IN repos -%]
 <tr>
    <td>[% repo.oaibaseurl %]</td>
    <td>[% repo.system_url %]</td>
    <td>[% repo.lat %]</td>
    <td>[% repo.long %]</td>
    <td>[% repo.opstatus %]</td>
    <td>[% repo.fulltext %]</td>
    <td>[% repo.mandate %]</td>
[% END -%]

[download]

.... but how do I get a list of all the data types the web-service is linked to?

---- within controller ----
    # Get the first 10 results (getting all crashes the system! )
    # Add in a join to get all the contents (ManyToMany relationship)
    $c->stash( repos => [ $c->model('ORIdatabase::Repo')->search(
                          {}
                          {
                             join     => 'contents',
                             prefetch => 'contents',
                             rows     => '10'
                          }
                        )
                      ] );

---- In Template ----
[% FOREACH repo IN repos -%]
 <tr>
    <td>[% repo.oaibaseurl %]</td>
    <td>[% repo.system_url %]</td>
    <td>[% repo.lat %]</td>
    <td>[% repo.long %]</td>
    <td>[% repo.opstatus %]</td>
    <td>[% repo.fulltext %]</td>
    <td>[% repo.mandate %]</td>
    <td>[% repo.mandate %]</td>
    <td>
 [% # How to I get the list of content types? -%]
    </td>
[% END -%]

[download]

.... however this barfs! Using the 'content' or 'contents' relationships fail, and 'contentlinks' just goes down 1 level!!

How do I get the list of text fields from ORI::Schema::Result::Contenttype, where they are linked by ORI::Schema::Result::Contentlink?



-- Ian Stuart
A man depriving some poor village, somewhere, of a first-class idiot.

Replies are listed 'Best First'.
Re: DBIx and ManyToMany Relationships
by moritz (Cardinal) on May 30, 2012 at 11:55 UTC

    My DBIx::Class knowledge is a bit rusty, but I think what you want is 

    $rs->search(
    {},
    {
        rows     => 10,
        join     => { contentlinks => 'content' },
        prefetch => { contentlinks => 'content' },
    }
);

    [download]

    (It might be that the prefetch alone is enough, and you don't need the join; I'm not quite sure).

    For constructing queries, ignore your ManyToMany -- it is not a reliationship you can use in queries (only a shortcut on the result objects), so it probably confuses you. Simply ignore it.

    Perl 6 - the future is here, just unevenly distributed
[reply]
[d/l]

      oh yeeah, you're right, I accidentally wrote { contentlinks => 'repo' } in my above reply but of course i wouldn't make sense to link back to "repo". join => { contentlinks => 'content' } is the correct path.

      I'm also never sure whether prefetch alone is sufficient. For the cases I had so far, it seems to be but then the docs always seem to include both so I do it as well to be on the safe side just in case this behaviour is changed in future version.

[reply]
Re: DBIx and ManyToMany Relationships
by tospo (Hermit) on May 30, 2012 at 09:48 UTC
    Try this: 
     $c->stash( repos => [ $c->model('ORIdatabase::Repo')->search(
                          {}
                          {
                             join     => {'contentlinks' => 'repo'},
                             prefetch => {'contentlinks' => 'repo'},
                             rows     => '10'
                          }
                        )
                      ] );

    [download]
[reply]
[d/l]

      Just thought I should also explain this: it is a common mistake to treat many-to-many accessors as a relationship accessor. Many-to-many is simply a bridge for convenience that allows you to retrieve records across a linker table without having to explicitly include the linker but you can't use it in a join or prefetch becasue it doesn't translate into a single table to join to for the SQL statement if that makes sense?
      So instead, you have to define the actual "path" from the first table you query against to all teh tables you want to include and prefetch and yuo do that with hash refs like in the snippet I posted above, which means: "join to contentlinks and from contentlinks to repo".

      What you can do with your many-to-many accessor is something like this: 
      $c->model('ORIdatabase::Repo')->search({}, {rows => '10'})->contents;

      [download]
[reply]
[d/l]

        Hmmm.... this sounds suspiciously like making multiple calls to the database, rather than gathering all the data in one query...
        (In CGI-land, I got a 25-minute sequence of Perl loops and multiple SQL calls down to one 7 second call and 8 minutes of post-processing (on 150,000 records over 18 tables))

        OK - is there a way of creating the SQL query at cole-level [based on a variety of parameters, and then passing it down to the DBIx connector to make the actual query?



        -- Ian Stuart
        A man depriving some poor village, somewhere, of a first-class idiot.
[reply]
      That gets me 
        '_column_data' => {
                      'content_id' => 11,
                      'repo_id' => 1
                    }

      [download]

      .... but not the text that relates to content_id:11

      I need to go repo.id (1) -> content_ids ([1, 3, 5, 11]) -> text ([Video, Audo, Word Processor, XML])



      -- Ian Stuart
      A man depriving some poor village, somewhere, of a first-class idiot.
[reply]
[d/l]
[select]
        I'm not sure I udnerstand what you mean with "that get's me ..." - can you post the actual code you are running now?
[reply]

Back to Seekers of Perl Wisdom