[Dbix-class] I get only one in a one to one
Jason Galea
lists at eightdegrees.com.au
Tue Jan 10 13:12:40 GMT 2012
AFAIK you can't use the columns attribute for what you are trying to
achieve, you'll need +select and +as.. see here..
https://metacpan.org/module/DBIx::Class::Manual::Joining#Subset-of-related-=
fields
cheers,
J
On Tue, Jan 10, 2012 at 10:40 PM, Miguel Barco <miguelelelele at yahoo.es>wrot=
e:
> 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=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
> calculations because the prefetch tries to retrieve them, and they do not
> exists 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: *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=3DInnoDB DEFAULT CHARSET=3Dutf8;
>
>
> 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=3DInnoDB DEFAULT CHARSET=3Dutf8;
>
>
>
> 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" =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
>
> 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 '*
> 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) =3D @_;
> $c->stash(books =3D> [$c->model('DB::Book')->basic_search()]);
> $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.content_en, the_content.content_es, the_content.pictures FROM
> books me LEFT JOIN contents the_content ON the_content.id =3D 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=3Dutf-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=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 from 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 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 =3D 1111
> *AND* contents.id =3D 1111
>
>
> 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 fir=
st
> 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/dbix-class@lists.scsys.co.uk
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20120110/83e=
5bf8b/attachment-0001.htm
More information about the DBIx-Class
mailing list