Ok, thanks.
This was insofar helpful as it showed my lack of understanding of
DBIx::Class :)
I will go back to the drawing board and some books and try to understand
better, what it does and what I want to achieve.
Rgds,
Ekki
On Fri, Aug 24, 2012 at 6:34 PM, fREW Schmidt wrote:
>
> 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,
> 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
>
> ___
> 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
>
___
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
