You have commented out the line where you get content_en from the
CONTENTS table, but you have done so _in HTML_. The template engine is
running your template, including your HTML comment, and is getting the
content_en field, just to put it in an HTML comment.
If you want to avoid the TT engine calling the relationship you must
comment the call _inside_ the TT code, i.e.:
(...)
[% FOREACH book IN books -%]
<tr>
<td>[% book.title %]</td>
*<td>[% # book.the_content.content_en %]</td>
*
</tr>
[% END -%]
(...)
And something more: if you just want a column from a relation, do a
join, not a prefetch:
(...)
sub basic_search {
my ($self) = @_;
return $self->search(
{},
{
columns => [qw/ me.title the_content.content_en /],
join => 'the_content',
});
}
(...)
A prefetch generates a JOIN but if you just want a JOIN you do not need
to prefetch, unless you want to prefetch the whole relation.
Regards
J.
El 07/01/12 13:28, Miguel Barco escribió:
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]
_______________________________________________
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]
