Hi, I tried to isolate the problem with a small application to see if someone
can help me.
I started with the Catalyst tutorial, adding just the special elements. The
problem remains the same.
I have added a new table: contents, to store additional contents for the
tutorial table books. So we have two tables:
CREATE TABLE IF NOT EXISTS `books` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` text,
`rating` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `contents` (
`id` int(12) NOT NULL,
`Content_EN` longtext NOT NULL,
`Content_ES` longtext NOT NULL,
`Pictures` blob NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
In Schema/Result/Book.pm I have added the relationship between books and
contents
__PACKAGE__->might_have(
"the_content",
"MyApp::Schema::Result::Content",
{ "foreign.id" => "self.id" },
);
And a ResultSet for my test: Schema/ResultSet/Book.pm with the search that I
will use from the Controller
I have reduced the columns just to 'title' from books and 'content_en' from
contents
sub basic_search {
my ($self) = @_;
return $self->search(
{},
{
columns => [qw/ me.title the_content.content_en /],
prefetch => 'the_content',
});
}
In the template I tried commenting and uncommenting the cell that calls
'content_en' column
<table>
<tr><th>Title</th><th>Rating</th><th>Author(s)</th></tr>
[% # Display each book in a table row %]
[% FOREACH book IN books -%]
<tr>
<td>[% book.title %]</td>
<!-- <td>[% book.the_content.content_en %]</td> -->
</tr>
[% END -%]
</table>
So, in the Controller I call my "basic_search" :
sub list :Local {
my ($self, $c) = @_;
$c->stash(books => [$c->model('DB::Book')->basic_search()]);
$c->stash(template => 'books/list.tt2');
}
And in the end, this is what I get in the debug window:
- At first it SELECTs me.title, the_content.content_en
fine
- But then it adds all the columns from the prefetched table!!!!
[info] MyApp powered by Catalyst 5.90007
HTTP::Server::PSGI: Accepting connections at http://0:3000/
SELECT me.title, the_content.content_en, the_content.id,
the_content.content_en, the_content.content_es, the_content.pictures FROM books
me LEFT JOIN contents the_content ON the_content.id = me.id:
[info] *** Request 1 (0.000/s) [24182] [Sat Jan 7 12:31:47 2012] ***
[debug] Path is "books/list"
[debug] "GET" request for "books/list" from "88.23.7.72"
[debug] Rendering template "books/list.tt2"
[debug] Response Code: 200; Content-Type: text/html; charset=utf-8;
Content-Length: 1564
[info] Request took 0.143192s (6.984/s)
.------------------------------------------------------------+-----------.
| Action | Time |
+------------------------------------------------------------+-----------+
| /books/list | 0.088956s |
| /end | 0.048612s |
| -> MyApp::View::HTML->process | 0.047493s |
'------------------------------------------------------------+-----------'
So I found the very same problem that I have in my real application: unwanted
(and very BIG) columns are called in each request. I can´t see what is calling
them.
As I stated some emails ago, the real db has an ancient inherited structure
that can't be changed, (and I think that splitting text and blob columns from
the main table is not a strange practice...)
Any help will be really appreciated. I am stuck. Regards:
Migue
________________________________
De: Jess Robinson <[email protected]>
Para: Miguel Barco <[email protected]>; DBIx::Class user and developer
list <[email protected]>
Enviado: martes 3 de enero de 2012 17:53
Asunto: Re: [Dbix-class] I get only one in a one to one
It's hard to debug your problem with only tiny snippets of the code.
Somewhere in your code or your template you are using the relationship
without the columns restriction or just by calling it via the relationship
method.
Can you reply including all the relevant bits of code, instead of just
tiny parts?
Or start debugging by using a very minimal app or script, and find out
yourself which piece of code is causing the SQL statements which fetch all
the columns.
DBIC does what you ask it, mostly..
Jess_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[email protected]
