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? TIA, Ekki
_______________________________________________ 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
