http://www.perlmonks.org?node_id=973239


in reply to DBIx and ManyToMany Relationships

Try this: 
 $c->stash( repos => [ $c->model('ORIdatabase::Repo')->search(
                          {}
                          {
                             join     => {'contentlinks' => 'repo'},
                             prefetch => {'contentlinks' => 'repo'},
                             rows     => '10'
                          }
                        )
                      ] );

[download]

Replies are listed 'Best First'.
Re^2: DBIx and ManyToMany Relationships
by tospo (Hermit) on May 30, 2012 at 10:09 UTC

    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]
Re^2: DBIx and ManyToMany Relationships
by kiz (Monk) on May 30, 2012 at 10:43 UTC
    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]
        (Noting the comment below)

        The search model is currently:

          $c->stash( repos => [ $c->model('ORIdatabase::Repo')
                            ->search(
                    {}, #$what,
                    {
                       join     => {'contentlinks' => 'content'} ,
                       prefetch => {'contentlinks' => 'content'} ,
                       rows     => $params->{'limit'}
                    }
                              )
                      ] 
           );

        [download]

        And buried at the end of the hash/object returned are the data fields the query returns...



        -- Ian Stuart
        A man depriving some poor village, somewhere, of a first-class idiot.
[reply]
[d/l]

In Section Seekers of Perl Wisdom