Re^2: DBIx and ManyToMany Relationships

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]

Comment on Re^2: DBIx and ManyToMany Relationships Download Code

Replies are listed 'Best First'.
Re^3: DBIx and ManyToMany Relationships by kiz (Monk) on May 30, 2012 at 11:22 UTC
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]

Replies are listed 'Best First'.

Re^3: DBIx and ManyToMany Relationships
by kiz (Monk) on May 30, 2012 at 11:22 UTC

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]