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 <[email protected]>wrote: > 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] >
_______________________________________________ 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]
