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

Tue Jan 10 12:40:48 GMT 2012

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 b=
ook record has only one content record.

I can=B4t understand this behavior as an optimization, retrieving a lot of =
big columns...

Even more, I cant create additional "soft" columns to get results from calc=
ulations because the prefetch tries to retrieve them, and they do not exist=
s really in the db.

I case that DBIc can=B4t 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:=A0contents, to store additional contents for the =
tutorial table=A0books. 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=A0Schema/Result/Book.pm=A0I have added the relationship between=A0books=
=A0and=A0contents

__PACKAGE__->might_have(
=A0 "the_content",
=A0 "MyApp::Schema::Result::Content",
=A0 { "foreign.id" =3D> "self.id" },
);

And a ResultSet for my test:=A0Schema/ResultSet/Book.pm=A0with the search t=
hat 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=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,=A0the_content.id, the_content.cont=
ent_en, the_content.content_es, the_content.pictures=A0FROM books me LEFT J=
OIN 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...)

________________________________
 De: neil.lunn <neil at mylunn.id.au>
Para: dbix-class at lists.scsys.co.uk =

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 f=
rom the main table an some from the related one, so I was told to use Prefe=
tch.
Which is fine as this is basically an optimisation to avoid going back to t=
he 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 ev=
erywhere):
>
>
>SELECT=A0books.title,=A0contents.content_en
>FROM=A0books,=A0contents
>WHERE=A0books.id=A0=3D=A01111
>AND=A0contents.id=A0=3D=A01111
Which is more or less exactly what prefetch is doing for you. Set
    DBIC_TRACE=3D1 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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20120110/8fa=
c66bd/attachment-0001.htm