[Dbix-class] I get only one in a one to one

[Miguel Barco]

Hi, I tried to isolate the problem with a small application to see if someo=
ne 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 tu=
torial table books. So we have two tables:

CREATE TABLE IF NOT EXISTS `books` (
=A0 `id` int(11) NOT NULL AUTO_INCREMENT,
=A0 `title` text,
=A0 `rating` int(11) DEFAULT NULL,
=A0 PRIMARY KEY (`id`)
) ENGINE=3DInnoDB =A0DEFAULT CHARSET=3Dutf8;


CREATE TABLE IF NOT EXISTS `contents` (
=A0 `id` int(12) NOT NULL,
=A0 `Content_EN` longtext NOT NULL,
=A0 `Content_ES` longtext NOT NULL,
=A0 `Pictures` blob NOT NULL,
=A0 PRIMARY KEY (`id`)
) ENGINE=3DInnoDB DEFAULT CHARSET=3Dutf8;



In Schema/Result/Book.pm I have added the relationship between books and co=
ntents

__PACKAGE__->might_have(
=A0 "the_content",
=A0 "MyApp::Schema::Result::Content",
=A0 { "foreign.id" =3D> "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=A0

sub basic_search {
my ($self) =3D @_;

return $self->search(
{},
{
columns =3D> [qw/ me.title the_content.content_en /],
prefetch =3D> 'the_content',
});
}


In the template I tried commenting and uncommenting the cell that calls 'co=
ntent_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 -%]
=A0 <tr>
<td>[% book.title %]</td>
<!-- =A0 =A0 <td>[% book.the_content.content_en %]</td> -->
=A0 </tr>
[% END -%]
</table>



So, in the Controller I call my "basic_search" :

sub list :Local {
=A0 =A0 my ($self, $c) =3D @_;
$c->stash(books =3D> [$c->model('DB::Book')->basic_search()]);
=A0 =A0 $c->stash(template =3D> '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.conten=
t_en, the_content.content_es, the_content.pictures FROM books me LEFT JOIN =
contents the_content ON the_content.id =3D me.id:=A0
[info] *** Request 1 (0.000/s) [24182] [Sat Jan =A07 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=3Dutf-8; Conte=
nt-Length: 1564
[info] Request took 0.143192s (6.984/s)
.------------------------------------------------------------+-----------.
| Action =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | Time =A0 =A0 =A0|
+------------------------------------------------------------+-----------+
| /books/list =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| 0.088956s |
| /end =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | 0.048612s |
| =A0-> MyApp::View::HTML->process =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 | 0.047493s |
'------------------------------------------------------------+-----------'


So I found the very same problem that I have in my real application: unwant=
ed (and very BIG) columns are called in each request. I can=B4t 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 f=
rom the main table is not a strange practice...)

Any help will be really appreciated. I am stuck. Regards:
Migue


________________________________
 De: Jess Robinson <castaway at desert-island.me.uk>
Para: Miguel Barco <miguelelelele at yahoo.es>; DBIx::Class user and developer=
 list <dbix-class at lists.scsys.co.uk> =

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20120107/aec=
024f2/attachment.htm