On 08/24/2012 09:15 AM, Ekki Plicht wrote: > Hi. > > I have a table of products, each product has one or more texts in a > related table. The related table does not only have the text stored, > but also a language column, i.e. the table stores texts in all > languages. A product can have more than one text in one language. > > CREATE TABLE `products` ( > `id` int(11) NOT NULL AUTO_INCREMENT, > `artnum` varchar(12) NOT NULL, > PRIMARY KEY (`id`), > UNIQUE KEY `artnum_UNIQUE` (`artnum`), > ) ENGINE=InnoDB DEFAULT CHARSET=utf8; > > CREATE TABLE `text` ( > `id` int(11) NOT NULL AUTO_INCREMENT, > `id_prod` int(11) DEFAULT NULL, > `lang` varchar(2) DEFAULT NULL COMMENT 'ISO 639-1 code of language > used.\n', > `text` text COMMENT 'The text describing the product itself.\n', > PRIMARY KEY (`id`), > KEY `fk_text_prod` (`id_prod`), > ) ENGINE=InnoDB DEFAULT CHARSET=utf8; > > > In an overview of products, I want to see how many texts each product > _in_ _each_ _language_ has. How do I do this with DBIc? > > I tried with prefetch, but don't understand how I can differentiate > the prefectched rows by language. Currently I just get the products, > and then iterate over each product, doing n searches, one for each > language. At currently 5 languages and 25 products per page, I have > 125 queries just to get at that information. The fetch time is > notable, in the range of 2 or 3 seconds. > > One idea I had is to give up some level of normalization, put a > trigger in table `text` which updates a per language counter in table > `products`. But I don't know if this isn't overkill just to get the > counts of texts per product per language. > > Another idea was to have one table per language, resulting in a simple > 1:n relation for each language. But that would require extending the > tables when we add a new language. Possible, but not very elegant. > > The `text` table does not update very often, in fact only rarely once > it is populated. So the number of texts per product do not change often. > > Maybe the design of the database is just inefficient, it's not my main > area of expertise... :) > > So my questions are: > - How do I do an effective search with or without prefetch for this > related table with dbic, just getting the counts per language in the > related table? > - Is such a design useful at all, considered that this is just one of > nine similiar related tables?
This doesn't actually have much to do with prefetch.
$product_rs->search(undef, {
group_by => [qw(me.id text.lang)],
columns => {
product_id => 'me.id',
lang => 'text.lang',
text_count => { count => 'text.id' },
},
join => 'text',
result_class => 'DBIx::Class::ResultClass::HashRefInflator',
})->all;
Now if you wanted something more complex, like to count a number of
related things, you may want to look at my helper, CorrelateRelationship
<http://search.cpan.org/%7Efrew/DBIx-Class-Helpers-2.015000/lib/DBIx/Class/Helper/ResultSet/CorrelateRelationship.pm>,
which was pretty much made for just this use case, where I had to make 7
related counts for each row in a table of 25 rows. Before correlating
it was 25 * 7 + 1 queries, now it's just one gigantic one. Fast in SQL
Server, supposedly it will be slow in mysql, but I have yet to see
evidence of that.
Hope this helps!
-fREW
signature.asc
Description: OpenPGP digital signature
_______________________________________________ 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
