Thank you, but as I stated before (this is the problem with mailing lists,
previous references are lost), mine is a one-to-one relationship, so each book
record has only one content record.
I can´t understand this behavior as an optimization, retrieving a lot of big
columns...
Even more, I cant create additional "soft" columns to get results from
calculations because the prefetch tries to retrieve them, and they do not
exists really in the db.
I case that DBIc can´t do this kind of query, can I do the query somehow and
retrieve results i a similar objet for TT?
Here is again my whole schema, with the model, the DBIC Trace, the columns,
etc. I case someone can help:
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...)
________________________________
De: neil.lunn <[email protected]>
Para: [email protected]
Enviado: martes 10 de enero de 2012 0:49
Asunto: Re: [Dbix-class] I get only one in a one to one
On 10/01/2012 8:09 AM, Miguel Barco wrote:
But the problem remains the same, because I want to retrieve some columns from
the main table an some from the related one, so I was told to use Prefetch.
Which is fine as this is basically an optimisation to avoid going back to the
database engine to call rows from the related table entry.
>
>What I really need is something as simple as this query (that I can use
>everywhere):
>
>
>SELECT books.title, contents.content_en
>FROM books, contents
>WHERE books.id = 1111
>AND contents.id = 1111
Which is more or less exactly what prefetch is doing for you. Set
DBIC_TRACE=1 on the command line before starting you app to see what
is happening.
But what you are basically missing is that your "contents" table is
the related table. Therefore for each "book" there are *many*
"contents". So not only are you iterating through the books, you
need to iterate through the "contents" as well otherwise of course
you are just accessing the first line. To see the others you call
the next one, and so on.
So basically you need to stop thinking in terms of the raw sql
results and start thinking in terms of the objects that are
represented. A "book" with many "contents" Which is what an ORM is
for._______________________________________________
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]
