Skip Menu |
 

This queue is for tickets about the DBIx-Class CPAN distribution.

Report information
The Basics
Id: 54949
Status: resolved
Priority: 0/
Queue: DBIx-Class

People
Owner: Nobody in particular
Requestors: soporte [...] web-experto.com.ar
Cc:
AdminCc:

Bug Information
Severity: Normal
Broken in: 0.08119
Fixed in: 0.08250



Subject: Search with has_many relation fails at using ORDER BY RAND()
Download (untitled) / with headers
text/plain 3.7k
Hi, first i apologies about my english, i will try to express my self the best i can. I found this issue that i detail next. Thanks DBIx-Class: DBIx-Class-0.08119 Perl Version: 5.8 OS: Ubuntu 8.10 package WEBEXPERTO::Modulos::Catalogue::Model::Product; use strict; use warnings; use base qw/DBIx::Class/; use base qw/WEBEXPERTO::Core::MEB::Model::Model/; __PACKAGE__->load_components(qw/PK::Auto ResultSetManager Core/); __PACKAGE__->table('catalogue_products'); __PACKAGE__->add_columns(qw/id_product id_node name_product description_product is_actived_product datetime_added_product datetime_last_modified_product date_start_publish_product date_finish_publish_product imagen_product resumen_product codigo_product destacado_product oferta_product skin_name/); __PACKAGE__->set_primary_key(qw/ id_product /); __PACKAGE__->has_many( 'productPrice' => 'WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice', {'foreign.idProduct' => 'self.id_product'}, {cascade_delete => 0}, ); __PACKAGE__->has_one( 'node' => 'WEBEXPERTO::Modulos::Catalogue::Model::Node', {'foreign.id' => 'self.id_node'}, {cascade_delete => 0}, ); 1; package WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice; use strict; use warnings; use base qw/DBIx::Class/; __PACKAGE__->load_components(qw/PK::Auto Core/); __PACKAGE__->table('catalogue_productPrices'); __PACKAGE__->add_columns(qw/id idProduct idPriceList idCurrency value/); __PACKAGE__->set_primary_key(qw/ id /); __PACKAGE__->has_one( 'currency' =>'WEBEXPERTO::Modulos::Catalogue::Model::Currency', {'foreign.id' => 'self.idCurrency' }, ); __PACKAGE__->belongs_to( 'priceList' =>'WEBEXPERTO::Modulos::Catalogue::Model::PriceList', {'foreign.id' => 'self.idPriceList' }, ); 1; #CODE my @productPrefetch; push(@productPrefetch,'node'); push(@productPrefetch,{'productPrice'=>'currency'});#has_many relation my $searchAttributes = { prefetch => \@productPrefetch, rows => 3, order_by=> 'RAND()', #<-- cause the bug group_by => 'me.id_product', }; my $rsProducts = $schema->resultset('Product')->search({}, $searchAttributes); my $totalItems = $rsProducts->pager()->total_entries; What i expect is a set of products with has_many relation (productPrice) loaded with all prices. What i get is three products but eachone repeted 3 times, seems that group_by is not working. Sql query looks like this: SELECT me.id_product, me.id_node, me.name_product, me.description_product, me.is_actived_product, me.datetime_added_product, me.datetime_last_modified_product, me.date_start_publish_product, me.date_finish_publish_product, me.imagen_product, me.resumen_product, me.codigo_product, me.destacado_product, me.oferta_product, me.skin_name, productPrice.id, productPrice.idProduct, productPrice.idPriceList, productPrice.idCurrency, productPrice.value, currency.id, currency.name, currency.symbol FROM (SELECT me.id_product, me.id_node, me.name_product, me.description_product, me.is_actived_product, me.datetime_added_product, me.datetime_last_modified_product, me.date_start_publish_product, me.date_finish_publish_product, me.imagen_product, me.resumen_product, me.codigo_product, me.destacado_product, me.oferta_product, me.skin_name, me.stock_product, me.iva_product, me.marca_product FROM catalogue_products me GROUP BY me.id_product ORDER BY RAND() LIMIT 3) me LEFT JOIN catalogue_productPrices productPrice ON productPrice.idProduct = me.id_product LEFT JOIN catalogue_currencies currency ON currency.id = productPrice.idCurrency ORDER BY RAND(), productPrice.idProduct; The last 'ORDER BY RAND()' mix up the product results (its seems like group_by is ignored). If you dont specify order_by attribute (with 'RAND()') its works just fine. If you need more details please let me know. Thanks again.
Download (untitled) / with headers
text/plain 5.4k
On Wed Feb 24 15:24:56 2010, webexperto wrote: Show quoted text
> Hi, first i apologies about my english, i will try to express my self > the best i can. I found this issue that i detail next. Thanks > > DBIx-Class: DBIx-Class-0.08119 > Perl Version: 5.8 > OS: Ubuntu 8.10 > > package WEBEXPERTO::Modulos::Catalogue::Model::Product; > use strict; > use warnings; > use base qw/DBIx::Class/; > use base qw/WEBEXPERTO::Core::MEB::Model::Model/; > __PACKAGE__->load_components(qw/PK::Auto ResultSetManager Core/); > __PACKAGE__->table('catalogue_products'); > __PACKAGE__->add_columns(qw/id_product id_node name_product > description_product is_actived_product datetime_added_product > datetime_last_modified_product date_start_publish_product > date_finish_publish_product imagen_product resumen_product > codigo_product destacado_product oferta_product skin_name/); > __PACKAGE__->set_primary_key(qw/ id_product /); > __PACKAGE__->has_many( > 'productPrice' => > 'WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice', > {'foreign.idProduct' => 'self.id_product'}, > {cascade_delete => 0}, > ); > __PACKAGE__->has_one( > 'node' => 'WEBEXPERTO::Modulos::Catalogue::Model::Node', > {'foreign.id' => 'self.id_node'}, > {cascade_delete => 0}, > ); > 1; > > package WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice; > use strict; > use warnings; > use base qw/DBIx::Class/; > __PACKAGE__->load_components(qw/PK::Auto Core/); > __PACKAGE__->table('catalogue_productPrices'); > __PACKAGE__->add_columns(qw/id idProduct idPriceList idCurrency value/); > __PACKAGE__->set_primary_key(qw/ id /); > __PACKAGE__->has_one( > 'currency' =>'WEBEXPERTO::Modulos::Catalogue::Model::Currency', > {'foreign.id' => 'self.idCurrency' }, > ); > > __PACKAGE__->belongs_to( > 'priceList' =>'WEBEXPERTO::Modulos::Catalogue::Model::PriceList', > {'foreign.id' => 'self.idPriceList' }, > ); > 1; > > > > #CODE > my @productPrefetch; > push(@productPrefetch,'node'); > push(@productPrefetch,{'productPrice'=>'currency'});#has_many relation > my $searchAttributes = { > prefetch => \@productPrefetch, > rows => 3, > order_by=> 'RAND()', #<-- cause the bug > group_by => 'me.id_product', > }; > my $rsProducts = $schema->resultset('Product')->search({}, > $searchAttributes); > my $totalItems = $rsProducts->pager()->total_entries; > > > > What i expect is a set of products with has_many relation (productPrice) > loaded with all prices. What i get is three products but eachone repeted > 3 times, > seems that group_by is not working. > > Sql query looks like this: > SELECT me.id_product, me.id_node, me.name_product, > me.description_product, me.is_actived_product, > me.datetime_added_product, me.datetime_last_modified_product, > me.date_start_publish_product, me.date_finish_publish_product, > me.imagen_product, me.resumen_product, me.codigo_product, > me.destacado_product, me.oferta_product, me.skin_name, productPrice.id, > productPrice.idProduct, productPrice.idPriceList, > productPrice.idCurrency, productPrice.value, currency.id, currency.name, > currency.symbol FROM > (SELECT me.id_product, me.id_node, me.name_product, > me.description_product, me.is_actived_product, > me.datetime_added_product, me.datetime_last_modified_product, > me.date_start_publish_product, me.date_finish_publish_product, > me.imagen_product, me.resumen_product, me.codigo_product, > me.destacado_product, me.oferta_product, me.skin_name, me.stock_product, > me.iva_product, me.marca_product FROM catalogue_products me GROUP BY > me.id_product ORDER BY RAND() LIMIT 3) me > LEFT JOIN catalogue_productPrices productPrice ON productPrice.idProduct > = me.id_product LEFT JOIN catalogue_currencies currency ON currency.id = > productPrice.idCurrency ORDER BY RAND(), productPrice.idProduct; > > The last 'ORDER BY RAND()' mix up the product results (its seems like > group_by is ignored). If you dont specify order_by attribute (with > 'RAND()') its works just fine. > > If you need more details please let me know. Thanks again.
First of all this has nothing to do with GROUP BY (which is espected in the innermost select). In order to resolve this bug on the technical level, I need a proposal for SQL that *will* work in this situation. From all I know it is simply not possible to express what you want in pure SQL because: 1) All rows relevant to the same catalogue_product need to be grouped together (appear one after another). For this the order_by criteria for the outermost select *must* be "by PK/Unique column(s) of catalogue_product. This requirement is necessary to make ->next work (we fetch rows until the master-PK changes) 2) You want a random ordering of rows which is in direct contradiction with the above requirement. You either: 2a) Make the outermost select ordered by your criteria, which messes up the sequencin of different catalogue_products an gies you the result you observe 2b) Make the inner catalogue_product-only select ordered by your criteria - the outer order_by will reorder the results of the join again 2c) Make the inner catalogue_product-only select ordered by your desired criteria and DO NOT order the outermost join result - this will result in non-deterministic order of the final result, depending on how the rows are atually stored in the database. So there - 3 scenarios I can think of, none of which really work. If you can express it in *deterministic* SQL - I can make DBIC execute it, no questions asked. Cheers
Subject: [Autoreply] [rt.cpan.org #54949] Search with has_many relation fails at using ORDER BY RAND()
Date: Thu, 25 Feb 2010 01:51:53 -0300 (ART)
To: "Peter Rabbitson via RT" <bug-DBIx-Class [...] rt.cpan.org>
From: macevedo [...] web-experto.com.ar
Download (untitled) / with headers
text/plain 179b
En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Subject: [rt.cpan.org #54949] Search with has_many relation fails at using ORDER BY RAND()
Date: Thu, 25 Feb 2010 03:44:52 -0500
To: soporte [...] web-experto.com.ar
From: "Peter Rabbitson via RT" <bug-DBIx-Class [...] rt.cpan.org>
Download (untitled) / with headers
text/plain 5.4k
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > On Wed Feb 24 15:24:56 2010, webexperto wrote: Show quoted text
> Hi, first i apologies about my english, i will try to express my self > the best i can. I found this issue that i detail next. Thanks > > DBIx-Class: DBIx-Class-0.08119 > Perl Version: 5.8 > OS: Ubuntu 8.10 > > package WEBEXPERTO::Modulos::Catalogue::Model::Product; > use strict; > use warnings; > use base qw/DBIx::Class/; > use base qw/WEBEXPERTO::Core::MEB::Model::Model/; > __PACKAGE__->load_components(qw/PK::Auto ResultSetManager Core/); > __PACKAGE__->table('catalogue_products'); > __PACKAGE__->add_columns(qw/id_product id_node name_product > description_product is_actived_product datetime_added_product > datetime_last_modified_product date_start_publish_product > date_finish_publish_product imagen_product resumen_product > codigo_product destacado_product oferta_product skin_name/); > __PACKAGE__->set_primary_key(qw/ id_product /); > __PACKAGE__->has_many( > 'productPrice' => > 'WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice', > {'foreign.idProduct' => 'self.id_product'}, > {cascade_delete => 0}, > ); > __PACKAGE__->has_one( > 'node' => 'WEBEXPERTO::Modulos::Catalogue::Model::Node', > {'foreign.id' => 'self.id_node'}, > {cascade_delete => 0}, > ); > 1; > > package WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice; > use strict; > use warnings; > use base qw/DBIx::Class/; > __PACKAGE__->load_components(qw/PK::Auto Core/); > __PACKAGE__->table('catalogue_productPrices'); > __PACKAGE__->add_columns(qw/id idProduct idPriceList idCurrency value/); > __PACKAGE__->set_primary_key(qw/ id /); > __PACKAGE__->has_one( > 'currency' =>'WEBEXPERTO::Modulos::Catalogue::Model::Currency', > {'foreign.id' => 'self.idCurrency' }, > ); > > __PACKAGE__->belongs_to( > 'priceList' =>'WEBEXPERTO::Modulos::Catalogue::Model::PriceList', > {'foreign.id' => 'self.idPriceList' }, > ); > 1; > > > > #CODE > my @productPrefetch; > push(@productPrefetch,'node'); > push(@productPrefetch,{'productPrice'=>'currency'});#has_many relation > my $searchAttributes = { > prefetch => \@productPrefetch, > rows => 3, > order_by=> 'RAND()', #<-- cause the bug > group_by => 'me.id_product', > }; > my $rsProducts = $schema->resultset('Product')->search({}, > $searchAttributes); > my $totalItems = $rsProducts->pager()->total_entries; > > > > What i expect is a set of products with has_many relation (productPrice) > loaded with all prices. What i get is three products but eachone repeted > 3 times, > seems that group_by is not working. > > Sql query looks like this: > SELECT me.id_product, me.id_node, me.name_product, > me.description_product, me.is_actived_product, > me.datetime_added_product, me.datetime_last_modified_product, > me.date_start_publish_product, me.date_finish_publish_product, > me.imagen_product, me.resumen_product, me.codigo_product, > me.destacado_product, me.oferta_product, me.skin_name, productPrice.id, > productPrice.idProduct, productPrice.idPriceList, > productPrice.idCurrency, productPrice.value, currency.id, currency.name, > currency.symbol FROM > (SELECT me.id_product, me.id_node, me.name_product, > me.description_product, me.is_actived_product, > me.datetime_added_product, me.datetime_last_modified_product, > me.date_start_publish_product, me.date_finish_publish_product, > me.imagen_product, me.resumen_product, me.codigo_product, > me.destacado_product, me.oferta_product, me.skin_name, me.stock_product, > me.iva_product, me.marca_product FROM catalogue_products me GROUP BY > me.id_product ORDER BY RAND() LIMIT 3) me > LEFT JOIN catalogue_productPrices productPrice ON productPrice.idProduct > = me.id_product LEFT JOIN catalogue_currencies currency ON currency.id = > productPrice.idCurrency ORDER BY RAND(), productPrice.idProduct; > > The last 'ORDER BY RAND()' mix up the product results (its seems like > group_by is ignored). If you dont specify order_by attribute (with > 'RAND()') its works just fine. > > If you need more details please let me know. Thanks again.
First of all this has nothing to do with GROUP BY (which is espected in the innermost select). In order to resolve this bug on the technical level, I need a proposal for SQL that *will* work in this situation. From all I know it is simply not possible to express what you want in pure SQL because: 1) All rows relevant to the same catalogue_product need to be grouped together (appear one after another). For this the order_by criteria for the outermost select *must* be "by PK/Unique column(s) of catalogue_product. This requirement is necessary to make ->next work (we fetch rows until the master-PK changes) 2) You want a random ordering of rows which is in direct contradiction with the above requirement. You either: 2a) Make the outermost select ordered by your criteria, which messes up the sequencin of different catalogue_products an gies you the result you observe 2b) Make the inner catalogue_product-only select ordered by your criteria - the outer order_by will reorder the results of the join again 2c) Make the inner catalogue_product-only select ordered by your desired criteria and DO NOT order the outermost join result - this will result in non-deterministic order of the final result, depending on how the rows are atually stored in the database. So there - 3 scenarios I can think of, none of which really work. If you can express it in *deterministic* SQL - I can make DBIC execute it, no questions asked. Cheers
Subject: [Autoreply] [Autoreply] [rt.cpan.org #54949] Search with has_many relation fails at using ORDER BY RAND()
Date: Thu, 25 Feb 2010 01:53:07 -0300 (ART)
To: "macevedo via RT" <bug-DBIx-Class [...] rt.cpan.org>
From: macevedo [...] web-experto.com.ar
Download (untitled) / with headers
text/plain 179b
En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Subject: [Autoreply] [rt.cpan.org #54949] Search with has_many relation fails at using ORDER BY RAND()
Date: Thu, 25 Feb 2010 03:46:06 -0500
To: soporte [...] web-experto.com.ar
From: "macevedo via RT" <bug-DBIx-Class [...] rt.cpan.org>
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 5.4k
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > On Wed Feb 24 15:24:56 2010, webexperto wrote: Show quoted text
> Hi, first i apologies about my english, i will try to express my self > the best i can. I found this issue that i detail next. Thanks > > DBIx-Class: DBIx-Class-0.08119 > Perl Version: 5.8 > OS: Ubuntu 8.10 > > package WEBEXPERTO::Modulos::Catalogue::Model::Product; > use strict; > use warnings; > use base qw/DBIx::Class/; > use base qw/WEBEXPERTO::Core::MEB::Model::Model/; > __PACKAGE__->load_components(qw/PK::Auto ResultSetManager Core/); > __PACKAGE__->table('catalogue_products'); > __PACKAGE__->add_columns(qw/id_product id_node name_product > description_product is_actived_product datetime_added_product > datetime_last_modified_product date_start_publish_product > date_finish_publish_product imagen_product resumen_product > codigo_product destacado_product oferta_product skin_name/); > __PACKAGE__->set_primary_key(qw/ id_product /); > __PACKAGE__->has_many( > 'productPrice' => > 'WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice', > {'foreign.idProduct' => 'self.id_product'}, > {cascade_delete => 0}, > ); > __PACKAGE__->has_one( > 'node' => 'WEBEXPERTO::Modulos::Catalogue::Model::Node', > {'foreign.id' => 'self.id_node'}, > {cascade_delete => 0}, > ); > 1; > > package WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice; > use strict; > use warnings; > use base qw/DBIx::Class/; > __PACKAGE__->load_components(qw/PK::Auto Core/); > __PACKAGE__->table('catalogue_productPrices'); > __PACKAGE__->add_columns(qw/id idProduct idPriceList idCurrency value/); > __PACKAGE__->set_primary_key(qw/ id /); > __PACKAGE__->has_one( > 'currency' =>'WEBEXPERTO::Modulos::Catalogue::Model::Currency', > {'foreign.id' => 'self.idCurrency' }, > ); > > __PACKAGE__->belongs_to( > 'priceList' =>'WEBEXPERTO::Modulos::Catalogue::Model::PriceList', > {'foreign.id' => 'self.idPriceList' }, > ); > 1; > > > > #CODE > my @productPrefetch; > push(@productPrefetch,'node'); > push(@productPrefetch,{'productPrice'=>'currency'});#has_many relation > my $searchAttributes = { > prefetch => \@productPrefetch, > rows => 3, > order_by=> 'RAND()', #<-- cause the bug > group_by => 'me.id_product', > }; > my $rsProducts = $schema->resultset('Product')->search({}, > $searchAttributes); > my $totalItems = $rsProducts->pager()->total_entries; > > > > What i expect is a set of products with has_many relation (productPrice) > loaded with all prices. What i get is three products but eachone repeted > 3 times, > seems that group_by is not working. > > Sql query looks like this: > SELECT me.id_product, me.id_node, me.name_product, > me.description_product, me.is_actived_product, > me.datetime_added_product, me.datetime_last_modified_product, > me.date_start_publish_product, me.date_finish_publish_product, > me.imagen_product, me.resumen_product, me.codigo_product, > me.destacado_product, me.oferta_product, me.skin_name, productPrice.id, > productPrice.idProduct, productPrice.idPriceList, > productPrice.idCurrency, productPrice.value, currency.id, currency.name, > currency.symbol FROM > (SELECT me.id_product, me.id_node, me.name_product, > me.description_product, me.is_actived_product, > me.datetime_added_product, me.datetime_last_modified_product, > me.date_start_publish_product, me.date_finish_publish_product, > me.imagen_product, me.resumen_product, me.codigo_product, > me.destacado_product, me.oferta_product, me.skin_name, me.stock_product, > me.iva_product, me.marca_product FROM catalogue_products me GROUP BY > me.id_product ORDER BY RAND() LIMIT 3) me > LEFT JOIN catalogue_productPrices productPrice ON productPrice.idProduct > = me.id_product LEFT JOIN catalogue_currencies currency ON currency.id = > productPrice.idCurrency ORDER BY RAND(), productPrice.idProduct; > > The last 'ORDER BY RAND()' mix up the product results (its seems like > group_by is ignored). If you dont specify order_by attribute (with > 'RAND()') its works just fine. > > If you need more details please let me know. Thanks again.
First of all this has nothing to do with GROUP BY (which is espected in the innermost select). In order to resolve this bug on the technical level, I need a proposal for SQL that *will* work in this situation. From all I know it is simply not possible to express what you want in pure SQL because: 1) All rows relevant to the same catalogue_product need to be grouped together (appear one after another). For this the order_by criteria for the outermost select *must* be "by PK/Unique column(s) of catalogue_product. This requirement is necessary to make ->next work (we fetch rows until the master-PK changes) 2) You want a random ordering of rows which is in direct contradiction with the above requirement. You either: 2a) Make the outermost select ordered by your criteria, which messes up the sequencin of different catalogue_products an gies you the result you observe 2b) Make the inner catalogue_product-only select ordered by your criteria - the outer order_by will reorder the results of the join again 2c) Make the inner catalogue_product-only select ordered by your desired criteria and DO NOT order the outermost join result - this will result in non-deterministic order of the final result, depending on how the rows are atually stored in the database. So there - 3 scenarios I can think of, none of which really work. If you can express it in *deterministic* SQL - I can make DBIC execute it, no questions asked. Cheers
Subject: [Autoreply] [Autoreply] [Autoreply] [rt.cpan.org #54949] Search with has_many relation fails at using ORDER BY RAND()
Date: Thu, 25 Feb 2010 01:54:12 -0300 (ART)
To: "macevedo via RT" <bug-DBIx-Class [...] rt.cpan.org>
From: macevedo [...] web-experto.com.ar
Download (untitled) / with headers
text/plain 179b
En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Subject: [Autoreply] [Autoreply] [rt.cpan.org #54949] Search with has_many relation fails at using ORDER BY RAND()
Date: Thu, 25 Feb 2010 03:47:14 -0500
To: soporte [...] web-experto.com.ar
From: "macevedo via RT" <bug-DBIx-Class [...] rt.cpan.org>
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 5.4k
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > On Wed Feb 24 15:24:56 2010, webexperto wrote: Show quoted text
> Hi, first i apologies about my english, i will try to express my self > the best i can. I found this issue that i detail next. Thanks > > DBIx-Class: DBIx-Class-0.08119 > Perl Version: 5.8 > OS: Ubuntu 8.10 > > package WEBEXPERTO::Modulos::Catalogue::Model::Product; > use strict; > use warnings; > use base qw/DBIx::Class/; > use base qw/WEBEXPERTO::Core::MEB::Model::Model/; > __PACKAGE__->load_components(qw/PK::Auto ResultSetManager Core/); > __PACKAGE__->table('catalogue_products'); > __PACKAGE__->add_columns(qw/id_product id_node name_product > description_product is_actived_product datetime_added_product > datetime_last_modified_product date_start_publish_product > date_finish_publish_product imagen_product resumen_product > codigo_product destacado_product oferta_product skin_name/); > __PACKAGE__->set_primary_key(qw/ id_product /); > __PACKAGE__->has_many( > 'productPrice' => > 'WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice', > {'foreign.idProduct' => 'self.id_product'}, > {cascade_delete => 0}, > ); > __PACKAGE__->has_one( > 'node' => 'WEBEXPERTO::Modulos::Catalogue::Model::Node', > {'foreign.id' => 'self.id_node'}, > {cascade_delete => 0}, > ); > 1; > > package WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice; > use strict; > use warnings; > use base qw/DBIx::Class/; > __PACKAGE__->load_components(qw/PK::Auto Core/); > __PACKAGE__->table('catalogue_productPrices'); > __PACKAGE__->add_columns(qw/id idProduct idPriceList idCurrency value/); > __PACKAGE__->set_primary_key(qw/ id /); > __PACKAGE__->has_one( > 'currency' =>'WEBEXPERTO::Modulos::Catalogue::Model::Currency', > {'foreign.id' => 'self.idCurrency' }, > ); > > __PACKAGE__->belongs_to( > 'priceList' =>'WEBEXPERTO::Modulos::Catalogue::Model::PriceList', > {'foreign.id' => 'self.idPriceList' }, > ); > 1; > > > > #CODE > my @productPrefetch; > push(@productPrefetch,'node'); > push(@productPrefetch,{'productPrice'=>'currency'});#has_many relation > my $searchAttributes = { > prefetch => \@productPrefetch, > rows => 3, > order_by=> 'RAND()', #<-- cause the bug > group_by => 'me.id_product', > }; > my $rsProducts = $schema->resultset('Product')->search({}, > $searchAttributes); > my $totalItems = $rsProducts->pager()->total_entries; > > > > What i expect is a set of products with has_many relation (productPrice) > loaded with all prices. What i get is three products but eachone repeted > 3 times, > seems that group_by is not working. > > Sql query looks like this: > SELECT me.id_product, me.id_node, me.name_product, > me.description_product, me.is_actived_product, > me.datetime_added_product, me.datetime_last_modified_product, > me.date_start_publish_product, me.date_finish_publish_product, > me.imagen_product, me.resumen_product, me.codigo_product, > me.destacado_product, me.oferta_product, me.skin_name, productPrice.id, > productPrice.idProduct, productPrice.idPriceList, > productPrice.idCurrency, productPrice.value, currency.id, currency.name, > currency.symbol FROM > (SELECT me.id_product, me.id_node, me.name_product, > me.description_product, me.is_actived_product, > me.datetime_added_product, me.datetime_last_modified_product, > me.date_start_publish_product, me.date_finish_publish_product, > me.imagen_product, me.resumen_product, me.codigo_product, > me.destacado_product, me.oferta_product, me.skin_name, me.stock_product, > me.iva_product, me.marca_product FROM catalogue_products me GROUP BY > me.id_product ORDER BY RAND() LIMIT 3) me > LEFT JOIN catalogue_productPrices productPrice ON productPrice.idProduct > = me.id_product LEFT JOIN catalogue_currencies currency ON currency.id = > productPrice.idCurrency ORDER BY RAND(), productPrice.idProduct; > > The last 'ORDER BY RAND()' mix up the product results (its seems like > group_by is ignored). If you dont specify order_by attribute (with > 'RAND()') its works just fine. > > If you need more details please let me know. Thanks again.
First of all this has nothing to do with GROUP BY (which is espected in the innermost select). In order to resolve this bug on the technical level, I need a proposal for SQL that *will* work in this situation. From all I know it is simply not possible to express what you want in pure SQL because: 1) All rows relevant to the same catalogue_product need to be grouped together (appear one after another). For this the order_by criteria for the outermost select *must* be "by PK/Unique column(s) of catalogue_product. This requirement is necessary to make ->next work (we fetch rows until the master-PK changes) 2) You want a random ordering of rows which is in direct contradiction with the above requirement. You either: 2a) Make the outermost select ordered by your criteria, which messes up the sequencin of different catalogue_products an gies you the result you observe 2b) Make the inner catalogue_product-only select ordered by your criteria - the outer order_by will reorder the results of the join again 2c) Make the inner catalogue_product-only select ordered by your desired criteria and DO NOT order the outermost join result - this will result in non-deterministic order of the final result, depending on how the rows are atually stored in the database. So there - 3 scenarios I can think of, none of which really work. If you can express it in *deterministic* SQL - I can make DBIC execute it, no questions asked. Cheers
Subject: [Autoreply] [Autoreply] [Autoreply] [Autoreply] [rt.cpan.org #54949] Search with has_many relation fails at using ORDER BY RAND()
Date: Thu, 25 Feb 2010 01:55:22 -0300 (ART)
To: "macevedo via RT" <bug-DBIx-Class [...] rt.cpan.org>
From: macevedo [...] web-experto.com.ar
Download (untitled) / with headers
text/plain 179b
En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Subject: [Autoreply] [Autoreply] [Autoreply] [rt.cpan.org #54949] Search with has_many relation fails at using ORDER BY RAND()
Date: Thu, 25 Feb 2010 03:48:24 -0500
To: soporte [...] web-experto.com.ar
From: "macevedo via RT" <bug-DBIx-Class [...] rt.cpan.org>
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 5.4k
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > On Wed Feb 24 15:24:56 2010, webexperto wrote: Show quoted text
> Hi, first i apologies about my english, i will try to express my self > the best i can. I found this issue that i detail next. Thanks > > DBIx-Class: DBIx-Class-0.08119 > Perl Version: 5.8 > OS: Ubuntu 8.10 > > package WEBEXPERTO::Modulos::Catalogue::Model::Product; > use strict; > use warnings; > use base qw/DBIx::Class/; > use base qw/WEBEXPERTO::Core::MEB::Model::Model/; > __PACKAGE__->load_components(qw/PK::Auto ResultSetManager Core/); > __PACKAGE__->table('catalogue_products'); > __PACKAGE__->add_columns(qw/id_product id_node name_product > description_product is_actived_product datetime_added_product > datetime_last_modified_product date_start_publish_product > date_finish_publish_product imagen_product resumen_product > codigo_product destacado_product oferta_product skin_name/); > __PACKAGE__->set_primary_key(qw/ id_product /); > __PACKAGE__->has_many( > 'productPrice' => > 'WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice', > {'foreign.idProduct' => 'self.id_product'}, > {cascade_delete => 0}, > ); > __PACKAGE__->has_one( > 'node' => 'WEBEXPERTO::Modulos::Catalogue::Model::Node', > {'foreign.id' => 'self.id_node'}, > {cascade_delete => 0}, > ); > 1; > > package WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice; > use strict; > use warnings; > use base qw/DBIx::Class/; > __PACKAGE__->load_components(qw/PK::Auto Core/); > __PACKAGE__->table('catalogue_productPrices'); > __PACKAGE__->add_columns(qw/id idProduct idPriceList idCurrency value/); > __PACKAGE__->set_primary_key(qw/ id /); > __PACKAGE__->has_one( > 'currency' =>'WEBEXPERTO::Modulos::Catalogue::Model::Currency', > {'foreign.id' => 'self.idCurrency' }, > ); > > __PACKAGE__->belongs_to( > 'priceList' =>'WEBEXPERTO::Modulos::Catalogue::Model::PriceList', > {'foreign.id' => 'self.idPriceList' }, > ); > 1; > > > > #CODE > my @productPrefetch; > push(@productPrefetch,'node'); > push(@productPrefetch,{'productPrice'=>'currency'});#has_many relation > my $searchAttributes = { > prefetch => \@productPrefetch, > rows => 3, > order_by=> 'RAND()', #<-- cause the bug > group_by => 'me.id_product', > }; > my $rsProducts = $schema->resultset('Product')->search({}, > $searchAttributes); > my $totalItems = $rsProducts->pager()->total_entries; > > > > What i expect is a set of products with has_many relation (productPrice) > loaded with all prices. What i get is three products but eachone repeted > 3 times, > seems that group_by is not working. > > Sql query looks like this: > SELECT me.id_product, me.id_node, me.name_product, > me.description_product, me.is_actived_product, > me.datetime_added_product, me.datetime_last_modified_product, > me.date_start_publish_product, me.date_finish_publish_product, > me.imagen_product, me.resumen_product, me.codigo_product, > me.destacado_product, me.oferta_product, me.skin_name, productPrice.id, > productPrice.idProduct, productPrice.idPriceList, > productPrice.idCurrency, productPrice.value, currency.id, currency.name, > currency.symbol FROM > (SELECT me.id_product, me.id_node, me.name_product, > me.description_product, me.is_actived_product, > me.datetime_added_product, me.datetime_last_modified_product, > me.date_start_publish_product, me.date_finish_publish_product, > me.imagen_product, me.resumen_product, me.codigo_product, > me.destacado_product, me.oferta_product, me.skin_name, me.stock_product, > me.iva_product, me.marca_product FROM catalogue_products me GROUP BY > me.id_product ORDER BY RAND() LIMIT 3) me > LEFT JOIN catalogue_productPrices productPrice ON productPrice.idProduct > = me.id_product LEFT JOIN catalogue_currencies currency ON currency.id = > productPrice.idCurrency ORDER BY RAND(), productPrice.idProduct; > > The last 'ORDER BY RAND()' mix up the product results (its seems like > group_by is ignored). If you dont specify order_by attribute (with > 'RAND()') its works just fine. > > If you need more details please let me know. Thanks again.
First of all this has nothing to do with GROUP BY (which is espected in the innermost select). In order to resolve this bug on the technical level, I need a proposal for SQL that *will* work in this situation. From all I know it is simply not possible to express what you want in pure SQL because: 1) All rows relevant to the same catalogue_product need to be grouped together (appear one after another). For this the order_by criteria for the outermost select *must* be "by PK/Unique column(s) of catalogue_product. This requirement is necessary to make ->next work (we fetch rows until the master-PK changes) 2) You want a random ordering of rows which is in direct contradiction with the above requirement. You either: 2a) Make the outermost select ordered by your criteria, which messes up the sequencin of different catalogue_products an gies you the result you observe 2b) Make the inner catalogue_product-only select ordered by your criteria - the outer order_by will reorder the results of the join again 2c) Make the inner catalogue_product-only select ordered by your desired criteria and DO NOT order the outermost join result - this will result in non-deterministic order of the final result, depending on how the rows are atually stored in the database. So there - 3 scenarios I can think of, none of which really work. If you can express it in *deterministic* SQL - I can make DBIC execute it, no questions asked. Cheers
Subject: [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [rt.cpan.org #54949] Search with has_many relation fails at using ORDER BY RAND()
Date: Thu, 25 Feb 2010 01:56:34 -0300 (ART)
To: "macevedo via RT" <bug-DBIx-Class [...] rt.cpan.org>
From: macevedo [...] web-experto.com.ar
Download (untitled) / with headers
text/plain 179b
En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Subject: [Autoreply] [Autoreply] [Autoreply] [Autoreply] [rt.cpan.org #54949] Search with has_many relation fails at using ORDER BY RAND()
Date: Thu, 25 Feb 2010 03:49:36 -0500
To: soporte [...] web-experto.com.ar
From: "macevedo via RT" <bug-DBIx-Class [...] rt.cpan.org>
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 5.4k
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > On Wed Feb 24 15:24:56 2010, webexperto wrote: Show quoted text
> Hi, first i apologies about my english, i will try to express my self > the best i can. I found this issue that i detail next. Thanks > > DBIx-Class: DBIx-Class-0.08119 > Perl Version: 5.8 > OS: Ubuntu 8.10 > > package WEBEXPERTO::Modulos::Catalogue::Model::Product; > use strict; > use warnings; > use base qw/DBIx::Class/; > use base qw/WEBEXPERTO::Core::MEB::Model::Model/; > __PACKAGE__->load_components(qw/PK::Auto ResultSetManager Core/); > __PACKAGE__->table('catalogue_products'); > __PACKAGE__->add_columns(qw/id_product id_node name_product > description_product is_actived_product datetime_added_product > datetime_last_modified_product date_start_publish_product > date_finish_publish_product imagen_product resumen_product > codigo_product destacado_product oferta_product skin_name/); > __PACKAGE__->set_primary_key(qw/ id_product /); > __PACKAGE__->has_many( > 'productPrice' => > 'WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice', > {'foreign.idProduct' => 'self.id_product'}, > {cascade_delete => 0}, > ); > __PACKAGE__->has_one( > 'node' => 'WEBEXPERTO::Modulos::Catalogue::Model::Node', > {'foreign.id' => 'self.id_node'}, > {cascade_delete => 0}, > ); > 1; > > package WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice; > use strict; > use warnings; > use base qw/DBIx::Class/; > __PACKAGE__->load_components(qw/PK::Auto Core/); > __PACKAGE__->table('catalogue_productPrices'); > __PACKAGE__->add_columns(qw/id idProduct idPriceList idCurrency value/); > __PACKAGE__->set_primary_key(qw/ id /); > __PACKAGE__->has_one( > 'currency' =>'WEBEXPERTO::Modulos::Catalogue::Model::Currency', > {'foreign.id' => 'self.idCurrency' }, > ); > > __PACKAGE__->belongs_to( > 'priceList' =>'WEBEXPERTO::Modulos::Catalogue::Model::PriceList', > {'foreign.id' => 'self.idPriceList' }, > ); > 1; > > > > #CODE > my @productPrefetch; > push(@productPrefetch,'node'); > push(@productPrefetch,{'productPrice'=>'currency'});#has_many relation > my $searchAttributes = { > prefetch => \@productPrefetch, > rows => 3, > order_by=> 'RAND()', #<-- cause the bug > group_by => 'me.id_product', > }; > my $rsProducts = $schema->resultset('Product')->search({}, > $searchAttributes); > my $totalItems = $rsProducts->pager()->total_entries; > > > > What i expect is a set of products with has_many relation (productPrice) > loaded with all prices. What i get is three products but eachone repeted > 3 times, > seems that group_by is not working. > > Sql query looks like this: > SELECT me.id_product, me.id_node, me.name_product, > me.description_product, me.is_actived_product, > me.datetime_added_product, me.datetime_last_modified_product, > me.date_start_publish_product, me.date_finish_publish_product, > me.imagen_product, me.resumen_product, me.codigo_product, > me.destacado_product, me.oferta_product, me.skin_name, productPrice.id, > productPrice.idProduct, productPrice.idPriceList, > productPrice.idCurrency, productPrice.value, currency.id, currency.name, > currency.symbol FROM > (SELECT me.id_product, me.id_node, me.name_product, > me.description_product, me.is_actived_product, > me.datetime_added_product, me.datetime_last_modified_product, > me.date_start_publish_product, me.date_finish_publish_product, > me.imagen_product, me.resumen_product, me.codigo_product, > me.destacado_product, me.oferta_product, me.skin_name, me.stock_product, > me.iva_product, me.marca_product FROM catalogue_products me GROUP BY > me.id_product ORDER BY RAND() LIMIT 3) me > LEFT JOIN catalogue_productPrices productPrice ON productPrice.idProduct > = me.id_product LEFT JOIN catalogue_currencies currency ON currency.id = > productPrice.idCurrency ORDER BY RAND(), productPrice.idProduct; > > The last 'ORDER BY RAND()' mix up the product results (its seems like > group_by is ignored). If you dont specify order_by attribute (with > 'RAND()') its works just fine. > > If you need more details please let me know. Thanks again.
First of all this has nothing to do with GROUP BY (which is espected in the innermost select). In order to resolve this bug on the technical level, I need a proposal for SQL that *will* work in this situation. From all I know it is simply not possible to express what you want in pure SQL because: 1) All rows relevant to the same catalogue_product need to be grouped together (appear one after another). For this the order_by criteria for the outermost select *must* be "by PK/Unique column(s) of catalogue_product. This requirement is necessary to make ->next work (we fetch rows until the master-PK changes) 2) You want a random ordering of rows which is in direct contradiction with the above requirement. You either: 2a) Make the outermost select ordered by your criteria, which messes up the sequencin of different catalogue_products an gies you the result you observe 2b) Make the inner catalogue_product-only select ordered by your criteria - the outer order_by will reorder the results of the join again 2c) Make the inner catalogue_product-only select ordered by your desired criteria and DO NOT order the outermost join result - this will result in non-deterministic order of the final result, depending on how the rows are atually stored in the database. So there - 3 scenarios I can think of, none of which really work. If you can express it in *deterministic* SQL - I can make DBIC execute it, no questions asked. Cheers
Subject: [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [rt.cpan.org #54949] Search with has_many relation fails at using ORDER BY RAND()
Date: Thu, 25 Feb 2010 01:57:34 -0300 (ART)
To: "macevedo via RT" <bug-DBIx-Class [...] rt.cpan.org>
From: macevedo [...] web-experto.com.ar
Download (untitled) / with headers
text/plain 179b
En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Subject: [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [rt.cpan.org #54949] Search with has_many relation fails at using ORDER BY RAND()
Date: Thu, 25 Feb 2010 03:50:36 -0500
To: soporte [...] web-experto.com.ar
From: "macevedo via RT" <bug-DBIx-Class [...] rt.cpan.org>
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 5.4k
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > On Wed Feb 24 15:24:56 2010, webexperto wrote: Show quoted text
> Hi, first i apologies about my english, i will try to express my self > the best i can. I found this issue that i detail next. Thanks > > DBIx-Class: DBIx-Class-0.08119 > Perl Version: 5.8 > OS: Ubuntu 8.10 > > package WEBEXPERTO::Modulos::Catalogue::Model::Product; > use strict; > use warnings; > use base qw/DBIx::Class/; > use base qw/WEBEXPERTO::Core::MEB::Model::Model/; > __PACKAGE__->load_components(qw/PK::Auto ResultSetManager Core/); > __PACKAGE__->table('catalogue_products'); > __PACKAGE__->add_columns(qw/id_product id_node name_product > description_product is_actived_product datetime_added_product > datetime_last_modified_product date_start_publish_product > date_finish_publish_product imagen_product resumen_product > codigo_product destacado_product oferta_product skin_name/); > __PACKAGE__->set_primary_key(qw/ id_product /); > __PACKAGE__->has_many( > 'productPrice' => > 'WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice', > {'foreign.idProduct' => 'self.id_product'}, > {cascade_delete => 0}, > ); > __PACKAGE__->has_one( > 'node' => 'WEBEXPERTO::Modulos::Catalogue::Model::Node', > {'foreign.id' => 'self.id_node'}, > {cascade_delete => 0}, > ); > 1; > > package WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice; > use strict; > use warnings; > use base qw/DBIx::Class/; > __PACKAGE__->load_components(qw/PK::Auto Core/); > __PACKAGE__->table('catalogue_productPrices'); > __PACKAGE__->add_columns(qw/id idProduct idPriceList idCurrency value/); > __PACKAGE__->set_primary_key(qw/ id /); > __PACKAGE__->has_one( > 'currency' =>'WEBEXPERTO::Modulos::Catalogue::Model::Currency', > {'foreign.id' => 'self.idCurrency' }, > ); > > __PACKAGE__->belongs_to( > 'priceList' =>'WEBEXPERTO::Modulos::Catalogue::Model::PriceList', > {'foreign.id' => 'self.idPriceList' }, > ); > 1; > > > > #CODE > my @productPrefetch; > push(@productPrefetch,'node'); > push(@productPrefetch,{'productPrice'=>'currency'});#has_many relation > my $searchAttributes = { > prefetch => \@productPrefetch, > rows => 3, > order_by=> 'RAND()', #<-- cause the bug > group_by => 'me.id_product', > }; > my $rsProducts = $schema->resultset('Product')->search({}, > $searchAttributes); > my $totalItems = $rsProducts->pager()->total_entries; > > > > What i expect is a set of products with has_many relation (productPrice) > loaded with all prices. What i get is three products but eachone repeted > 3 times, > seems that group_by is not working. > > Sql query looks like this: > SELECT me.id_product, me.id_node, me.name_product, > me.description_product, me.is_actived_product, > me.datetime_added_product, me.datetime_last_modified_product, > me.date_start_publish_product, me.date_finish_publish_product, > me.imagen_product, me.resumen_product, me.codigo_product, > me.destacado_product, me.oferta_product, me.skin_name, productPrice.id, > productPrice.idProduct, productPrice.idPriceList, > productPrice.idCurrency, productPrice.value, currency.id, currency.name, > currency.symbol FROM > (SELECT me.id_product, me.id_node, me.name_product, > me.description_product, me.is_actived_product, > me.datetime_added_product, me.datetime_last_modified_product, > me.date_start_publish_product, me.date_finish_publish_product, > me.imagen_product, me.resumen_product, me.codigo_product, > me.destacado_product, me.oferta_product, me.skin_name, me.stock_product, > me.iva_product, me.marca_product FROM catalogue_products me GROUP BY > me.id_product ORDER BY RAND() LIMIT 3) me > LEFT JOIN catalogue_productPrices productPrice ON productPrice.idProduct > = me.id_product LEFT JOIN catalogue_currencies currency ON currency.id = > productPrice.idCurrency ORDER BY RAND(), productPrice.idProduct; > > The last 'ORDER BY RAND()' mix up the product results (its seems like > group_by is ignored). If you dont specify order_by attribute (with > 'RAND()') its works just fine. > > If you need more details please let me know. Thanks again.
First of all this has nothing to do with GROUP BY (which is espected in the innermost select). In order to resolve this bug on the technical level, I need a proposal for SQL that *will* work in this situation. From all I know it is simply not possible to express what you want in pure SQL because: 1) All rows relevant to the same catalogue_product need to be grouped together (appear one after another). For this the order_by criteria for the outermost select *must* be "by PK/Unique column(s) of catalogue_product. This requirement is necessary to make ->next work (we fetch rows until the master-PK changes) 2) You want a random ordering of rows which is in direct contradiction with the above requirement. You either: 2a) Make the outermost select ordered by your criteria, which messes up the sequencin of different catalogue_products an gies you the result you observe 2b) Make the inner catalogue_product-only select ordered by your criteria - the outer order_by will reorder the results of the join again 2c) Make the inner catalogue_product-only select ordered by your desired criteria and DO NOT order the outermost join result - this will result in non-deterministic order of the final result, depending on how the rows are atually stored in the database. So there - 3 scenarios I can think of, none of which really work. If you can express it in *deterministic* SQL - I can make DBIC execute it, no questions asked. Cheers
Subject: [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [rt.cpan.org #54949] Search with has_many relation fails at using ORDER BY RAND()
Date: Thu, 25 Feb 2010 01:58:48 -0300 (ART)
To: "macevedo via RT" <bug-DBIx-Class [...] rt.cpan.org>
From: macevedo [...] web-experto.com.ar
Download (untitled) / with headers
text/plain 179b
En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Subject: [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [rt.cpan.org #54949] Search with has_many relation fails at using ORDER BY RAND()
Date: Thu, 25 Feb 2010 03:51:52 -0500
To: soporte [...] web-experto.com.ar
From: "macevedo via RT" <bug-DBIx-Class [...] rt.cpan.org>
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 5.4k
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > On Wed Feb 24 15:24:56 2010, webexperto wrote: Show quoted text
> Hi, first i apologies about my english, i will try to express my self > the best i can. I found this issue that i detail next. Thanks > > DBIx-Class: DBIx-Class-0.08119 > Perl Version: 5.8 > OS: Ubuntu 8.10 > > package WEBEXPERTO::Modulos::Catalogue::Model::Product; > use strict; > use warnings; > use base qw/DBIx::Class/; > use base qw/WEBEXPERTO::Core::MEB::Model::Model/; > __PACKAGE__->load_components(qw/PK::Auto ResultSetManager Core/); > __PACKAGE__->table('catalogue_products'); > __PACKAGE__->add_columns(qw/id_product id_node name_product > description_product is_actived_product datetime_added_product > datetime_last_modified_product date_start_publish_product > date_finish_publish_product imagen_product resumen_product > codigo_product destacado_product oferta_product skin_name/); > __PACKAGE__->set_primary_key(qw/ id_product /); > __PACKAGE__->has_many( > 'productPrice' => > 'WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice', > {'foreign.idProduct' => 'self.id_product'}, > {cascade_delete => 0}, > ); > __PACKAGE__->has_one( > 'node' => 'WEBEXPERTO::Modulos::Catalogue::Model::Node', > {'foreign.id' => 'self.id_node'}, > {cascade_delete => 0}, > ); > 1; > > package WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice; > use strict; > use warnings; > use base qw/DBIx::Class/; > __PACKAGE__->load_components(qw/PK::Auto Core/); > __PACKAGE__->table('catalogue_productPrices'); > __PACKAGE__->add_columns(qw/id idProduct idPriceList idCurrency value/); > __PACKAGE__->set_primary_key(qw/ id /); > __PACKAGE__->has_one( > 'currency' =>'WEBEXPERTO::Modulos::Catalogue::Model::Currency', > {'foreign.id' => 'self.idCurrency' }, > ); > > __PACKAGE__->belongs_to( > 'priceList' =>'WEBEXPERTO::Modulos::Catalogue::Model::PriceList', > {'foreign.id' => 'self.idPriceList' }, > ); > 1; > > > > #CODE > my @productPrefetch; > push(@productPrefetch,'node'); > push(@productPrefetch,{'productPrice'=>'currency'});#has_many relation > my $searchAttributes = { > prefetch => \@productPrefetch, > rows => 3, > order_by=> 'RAND()', #<-- cause the bug > group_by => 'me.id_product', > }; > my $rsProducts = $schema->resultset('Product')->search({}, > $searchAttributes); > my $totalItems = $rsProducts->pager()->total_entries; > > > > What i expect is a set of products with has_many relation (productPrice) > loaded with all prices. What i get is three products but eachone repeted > 3 times, > seems that group_by is not working. > > Sql query looks like this: > SELECT me.id_product, me.id_node, me.name_product, > me.description_product, me.is_actived_product, > me.datetime_added_product, me.datetime_last_modified_product, > me.date_start_publish_product, me.date_finish_publish_product, > me.imagen_product, me.resumen_product, me.codigo_product, > me.destacado_product, me.oferta_product, me.skin_name, productPrice.id, > productPrice.idProduct, productPrice.idPriceList, > productPrice.idCurrency, productPrice.value, currency.id, currency.name, > currency.symbol FROM > (SELECT me.id_product, me.id_node, me.name_product, > me.description_product, me.is_actived_product, > me.datetime_added_product, me.datetime_last_modified_product, > me.date_start_publish_product, me.date_finish_publish_product, > me.imagen_product, me.resumen_product, me.codigo_product, > me.destacado_product, me.oferta_product, me.skin_name, me.stock_product, > me.iva_product, me.marca_product FROM catalogue_products me GROUP BY > me.id_product ORDER BY RAND() LIMIT 3) me > LEFT JOIN catalogue_productPrices productPrice ON productPrice.idProduct > = me.id_product LEFT JOIN catalogue_currencies currency ON currency.id = > productPrice.idCurrency ORDER BY RAND(), productPrice.idProduct; > > The last 'ORDER BY RAND()' mix up the product results (its seems like > group_by is ignored). If you dont specify order_by attribute (with > 'RAND()') its works just fine. > > If you need more details please let me know. Thanks again.
First of all this has nothing to do with GROUP BY (which is espected in the innermost select). In order to resolve this bug on the technical level, I need a proposal for SQL that *will* work in this situation. From all I know it is simply not possible to express what you want in pure SQL because: 1) All rows relevant to the same catalogue_product need to be grouped together (appear one after another). For this the order_by criteria for the outermost select *must* be "by PK/Unique column(s) of catalogue_product. This requirement is necessary to make ->next work (we fetch rows until the master-PK changes) 2) You want a random ordering of rows which is in direct contradiction with the above requirement. You either: 2a) Make the outermost select ordered by your criteria, which messes up the sequencin of different catalogue_products an gies you the result you observe 2b) Make the inner catalogue_product-only select ordered by your criteria - the outer order_by will reorder the results of the join again 2c) Make the inner catalogue_product-only select ordered by your desired criteria and DO NOT order the outermost join result - this will result in non-deterministic order of the final result, depending on how the rows are atually stored in the database. So there - 3 scenarios I can think of, none of which really work. If you can express it in *deterministic* SQL - I can make DBIC execute it, no questions asked. Cheers
Subject: [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [rt.cpan.org #54949] Search with has_many relation fails at using ORDER BY RAND()
Date: Thu, 25 Feb 2010 01:59:48 -0300 (ART)
To: "macevedo via RT" <bug-DBIx-Class [...] rt.cpan.org>
From: macevedo [...] web-experto.com.ar
Download (untitled) / with headers
text/plain 179b
En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Subject: [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [rt.cpan.org #54949] Search with has_many relation fails at using ORDER BY RAND()
Date: Thu, 25 Feb 2010 03:52:52 -0500
To: soporte [...] web-experto.com.ar
From: "macevedo via RT" <bug-DBIx-Class [...] rt.cpan.org>
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 5.4k
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > On Wed Feb 24 15:24:56 2010, webexperto wrote: Show quoted text
> Hi, first i apologies about my english, i will try to express my self > the best i can. I found this issue that i detail next. Thanks > > DBIx-Class: DBIx-Class-0.08119 > Perl Version: 5.8 > OS: Ubuntu 8.10 > > package WEBEXPERTO::Modulos::Catalogue::Model::Product; > use strict; > use warnings; > use base qw/DBIx::Class/; > use base qw/WEBEXPERTO::Core::MEB::Model::Model/; > __PACKAGE__->load_components(qw/PK::Auto ResultSetManager Core/); > __PACKAGE__->table('catalogue_products'); > __PACKAGE__->add_columns(qw/id_product id_node name_product > description_product is_actived_product datetime_added_product > datetime_last_modified_product date_start_publish_product > date_finish_publish_product imagen_product resumen_product > codigo_product destacado_product oferta_product skin_name/); > __PACKAGE__->set_primary_key(qw/ id_product /); > __PACKAGE__->has_many( > 'productPrice' => > 'WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice', > {'foreign.idProduct' => 'self.id_product'}, > {cascade_delete => 0}, > ); > __PACKAGE__->has_one( > 'node' => 'WEBEXPERTO::Modulos::Catalogue::Model::Node', > {'foreign.id' => 'self.id_node'}, > {cascade_delete => 0}, > ); > 1; > > package WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice; > use strict; > use warnings; > use base qw/DBIx::Class/; > __PACKAGE__->load_components(qw/PK::Auto Core/); > __PACKAGE__->table('catalogue_productPrices'); > __PACKAGE__->add_columns(qw/id idProduct idPriceList idCurrency value/); > __PACKAGE__->set_primary_key(qw/ id /); > __PACKAGE__->has_one( > 'currency' =>'WEBEXPERTO::Modulos::Catalogue::Model::Currency', > {'foreign.id' => 'self.idCurrency' }, > ); > > __PACKAGE__->belongs_to( > 'priceList' =>'WEBEXPERTO::Modulos::Catalogue::Model::PriceList', > {'foreign.id' => 'self.idPriceList' }, > ); > 1; > > > > #CODE > my @productPrefetch; > push(@productPrefetch,'node'); > push(@productPrefetch,{'productPrice'=>'currency'});#has_many relation > my $searchAttributes = { > prefetch => \@productPrefetch, > rows => 3, > order_by=> 'RAND()', #<-- cause the bug > group_by => 'me.id_product', > }; > my $rsProducts = $schema->resultset('Product')->search({}, > $searchAttributes); > my $totalItems = $rsProducts->pager()->total_entries; > > > > What i expect is a set of products with has_many relation (productPrice) > loaded with all prices. What i get is three products but eachone repeted > 3 times, > seems that group_by is not working. > > Sql query looks like this: > SELECT me.id_product, me.id_node, me.name_product, > me.description_product, me.is_actived_product, > me.datetime_added_product, me.datetime_last_modified_product, > me.date_start_publish_product, me.date_finish_publish_product, > me.imagen_product, me.resumen_product, me.codigo_product, > me.destacado_product, me.oferta_product, me.skin_name, productPrice.id, > productPrice.idProduct, productPrice.idPriceList, > productPrice.idCurrency, productPrice.value, currency.id, currency.name, > currency.symbol FROM > (SELECT me.id_product, me.id_node, me.name_product, > me.description_product, me.is_actived_product, > me.datetime_added_product, me.datetime_last_modified_product, > me.date_start_publish_product, me.date_finish_publish_product, > me.imagen_product, me.resumen_product, me.codigo_product, > me.destacado_product, me.oferta_product, me.skin_name, me.stock_product, > me.iva_product, me.marca_product FROM catalogue_products me GROUP BY > me.id_product ORDER BY RAND() LIMIT 3) me > LEFT JOIN catalogue_productPrices productPrice ON productPrice.idProduct > = me.id_product LEFT JOIN catalogue_currencies currency ON currency.id = > productPrice.idCurrency ORDER BY RAND(), productPrice.idProduct; > > The last 'ORDER BY RAND()' mix up the product results (its seems like > group_by is ignored). If you dont specify order_by attribute (with > 'RAND()') its works just fine. > > If you need more details please let me know. Thanks again.
First of all this has nothing to do with GROUP BY (which is espected in the innermost select). In order to resolve this bug on the technical level, I need a proposal for SQL that *will* work in this situation. From all I know it is simply not possible to express what you want in pure SQL because: 1) All rows relevant to the same catalogue_product need to be grouped together (appear one after another). For this the order_by criteria for the outermost select *must* be "by PK/Unique column(s) of catalogue_product. This requirement is necessary to make ->next work (we fetch rows until the master-PK changes) 2) You want a random ordering of rows which is in direct contradiction with the above requirement. You either: 2a) Make the outermost select ordered by your criteria, which messes up the sequencin of different catalogue_products an gies you the result you observe 2b) Make the inner catalogue_product-only select ordered by your criteria - the outer order_by will reorder the results of the join again 2c) Make the inner catalogue_product-only select ordered by your desired criteria and DO NOT order the outermost join result - this will result in non-deterministic order of the final result, depending on how the rows are atually stored in the database. So there - 3 scenarios I can think of, none of which really work. If you can express it in *deterministic* SQL - I can make DBIC execute it, no questions asked. Cheers
Subject: [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [rt.cpan.org #54949] Search with has_many relation fails at using ORDER BY RAND()
Date: Thu, 25 Feb 2010 02:00:49 -0300 (ART)
To: "macevedo via RT" <bug-DBIx-Class [...] rt.cpan.org>
From: macevedo [...] web-experto.com.ar
Download (untitled) / with headers
text/plain 179b
En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Subject: [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [rt.cpan.org #54949] Search with has_many relation fails at using ORDER BY RAND()
Date: Thu, 25 Feb 2010 03:53:54 -0500
To: soporte [...] web-experto.com.ar
From: "macevedo via RT" <bug-DBIx-Class [...] rt.cpan.org>
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 5.4k
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > On Wed Feb 24 15:24:56 2010, webexperto wrote: Show quoted text
> Hi, first i apologies about my english, i will try to express my self > the best i can. I found this issue that i detail next. Thanks > > DBIx-Class: DBIx-Class-0.08119 > Perl Version: 5.8 > OS: Ubuntu 8.10 > > package WEBEXPERTO::Modulos::Catalogue::Model::Product; > use strict; > use warnings; > use base qw/DBIx::Class/; > use base qw/WEBEXPERTO::Core::MEB::Model::Model/; > __PACKAGE__->load_components(qw/PK::Auto ResultSetManager Core/); > __PACKAGE__->table('catalogue_products'); > __PACKAGE__->add_columns(qw/id_product id_node name_product > description_product is_actived_product datetime_added_product > datetime_last_modified_product date_start_publish_product > date_finish_publish_product imagen_product resumen_product > codigo_product destacado_product oferta_product skin_name/); > __PACKAGE__->set_primary_key(qw/ id_product /); > __PACKAGE__->has_many( > 'productPrice' => > 'WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice', > {'foreign.idProduct' => 'self.id_product'}, > {cascade_delete => 0}, > ); > __PACKAGE__->has_one( > 'node' => 'WEBEXPERTO::Modulos::Catalogue::Model::Node', > {'foreign.id' => 'self.id_node'}, > {cascade_delete => 0}, > ); > 1; > > package WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice; > use strict; > use warnings; > use base qw/DBIx::Class/; > __PACKAGE__->load_components(qw/PK::Auto Core/); > __PACKAGE__->table('catalogue_productPrices'); > __PACKAGE__->add_columns(qw/id idProduct idPriceList idCurrency value/); > __PACKAGE__->set_primary_key(qw/ id /); > __PACKAGE__->has_one( > 'currency' =>'WEBEXPERTO::Modulos::Catalogue::Model::Currency', > {'foreign.id' => 'self.idCurrency' }, > ); > > __PACKAGE__->belongs_to( > 'priceList' =>'WEBEXPERTO::Modulos::Catalogue::Model::PriceList', > {'foreign.id' => 'self.idPriceList' }, > ); > 1; > > > > #CODE > my @productPrefetch; > push(@productPrefetch,'node'); > push(@productPrefetch,{'productPrice'=>'currency'});#has_many relation > my $searchAttributes = { > prefetch => \@productPrefetch, > rows => 3, > order_by=> 'RAND()', #<-- cause the bug > group_by => 'me.id_product', > }; > my $rsProducts = $schema->resultset('Product')->search({}, > $searchAttributes); > my $totalItems = $rsProducts->pager()->total_entries; > > > > What i expect is a set of products with has_many relation (productPrice) > loaded with all prices. What i get is three products but eachone repeted > 3 times, > seems that group_by is not working. > > Sql query looks like this: > SELECT me.id_product, me.id_node, me.name_product, > me.description_product, me.is_actived_product, > me.datetime_added_product, me.datetime_last_modified_product, > me.date_start_publish_product, me.date_finish_publish_product, > me.imagen_product, me.resumen_product, me.codigo_product, > me.destacado_product, me.oferta_product, me.skin_name, productPrice.id, > productPrice.idProduct, productPrice.idPriceList, > productPrice.idCurrency, productPrice.value, currency.id, currency.name, > currency.symbol FROM > (SELECT me.id_product, me.id_node, me.name_product, > me.description_product, me.is_actived_product, > me.datetime_added_product, me.datetime_last_modified_product, > me.date_start_publish_product, me.date_finish_publish_product, > me.imagen_product, me.resumen_product, me.codigo_product, > me.destacado_product, me.oferta_product, me.skin_name, me.stock_product, > me.iva_product, me.marca_product FROM catalogue_products me GROUP BY > me.id_product ORDER BY RAND() LIMIT 3) me > LEFT JOIN catalogue_productPrices productPrice ON productPrice.idProduct > = me.id_product LEFT JOIN catalogue_currencies currency ON currency.id = > productPrice.idCurrency ORDER BY RAND(), productPrice.idProduct; > > The last 'ORDER BY RAND()' mix up the product results (its seems like > group_by is ignored). If you dont specify order_by attribute (with > 'RAND()') its works just fine. > > If you need more details please let me know. Thanks again.
First of all this has nothing to do with GROUP BY (which is espected in the innermost select). In order to resolve this bug on the technical level, I need a proposal for SQL that *will* work in this situation. From all I know it is simply not possible to express what you want in pure SQL because: 1) All rows relevant to the same catalogue_product need to be grouped together (appear one after another). For this the order_by criteria for the outermost select *must* be "by PK/Unique column(s) of catalogue_product. This requirement is necessary to make ->next work (we fetch rows until the master-PK changes) 2) You want a random ordering of rows which is in direct contradiction with the above requirement. You either: 2a) Make the outermost select ordered by your criteria, which messes up the sequencin of different catalogue_products an gies you the result you observe 2b) Make the inner catalogue_product-only select ordered by your criteria - the outer order_by will reorder the results of the join again 2c) Make the inner catalogue_product-only select ordered by your desired criteria and DO NOT order the outermost join result - this will result in non-deterministic order of the final result, depending on how the rows are atually stored in the database. So there - 3 scenarios I can think of, none of which really work. If you can express it in *deterministic* SQL - I can make DBIC execute it, no questions asked. Cheers
Subject: [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [rt.cpan.org #54949] Search with has_many relation fails at using ORDER BY RAND()
Date: Thu, 25 Feb 2010 02:01:52 -0300 (ART)
To: "macevedo via RT" <bug-DBIx-Class [...] rt.cpan.org>
From: macevedo [...] web-experto.com.ar
Download (untitled) / with headers
text/plain 179b
En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Subject: [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [rt.cpan.org #54949] Search with has_many relation fails at using ORDER BY RAND()
Date: Thu, 25 Feb 2010 03:54:58 -0500
To: soporte [...] web-experto.com.ar
From: "macevedo via RT" <bug-DBIx-Class [...] rt.cpan.org>
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 5.4k
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > On Wed Feb 24 15:24:56 2010, webexperto wrote: Show quoted text
> Hi, first i apologies about my english, i will try to express my self > the best i can. I found this issue that i detail next. Thanks > > DBIx-Class: DBIx-Class-0.08119 > Perl Version: 5.8 > OS: Ubuntu 8.10 > > package WEBEXPERTO::Modulos::Catalogue::Model::Product; > use strict; > use warnings; > use base qw/DBIx::Class/; > use base qw/WEBEXPERTO::Core::MEB::Model::Model/; > __PACKAGE__->load_components(qw/PK::Auto ResultSetManager Core/); > __PACKAGE__->table('catalogue_products'); > __PACKAGE__->add_columns(qw/id_product id_node name_product > description_product is_actived_product datetime_added_product > datetime_last_modified_product date_start_publish_product > date_finish_publish_product imagen_product resumen_product > codigo_product destacado_product oferta_product skin_name/); > __PACKAGE__->set_primary_key(qw/ id_product /); > __PACKAGE__->has_many( > 'productPrice' => > 'WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice', > {'foreign.idProduct' => 'self.id_product'}, > {cascade_delete => 0}, > ); > __PACKAGE__->has_one( > 'node' => 'WEBEXPERTO::Modulos::Catalogue::Model::Node', > {'foreign.id' => 'self.id_node'}, > {cascade_delete => 0}, > ); > 1; > > package WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice; > use strict; > use warnings; > use base qw/DBIx::Class/; > __PACKAGE__->load_components(qw/PK::Auto Core/); > __PACKAGE__->table('catalogue_productPrices'); > __PACKAGE__->add_columns(qw/id idProduct idPriceList idCurrency value/); > __PACKAGE__->set_primary_key(qw/ id /); > __PACKAGE__->has_one( > 'currency' =>'WEBEXPERTO::Modulos::Catalogue::Model::Currency', > {'foreign.id' => 'self.idCurrency' }, > ); > > __PACKAGE__->belongs_to( > 'priceList' =>'WEBEXPERTO::Modulos::Catalogue::Model::PriceList', > {'foreign.id' => 'self.idPriceList' }, > ); > 1; > > > > #CODE > my @productPrefetch; > push(@productPrefetch,'node'); > push(@productPrefetch,{'productPrice'=>'currency'});#has_many relation > my $searchAttributes = { > prefetch => \@productPrefetch, > rows => 3, > order_by=> 'RAND()', #<-- cause the bug > group_by => 'me.id_product', > }; > my $rsProducts = $schema->resultset('Product')->search({}, > $searchAttributes); > my $totalItems = $rsProducts->pager()->total_entries; > > > > What i expect is a set of products with has_many relation (productPrice) > loaded with all prices. What i get is three products but eachone repeted > 3 times, > seems that group_by is not working. > > Sql query looks like this: > SELECT me.id_product, me.id_node, me.name_product, > me.description_product, me.is_actived_product, > me.datetime_added_product, me.datetime_last_modified_product, > me.date_start_publish_product, me.date_finish_publish_product, > me.imagen_product, me.resumen_product, me.codigo_product, > me.destacado_product, me.oferta_product, me.skin_name, productPrice.id, > productPrice.idProduct, productPrice.idPriceList, > productPrice.idCurrency, productPrice.value, currency.id, currency.name, > currency.symbol FROM > (SELECT me.id_product, me.id_node, me.name_product, > me.description_product, me.is_actived_product, > me.datetime_added_product, me.datetime_last_modified_product, > me.date_start_publish_product, me.date_finish_publish_product, > me.imagen_product, me.resumen_product, me.codigo_product, > me.destacado_product, me.oferta_product, me.skin_name, me.stock_product, > me.iva_product, me.marca_product FROM catalogue_products me GROUP BY > me.id_product ORDER BY RAND() LIMIT 3) me > LEFT JOIN catalogue_productPrices productPrice ON productPrice.idProduct > = me.id_product LEFT JOIN catalogue_currencies currency ON currency.id = > productPrice.idCurrency ORDER BY RAND(), productPrice.idProduct; > > The last 'ORDER BY RAND()' mix up the product results (its seems like > group_by is ignored). If you dont specify order_by attribute (with > 'RAND()') its works just fine. > > If you need more details please let me know. Thanks again.
First of all this has nothing to do with GROUP BY (which is espected in the innermost select). In order to resolve this bug on the technical level, I need a proposal for SQL that *will* work in this situation. From all I know it is simply not possible to express what you want in pure SQL because: 1) All rows relevant to the same catalogue_product need to be grouped together (appear one after another). For this the order_by criteria for the outermost select *must* be "by PK/Unique column(s) of catalogue_product. This requirement is necessary to make ->next work (we fetch rows until the master-PK changes) 2) You want a random ordering of rows which is in direct contradiction with the above requirement. You either: 2a) Make the outermost select ordered by your criteria, which messes up the sequencin of different catalogue_products an gies you the result you observe 2b) Make the inner catalogue_product-only select ordered by your criteria - the outer order_by will reorder the results of the join again 2c) Make the inner catalogue_product-only select ordered by your desired criteria and DO NOT order the outermost join result - this will result in non-deterministic order of the final result, depending on how the rows are atually stored in the database. So there - 3 scenarios I can think of, none of which really work. If you can express it in *deterministic* SQL - I can make DBIC execute it, no questions asked. Cheers
Subject: [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [rt.cpan.org #54949] Search with has_many relation fails at using ORDER BY RAND()
Date: Thu, 25 Feb 2010 02:02:55 -0300 (ART)
To: "macevedo via RT" <bug-DBIx-Class [...] rt.cpan.org>
From: macevedo [...] web-experto.com.ar
Download (untitled) / with headers
text/plain 179b
En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Subject: [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [rt.cpan.org #54949] Search with has_many relation fails at using ORDER BY RAND()
Date: Thu, 25 Feb 2010 03:56:00 -0500
To: soporte [...] web-experto.com.ar
From: "macevedo via RT" <bug-DBIx-Class [...] rt.cpan.org>
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 5.4k
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > On Wed Feb 24 15:24:56 2010, webexperto wrote: Show quoted text
> Hi, first i apologies about my english, i will try to express my self > the best i can. I found this issue that i detail next. Thanks > > DBIx-Class: DBIx-Class-0.08119 > Perl Version: 5.8 > OS: Ubuntu 8.10 > > package WEBEXPERTO::Modulos::Catalogue::Model::Product; > use strict; > use warnings; > use base qw/DBIx::Class/; > use base qw/WEBEXPERTO::Core::MEB::Model::Model/; > __PACKAGE__->load_components(qw/PK::Auto ResultSetManager Core/); > __PACKAGE__->table('catalogue_products'); > __PACKAGE__->add_columns(qw/id_product id_node name_product > description_product is_actived_product datetime_added_product > datetime_last_modified_product date_start_publish_product > date_finish_publish_product imagen_product resumen_product > codigo_product destacado_product oferta_product skin_name/); > __PACKAGE__->set_primary_key(qw/ id_product /); > __PACKAGE__->has_many( > 'productPrice' => > 'WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice', > {'foreign.idProduct' => 'self.id_product'}, > {cascade_delete => 0}, > ); > __PACKAGE__->has_one( > 'node' => 'WEBEXPERTO::Modulos::Catalogue::Model::Node', > {'foreign.id' => 'self.id_node'}, > {cascade_delete => 0}, > ); > 1; > > package WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice; > use strict; > use warnings; > use base qw/DBIx::Class/; > __PACKAGE__->load_components(qw/PK::Auto Core/); > __PACKAGE__->table('catalogue_productPrices'); > __PACKAGE__->add_columns(qw/id idProduct idPriceList idCurrency value/); > __PACKAGE__->set_primary_key(qw/ id /); > __PACKAGE__->has_one( > 'currency' =>'WEBEXPERTO::Modulos::Catalogue::Model::Currency', > {'foreign.id' => 'self.idCurrency' }, > ); > > __PACKAGE__->belongs_to( > 'priceList' =>'WEBEXPERTO::Modulos::Catalogue::Model::PriceList', > {'foreign.id' => 'self.idPriceList' }, > ); > 1; > > > > #CODE > my @productPrefetch; > push(@productPrefetch,'node'); > push(@productPrefetch,{'productPrice'=>'currency'});#has_many relation > my $searchAttributes = { > prefetch => \@productPrefetch, > rows => 3, > order_by=> 'RAND()', #<-- cause the bug > group_by => 'me.id_product', > }; > my $rsProducts = $schema->resultset('Product')->search({}, > $searchAttributes); > my $totalItems = $rsProducts->pager()->total_entries; > > > > What i expect is a set of products with has_many relation (productPrice) > loaded with all prices. What i get is three products but eachone repeted > 3 times, > seems that group_by is not working. > > Sql query looks like this: > SELECT me.id_product, me.id_node, me.name_product, > me.description_product, me.is_actived_product, > me.datetime_added_product, me.datetime_last_modified_product, > me.date_start_publish_product, me.date_finish_publish_product, > me.imagen_product, me.resumen_product, me.codigo_product, > me.destacado_product, me.oferta_product, me.skin_name, productPrice.id, > productPrice.idProduct, productPrice.idPriceList, > productPrice.idCurrency, productPrice.value, currency.id, currency.name, > currency.symbol FROM > (SELECT me.id_product, me.id_node, me.name_product, > me.description_product, me.is_actived_product, > me.datetime_added_product, me.datetime_last_modified_product, > me.date_start_publish_product, me.date_finish_publish_product, > me.imagen_product, me.resumen_product, me.codigo_product, > me.destacado_product, me.oferta_product, me.skin_name, me.stock_product, > me.iva_product, me.marca_product FROM catalogue_products me GROUP BY > me.id_product ORDER BY RAND() LIMIT 3) me > LEFT JOIN catalogue_productPrices productPrice ON productPrice.idProduct > = me.id_product LEFT JOIN catalogue_currencies currency ON currency.id = > productPrice.idCurrency ORDER BY RAND(), productPrice.idProduct; > > The last 'ORDER BY RAND()' mix up the product results (its seems like > group_by is ignored). If you dont specify order_by attribute (with > 'RAND()') its works just fine. > > If you need more details please let me know. Thanks again.
First of all this has nothing to do with GROUP BY (which is espected in the innermost select). In order to resolve this bug on the technical level, I need a proposal for SQL that *will* work in this situation. From all I know it is simply not possible to express what you want in pure SQL because: 1) All rows relevant to the same catalogue_product need to be grouped together (appear one after another). For this the order_by criteria for the outermost select *must* be "by PK/Unique column(s) of catalogue_product. This requirement is necessary to make ->next work (we fetch rows until the master-PK changes) 2) You want a random ordering of rows which is in direct contradiction with the above requirement. You either: 2a) Make the outermost select ordered by your criteria, which messes up the sequencin of different catalogue_products an gies you the result you observe 2b) Make the inner catalogue_product-only select ordered by your criteria - the outer order_by will reorder the results of the join again 2c) Make the inner catalogue_product-only select ordered by your desired criteria and DO NOT order the outermost join result - this will result in non-deterministic order of the final result, depending on how the rows are atually stored in the database. So there - 3 scenarios I can think of, none of which really work. If you can express it in *deterministic* SQL - I can make DBIC execute it, no questions asked. Cheers
Subject: [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [rt.cpan.org #54949] Search with has_many relation fails at using ORDER BY RAND()
Date: Thu, 25 Feb 2010 02:03:57 -0300 (ART)
To: "macevedo via RT" <bug-DBIx-Class [...] rt.cpan.org>
From: macevedo [...] web-experto.com.ar
Download (untitled) / with headers
text/plain 179b
En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Subject: [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [rt.cpan.org #54949] Search with has_many relation fails at using ORDER BY RAND()
Date: Thu, 25 Feb 2010 03:57:04 -0500
To: soporte [...] web-experto.com.ar
From: "macevedo via RT" <bug-DBIx-Class [...] rt.cpan.org>
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 5.4k
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > On Wed Feb 24 15:24:56 2010, webexperto wrote: Show quoted text
> Hi, first i apologies about my english, i will try to express my self > the best i can. I found this issue that i detail next. Thanks > > DBIx-Class: DBIx-Class-0.08119 > Perl Version: 5.8 > OS: Ubuntu 8.10 > > package WEBEXPERTO::Modulos::Catalogue::Model::Product; > use strict; > use warnings; > use base qw/DBIx::Class/; > use base qw/WEBEXPERTO::Core::MEB::Model::Model/; > __PACKAGE__->load_components(qw/PK::Auto ResultSetManager Core/); > __PACKAGE__->table('catalogue_products'); > __PACKAGE__->add_columns(qw/id_product id_node name_product > description_product is_actived_product datetime_added_product > datetime_last_modified_product date_start_publish_product > date_finish_publish_product imagen_product resumen_product > codigo_product destacado_product oferta_product skin_name/); > __PACKAGE__->set_primary_key(qw/ id_product /); > __PACKAGE__->has_many( > 'productPrice' => > 'WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice', > {'foreign.idProduct' => 'self.id_product'}, > {cascade_delete => 0}, > ); > __PACKAGE__->has_one( > 'node' => 'WEBEXPERTO::Modulos::Catalogue::Model::Node', > {'foreign.id' => 'self.id_node'}, > {cascade_delete => 0}, > ); > 1; > > package WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice; > use strict; > use warnings; > use base qw/DBIx::Class/; > __PACKAGE__->load_components(qw/PK::Auto Core/); > __PACKAGE__->table('catalogue_productPrices'); > __PACKAGE__->add_columns(qw/id idProduct idPriceList idCurrency value/); > __PACKAGE__->set_primary_key(qw/ id /); > __PACKAGE__->has_one( > 'currency' =>'WEBEXPERTO::Modulos::Catalogue::Model::Currency', > {'foreign.id' => 'self.idCurrency' }, > ); > > __PACKAGE__->belongs_to( > 'priceList' =>'WEBEXPERTO::Modulos::Catalogue::Model::PriceList', > {'foreign.id' => 'self.idPriceList' }, > ); > 1; > > > > #CODE > my @productPrefetch; > push(@productPrefetch,'node'); > push(@productPrefetch,{'productPrice'=>'currency'});#has_many relation > my $searchAttributes = { > prefetch => \@productPrefetch, > rows => 3, > order_by=> 'RAND()', #<-- cause the bug > group_by => 'me.id_product', > }; > my $rsProducts = $schema->resultset('Product')->search({}, > $searchAttributes); > my $totalItems = $rsProducts->pager()->total_entries; > > > > What i expect is a set of products with has_many relation (productPrice) > loaded with all prices. What i get is three products but eachone repeted > 3 times, > seems that group_by is not working. > > Sql query looks like this: > SELECT me.id_product, me.id_node, me.name_product, > me.description_product, me.is_actived_product, > me.datetime_added_product, me.datetime_last_modified_product, > me.date_start_publish_product, me.date_finish_publish_product, > me.imagen_product, me.resumen_product, me.codigo_product, > me.destacado_product, me.oferta_product, me.skin_name, productPrice.id, > productPrice.idProduct, productPrice.idPriceList, > productPrice.idCurrency, productPrice.value, currency.id, currency.name, > currency.symbol FROM > (SELECT me.id_product, me.id_node, me.name_product, > me.description_product, me.is_actived_product, > me.datetime_added_product, me.datetime_last_modified_product, > me.date_start_publish_product, me.date_finish_publish_product, > me.imagen_product, me.resumen_product, me.codigo_product, > me.destacado_product, me.oferta_product, me.skin_name, me.stock_product, > me.iva_product, me.marca_product FROM catalogue_products me GROUP BY > me.id_product ORDER BY RAND() LIMIT 3) me > LEFT JOIN catalogue_productPrices productPrice ON productPrice.idProduct > = me.id_product LEFT JOIN catalogue_currencies currency ON currency.id = > productPrice.idCurrency ORDER BY RAND(), productPrice.idProduct; > > The last 'ORDER BY RAND()' mix up the product results (its seems like > group_by is ignored). If you dont specify order_by attribute (with > 'RAND()') its works just fine. > > If you need more details please let me know. Thanks again.
First of all this has nothing to do with GROUP BY (which is espected in the innermost select). In order to resolve this bug on the technical level, I need a proposal for SQL that *will* work in this situation. From all I know it is simply not possible to express what you want in pure SQL because: 1) All rows relevant to the same catalogue_product need to be grouped together (appear one after another). For this the order_by criteria for the outermost select *must* be "by PK/Unique column(s) of catalogue_product. This requirement is necessary to make ->next work (we fetch rows until the master-PK changes) 2) You want a random ordering of rows which is in direct contradiction with the above requirement. You either: 2a) Make the outermost select ordered by your criteria, which messes up the sequencin of different catalogue_products an gies you the result you observe 2b) Make the inner catalogue_product-only select ordered by your criteria - the outer order_by will reorder the results of the join again 2c) Make the inner catalogue_product-only select ordered by your desired criteria and DO NOT order the outermost join result - this will result in non-deterministic order of the final result, depending on how the rows are atually stored in the database. So there - 3 scenarios I can think of, none of which really work. If you can express it in *deterministic* SQL - I can make DBIC execute it, no questions asked. Cheers
Subject: [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [rt.cpan.org #54949] Search with has_many relation fails at using ORDER BY RAND()
Date: Thu, 25 Feb 2010 02:04:58 -0300 (ART)
To: "macevedo via RT" <bug-DBIx-Class [...] rt.cpan.org>
From: macevedo [...] web-experto.com.ar
Download (untitled) / with headers
text/plain 179b
En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Subject: [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [rt.cpan.org #54949] Search with has_many relation fails at using ORDER BY RAND()
Date: Thu, 25 Feb 2010 03:58:05 -0500
To: soporte [...] web-experto.com.ar
From: "macevedo via RT" <bug-DBIx-Class [...] rt.cpan.org>
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 5.4k
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > On Wed Feb 24 15:24:56 2010, webexperto wrote: Show quoted text
> Hi, first i apologies about my english, i will try to express my self > the best i can. I found this issue that i detail next. Thanks > > DBIx-Class: DBIx-Class-0.08119 > Perl Version: 5.8 > OS: Ubuntu 8.10 > > package WEBEXPERTO::Modulos::Catalogue::Model::Product; > use strict; > use warnings; > use base qw/DBIx::Class/; > use base qw/WEBEXPERTO::Core::MEB::Model::Model/; > __PACKAGE__->load_components(qw/PK::Auto ResultSetManager Core/); > __PACKAGE__->table('catalogue_products'); > __PACKAGE__->add_columns(qw/id_product id_node name_product > description_product is_actived_product datetime_added_product > datetime_last_modified_product date_start_publish_product > date_finish_publish_product imagen_product resumen_product > codigo_product destacado_product oferta_product skin_name/); > __PACKAGE__->set_primary_key(qw/ id_product /); > __PACKAGE__->has_many( > 'productPrice' => > 'WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice', > {'foreign.idProduct' => 'self.id_product'}, > {cascade_delete => 0}, > ); > __PACKAGE__->has_one( > 'node' => 'WEBEXPERTO::Modulos::Catalogue::Model::Node', > {'foreign.id' => 'self.id_node'}, > {cascade_delete => 0}, > ); > 1; > > package WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice; > use strict; > use warnings; > use base qw/DBIx::Class/; > __PACKAGE__->load_components(qw/PK::Auto Core/); > __PACKAGE__->table('catalogue_productPrices'); > __PACKAGE__->add_columns(qw/id idProduct idPriceList idCurrency value/); > __PACKAGE__->set_primary_key(qw/ id /); > __PACKAGE__->has_one( > 'currency' =>'WEBEXPERTO::Modulos::Catalogue::Model::Currency', > {'foreign.id' => 'self.idCurrency' }, > ); > > __PACKAGE__->belongs_to( > 'priceList' =>'WEBEXPERTO::Modulos::Catalogue::Model::PriceList', > {'foreign.id' => 'self.idPriceList' }, > ); > 1; > > > > #CODE > my @productPrefetch; > push(@productPrefetch,'node'); > push(@productPrefetch,{'productPrice'=>'currency'});#has_many relation > my $searchAttributes = { > prefetch => \@productPrefetch, > rows => 3, > order_by=> 'RAND()', #<-- cause the bug > group_by => 'me.id_product', > }; > my $rsProducts = $schema->resultset('Product')->search({}, > $searchAttributes); > my $totalItems = $rsProducts->pager()->total_entries; > > > > What i expect is a set of products with has_many relation (productPrice) > loaded with all prices. What i get is three products but eachone repeted > 3 times, > seems that group_by is not working. > > Sql query looks like this: > SELECT me.id_product, me.id_node, me.name_product, > me.description_product, me.is_actived_product, > me.datetime_added_product, me.datetime_last_modified_product, > me.date_start_publish_product, me.date_finish_publish_product, > me.imagen_product, me.resumen_product, me.codigo_product, > me.destacado_product, me.oferta_product, me.skin_name, productPrice.id, > productPrice.idProduct, productPrice.idPriceList, > productPrice.idCurrency, productPrice.value, currency.id, currency.name, > currency.symbol FROM > (SELECT me.id_product, me.id_node, me.name_product, > me.description_product, me.is_actived_product, > me.datetime_added_product, me.datetime_last_modified_product, > me.date_start_publish_product, me.date_finish_publish_product, > me.imagen_product, me.resumen_product, me.codigo_product, > me.destacado_product, me.oferta_product, me.skin_name, me.stock_product, > me.iva_product, me.marca_product FROM catalogue_products me GROUP BY > me.id_product ORDER BY RAND() LIMIT 3) me > LEFT JOIN catalogue_productPrices productPrice ON productPrice.idProduct > = me.id_product LEFT JOIN catalogue_currencies currency ON currency.id = > productPrice.idCurrency ORDER BY RAND(), productPrice.idProduct; > > The last 'ORDER BY RAND()' mix up the product results (its seems like > group_by is ignored). If you dont specify order_by attribute (with > 'RAND()') its works just fine. > > If you need more details please let me know. Thanks again.
First of all this has nothing to do with GROUP BY (which is espected in the innermost select). In order to resolve this bug on the technical level, I need a proposal for SQL that *will* work in this situation. From all I know it is simply not possible to express what you want in pure SQL because: 1) All rows relevant to the same catalogue_product need to be grouped together (appear one after another). For this the order_by criteria for the outermost select *must* be "by PK/Unique column(s) of catalogue_product. This requirement is necessary to make ->next work (we fetch rows until the master-PK changes) 2) You want a random ordering of rows which is in direct contradiction with the above requirement. You either: 2a) Make the outermost select ordered by your criteria, which messes up the sequencin of different catalogue_products an gies you the result you observe 2b) Make the inner catalogue_product-only select ordered by your criteria - the outer order_by will reorder the results of the join again 2c) Make the inner catalogue_product-only select ordered by your desired criteria and DO NOT order the outermost join result - this will result in non-deterministic order of the final result, depending on how the rows are atually stored in the database. So there - 3 scenarios I can think of, none of which really work. If you can express it in *deterministic* SQL - I can make DBIC execute it, no questions asked. Cheers
Subject: [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [rt.cpan.org #54949] Search with has_many relation fails at using ORDER BY RAND()
Date: Thu, 25 Feb 2010 02:06:04 -0300 (ART)
To: "macevedo via RT" <bug-DBIx-Class [...] rt.cpan.org>
From: macevedo [...] web-experto.com.ar
Download (untitled) / with headers
text/plain 179b
En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Subject: [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [rt.cpan.org #54949] Search with has_many relation fails at using ORDER BY RAND()
Date: Thu, 25 Feb 2010 03:59:09 -0500
To: soporte [...] web-experto.com.ar
From: "macevedo via RT" <bug-DBIx-Class [...] rt.cpan.org>
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 5.4k
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > On Wed Feb 24 15:24:56 2010, webexperto wrote: Show quoted text
> Hi, first i apologies about my english, i will try to express my self > the best i can. I found this issue that i detail next. Thanks > > DBIx-Class: DBIx-Class-0.08119 > Perl Version: 5.8 > OS: Ubuntu 8.10 > > package WEBEXPERTO::Modulos::Catalogue::Model::Product; > use strict; > use warnings; > use base qw/DBIx::Class/; > use base qw/WEBEXPERTO::Core::MEB::Model::Model/; > __PACKAGE__->load_components(qw/PK::Auto ResultSetManager Core/); > __PACKAGE__->table('catalogue_products'); > __PACKAGE__->add_columns(qw/id_product id_node name_product > description_product is_actived_product datetime_added_product > datetime_last_modified_product date_start_publish_product > date_finish_publish_product imagen_product resumen_product > codigo_product destacado_product oferta_product skin_name/); > __PACKAGE__->set_primary_key(qw/ id_product /); > __PACKAGE__->has_many( > 'productPrice' => > 'WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice', > {'foreign.idProduct' => 'self.id_product'}, > {cascade_delete => 0}, > ); > __PACKAGE__->has_one( > 'node' => 'WEBEXPERTO::Modulos::Catalogue::Model::Node', > {'foreign.id' => 'self.id_node'}, > {cascade_delete => 0}, > ); > 1; > > package WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice; > use strict; > use warnings; > use base qw/DBIx::Class/; > __PACKAGE__->load_components(qw/PK::Auto Core/); > __PACKAGE__->table('catalogue_productPrices'); > __PACKAGE__->add_columns(qw/id idProduct idPriceList idCurrency value/); > __PACKAGE__->set_primary_key(qw/ id /); > __PACKAGE__->has_one( > 'currency' =>'WEBEXPERTO::Modulos::Catalogue::Model::Currency', > {'foreign.id' => 'self.idCurrency' }, > ); > > __PACKAGE__->belongs_to( > 'priceList' =>'WEBEXPERTO::Modulos::Catalogue::Model::PriceList', > {'foreign.id' => 'self.idPriceList' }, > ); > 1; > > > > #CODE > my @productPrefetch; > push(@productPrefetch,'node'); > push(@productPrefetch,{'productPrice'=>'currency'});#has_many relation > my $searchAttributes = { > prefetch => \@productPrefetch, > rows => 3, > order_by=> 'RAND()', #<-- cause the bug > group_by => 'me.id_product', > }; > my $rsProducts = $schema->resultset('Product')->search({}, > $searchAttributes); > my $totalItems = $rsProducts->pager()->total_entries; > > > > What i expect is a set of products with has_many relation (productPrice) > loaded with all prices. What i get is three products but eachone repeted > 3 times, > seems that group_by is not working. > > Sql query looks like this: > SELECT me.id_product, me.id_node, me.name_product, > me.description_product, me.is_actived_product, > me.datetime_added_product, me.datetime_last_modified_product, > me.date_start_publish_product, me.date_finish_publish_product, > me.imagen_product, me.resumen_product, me.codigo_product, > me.destacado_product, me.oferta_product, me.skin_name, productPrice.id, > productPrice.idProduct, productPrice.idPriceList, > productPrice.idCurrency, productPrice.value, currency.id, currency.name, > currency.symbol FROM > (SELECT me.id_product, me.id_node, me.name_product, > me.description_product, me.is_actived_product, > me.datetime_added_product, me.datetime_last_modified_product, > me.date_start_publish_product, me.date_finish_publish_product, > me.imagen_product, me.resumen_product, me.codigo_product, > me.destacado_product, me.oferta_product, me.skin_name, me.stock_product, > me.iva_product, me.marca_product FROM catalogue_products me GROUP BY > me.id_product ORDER BY RAND() LIMIT 3) me > LEFT JOIN catalogue_productPrices productPrice ON productPrice.idProduct > = me.id_product LEFT JOIN catalogue_currencies currency ON currency.id = > productPrice.idCurrency ORDER BY RAND(), productPrice.idProduct; > > The last 'ORDER BY RAND()' mix up the product results (its seems like > group_by is ignored). If you dont specify order_by attribute (with > 'RAND()') its works just fine. > > If you need more details please let me know. Thanks again.
First of all this has nothing to do with GROUP BY (which is espected in the innermost select). In order to resolve this bug on the technical level, I need a proposal for SQL that *will* work in this situation. From all I know it is simply not possible to express what you want in pure SQL because: 1) All rows relevant to the same catalogue_product need to be grouped together (appear one after another). For this the order_by criteria for the outermost select *must* be "by PK/Unique column(s) of catalogue_product. This requirement is necessary to make ->next work (we fetch rows until the master-PK changes) 2) You want a random ordering of rows which is in direct contradiction with the above requirement. You either: 2a) Make the outermost select ordered by your criteria, which messes up the sequencin of different catalogue_products an gies you the result you observe 2b) Make the inner catalogue_product-only select ordered by your criteria - the outer order_by will reorder the results of the join again 2c) Make the inner catalogue_product-only select ordered by your desired criteria and DO NOT order the outermost join result - this will result in non-deterministic order of the final result, depending on how the rows are atually stored in the database. So there - 3 scenarios I can think of, none of which really work. If you can express it in *deterministic* SQL - I can make DBIC execute it, no questions asked. Cheers
Subject: [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [rt.cpan.org #54949] Search with has_many relation fails at using ORDER BY RAND()
Date: Thu, 25 Feb 2010 02:07:17 -0300 (ART)
To: "macevedo via RT" <bug-DBIx-Class [...] rt.cpan.org>
From: macevedo [...] web-experto.com.ar
Download (untitled) / with headers
text/plain 179b
En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Subject: [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [rt.cpan.org #54949] Search with has_many relation fails at using ORDER BY RAND()
Date: Thu, 25 Feb 2010 04:00:22 -0500
To: soporte [...] web-experto.com.ar
From: "macevedo via RT" <bug-DBIx-Class [...] rt.cpan.org>
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 5.4k
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > On Wed Feb 24 15:24:56 2010, webexperto wrote: Show quoted text
> Hi, first i apologies about my english, i will try to express my self > the best i can. I found this issue that i detail next. Thanks > > DBIx-Class: DBIx-Class-0.08119 > Perl Version: 5.8 > OS: Ubuntu 8.10 > > package WEBEXPERTO::Modulos::Catalogue::Model::Product; > use strict; > use warnings; > use base qw/DBIx::Class/; > use base qw/WEBEXPERTO::Core::MEB::Model::Model/; > __PACKAGE__->load_components(qw/PK::Auto ResultSetManager Core/); > __PACKAGE__->table('catalogue_products'); > __PACKAGE__->add_columns(qw/id_product id_node name_product > description_product is_actived_product datetime_added_product > datetime_last_modified_product date_start_publish_product > date_finish_publish_product imagen_product resumen_product > codigo_product destacado_product oferta_product skin_name/); > __PACKAGE__->set_primary_key(qw/ id_product /); > __PACKAGE__->has_many( > 'productPrice' => > 'WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice', > {'foreign.idProduct' => 'self.id_product'}, > {cascade_delete => 0}, > ); > __PACKAGE__->has_one( > 'node' => 'WEBEXPERTO::Modulos::Catalogue::Model::Node', > {'foreign.id' => 'self.id_node'}, > {cascade_delete => 0}, > ); > 1; > > package WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice; > use strict; > use warnings; > use base qw/DBIx::Class/; > __PACKAGE__->load_components(qw/PK::Auto Core/); > __PACKAGE__->table('catalogue_productPrices'); > __PACKAGE__->add_columns(qw/id idProduct idPriceList idCurrency value/); > __PACKAGE__->set_primary_key(qw/ id /); > __PACKAGE__->has_one( > 'currency' =>'WEBEXPERTO::Modulos::Catalogue::Model::Currency', > {'foreign.id' => 'self.idCurrency' }, > ); > > __PACKAGE__->belongs_to( > 'priceList' =>'WEBEXPERTO::Modulos::Catalogue::Model::PriceList', > {'foreign.id' => 'self.idPriceList' }, > ); > 1; > > > > #CODE > my @productPrefetch; > push(@productPrefetch,'node'); > push(@productPrefetch,{'productPrice'=>'currency'});#has_many relation > my $searchAttributes = { > prefetch => \@productPrefetch, > rows => 3, > order_by=> 'RAND()', #<-- cause the bug > group_by => 'me.id_product', > }; > my $rsProducts = $schema->resultset('Product')->search({}, > $searchAttributes); > my $totalItems = $rsProducts->pager()->total_entries; > > > > What i expect is a set of products with has_many relation (productPrice) > loaded with all prices. What i get is three products but eachone repeted > 3 times, > seems that group_by is not working. > > Sql query looks like this: > SELECT me.id_product, me.id_node, me.name_product, > me.description_product, me.is_actived_product, > me.datetime_added_product, me.datetime_last_modified_product, > me.date_start_publish_product, me.date_finish_publish_product, > me.imagen_product, me.resumen_product, me.codigo_product, > me.destacado_product, me.oferta_product, me.skin_name, productPrice.id, > productPrice.idProduct, productPrice.idPriceList, > productPrice.idCurrency, productPrice.value, currency.id, currency.name, > currency.symbol FROM > (SELECT me.id_product, me.id_node, me.name_product, > me.description_product, me.is_actived_product, > me.datetime_added_product, me.datetime_last_modified_product, > me.date_start_publish_product, me.date_finish_publish_product, > me.imagen_product, me.resumen_product, me.codigo_product, > me.destacado_product, me.oferta_product, me.skin_name, me.stock_product, > me.iva_product, me.marca_product FROM catalogue_products me GROUP BY > me.id_product ORDER BY RAND() LIMIT 3) me > LEFT JOIN catalogue_productPrices productPrice ON productPrice.idProduct > = me.id_product LEFT JOIN catalogue_currencies currency ON currency.id = > productPrice.idCurrency ORDER BY RAND(), productPrice.idProduct; > > The last 'ORDER BY RAND()' mix up the product results (its seems like > group_by is ignored). If you dont specify order_by attribute (with > 'RAND()') its works just fine. > > If you need more details please let me know. Thanks again.
First of all this has nothing to do with GROUP BY (which is espected in the innermost select). In order to resolve this bug on the technical level, I need a proposal for SQL that *will* work in this situation. From all I know it is simply not possible to express what you want in pure SQL because: 1) All rows relevant to the same catalogue_product need to be grouped together (appear one after another). For this the order_by criteria for the outermost select *must* be "by PK/Unique column(s) of catalogue_product. This requirement is necessary to make ->next work (we fetch rows until the master-PK changes) 2) You want a random ordering of rows which is in direct contradiction with the above requirement. You either: 2a) Make the outermost select ordered by your criteria, which messes up the sequencin of different catalogue_products an gies you the result you observe 2b) Make the inner catalogue_product-only select ordered by your criteria - the outer order_by will reorder the results of the join again 2c) Make the inner catalogue_product-only select ordered by your desired criteria and DO NOT order the outermost join result - this will result in non-deterministic order of the final result, depending on how the rows are atually stored in the database. So there - 3 scenarios I can think of, none of which really work. If you can express it in *deterministic* SQL - I can make DBIC execute it, no questions asked. Cheers
Subject: [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [rt.cpan.org #54949] Search with has_many relation fails at using ORDER BY RAND()
Date: Thu, 25 Feb 2010 02:08:18 -0300 (ART)
To: "macevedo via RT" <bug-DBIx-Class [...] rt.cpan.org>
From: macevedo [...] web-experto.com.ar
Download (untitled) / with headers
text/plain 179b
En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Subject: [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [rt.cpan.org #54949] Search with has_many relation fails at using ORDER BY RAND()
Date: Thu, 25 Feb 2010 04:01:24 -0500
To: soporte [...] web-experto.com.ar
From: "macevedo via RT" <bug-DBIx-Class [...] rt.cpan.org>
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 5.4k
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > On Wed Feb 24 15:24:56 2010, webexperto wrote: Show quoted text
> Hi, first i apologies about my english, i will try to express my self > the best i can. I found this issue that i detail next. Thanks > > DBIx-Class: DBIx-Class-0.08119 > Perl Version: 5.8 > OS: Ubuntu 8.10 > > package WEBEXPERTO::Modulos::Catalogue::Model::Product; > use strict; > use warnings; > use base qw/DBIx::Class/; > use base qw/WEBEXPERTO::Core::MEB::Model::Model/; > __PACKAGE__->load_components(qw/PK::Auto ResultSetManager Core/); > __PACKAGE__->table('catalogue_products'); > __PACKAGE__->add_columns(qw/id_product id_node name_product > description_product is_actived_product datetime_added_product > datetime_last_modified_product date_start_publish_product > date_finish_publish_product imagen_product resumen_product > codigo_product destacado_product oferta_product skin_name/); > __PACKAGE__->set_primary_key(qw/ id_product /); > __PACKAGE__->has_many( > 'productPrice' => > 'WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice', > {'foreign.idProduct' => 'self.id_product'}, > {cascade_delete => 0}, > ); > __PACKAGE__->has_one( > 'node' => 'WEBEXPERTO::Modulos::Catalogue::Model::Node', > {'foreign.id' => 'self.id_node'}, > {cascade_delete => 0}, > ); > 1; > > package WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice; > use strict; > use warnings; > use base qw/DBIx::Class/; > __PACKAGE__->load_components(qw/PK::Auto Core/); > __PACKAGE__->table('catalogue_productPrices'); > __PACKAGE__->add_columns(qw/id idProduct idPriceList idCurrency value/); > __PACKAGE__->set_primary_key(qw/ id /); > __PACKAGE__->has_one( > 'currency' =>'WEBEXPERTO::Modulos::Catalogue::Model::Currency', > {'foreign.id' => 'self.idCurrency' }, > ); > > __PACKAGE__->belongs_to( > 'priceList' =>'WEBEXPERTO::Modulos::Catalogue::Model::PriceList', > {'foreign.id' => 'self.idPriceList' }, > ); > 1; > > > > #CODE > my @productPrefetch; > push(@productPrefetch,'node'); > push(@productPrefetch,{'productPrice'=>'currency'});#has_many relation > my $searchAttributes = { > prefetch => \@productPrefetch, > rows => 3, > order_by=> 'RAND()', #<-- cause the bug > group_by => 'me.id_product', > }; > my $rsProducts = $schema->resultset('Product')->search({}, > $searchAttributes); > my $totalItems = $rsProducts->pager()->total_entries; > > > > What i expect is a set of products with has_many relation (productPrice) > loaded with all prices. What i get is three products but eachone repeted > 3 times, > seems that group_by is not working. > > Sql query looks like this: > SELECT me.id_product, me.id_node, me.name_product, > me.description_product, me.is_actived_product, > me.datetime_added_product, me.datetime_last_modified_product, > me.date_start_publish_product, me.date_finish_publish_product, > me.imagen_product, me.resumen_product, me.codigo_product, > me.destacado_product, me.oferta_product, me.skin_name, productPrice.id, > productPrice.idProduct, productPrice.idPriceList, > productPrice.idCurrency, productPrice.value, currency.id, currency.name, > currency.symbol FROM > (SELECT me.id_product, me.id_node, me.name_product, > me.description_product, me.is_actived_product, > me.datetime_added_product, me.datetime_last_modified_product, > me.date_start_publish_product, me.date_finish_publish_product, > me.imagen_product, me.resumen_product, me.codigo_product, > me.destacado_product, me.oferta_product, me.skin_name, me.stock_product, > me.iva_product, me.marca_product FROM catalogue_products me GROUP BY > me.id_product ORDER BY RAND() LIMIT 3) me > LEFT JOIN catalogue_productPrices productPrice ON productPrice.idProduct > = me.id_product LEFT JOIN catalogue_currencies currency ON currency.id = > productPrice.idCurrency ORDER BY RAND(), productPrice.idProduct; > > The last 'ORDER BY RAND()' mix up the product results (its seems like > group_by is ignored). If you dont specify order_by attribute (with > 'RAND()') its works just fine. > > If you need more details please let me know. Thanks again.
First of all this has nothing to do with GROUP BY (which is espected in the innermost select). In order to resolve this bug on the technical level, I need a proposal for SQL that *will* work in this situation. From all I know it is simply not possible to express what you want in pure SQL because: 1) All rows relevant to the same catalogue_product need to be grouped together (appear one after another). For this the order_by criteria for the outermost select *must* be "by PK/Unique column(s) of catalogue_product. This requirement is necessary to make ->next work (we fetch rows until the master-PK changes) 2) You want a random ordering of rows which is in direct contradiction with the above requirement. You either: 2a) Make the outermost select ordered by your criteria, which messes up the sequencin of different catalogue_products an gies you the result you observe 2b) Make the inner catalogue_product-only select ordered by your criteria - the outer order_by will reorder the results of the join again 2c) Make the inner catalogue_product-only select ordered by your desired criteria and DO NOT order the outermost join result - this will result in non-deterministic order of the final result, depending on how the rows are atually stored in the database. So there - 3 scenarios I can think of, none of which really work. If you can express it in *deterministic* SQL - I can make DBIC execute it, no questions asked. Cheers
Subject: [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [rt.cpan.org #54949] Search with has_many relation fails at using ORDER BY RAND()
Date: Thu, 25 Feb 2010 02:09:25 -0300 (ART)
To: "macevedo via RT" <bug-DBIx-Class [...] rt.cpan.org>
From: macevedo [...] web-experto.com.ar
Download (untitled) / with headers
text/plain 179b
En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Subject: [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [rt.cpan.org #54949] Search with has_many relation fails at using ORDER BY RAND()
Date: Thu, 25 Feb 2010 04:02:31 -0500
To: soporte [...] web-experto.com.ar
From: "macevedo via RT" <bug-DBIx-Class [...] rt.cpan.org>
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 5.4k
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > On Wed Feb 24 15:24:56 2010, webexperto wrote: Show quoted text
> Hi, first i apologies about my english, i will try to express my self > the best i can. I found this issue that i detail next. Thanks > > DBIx-Class: DBIx-Class-0.08119 > Perl Version: 5.8 > OS: Ubuntu 8.10 > > package WEBEXPERTO::Modulos::Catalogue::Model::Product; > use strict; > use warnings; > use base qw/DBIx::Class/; > use base qw/WEBEXPERTO::Core::MEB::Model::Model/; > __PACKAGE__->load_components(qw/PK::Auto ResultSetManager Core/); > __PACKAGE__->table('catalogue_products'); > __PACKAGE__->add_columns(qw/id_product id_node name_product > description_product is_actived_product datetime_added_product > datetime_last_modified_product date_start_publish_product > date_finish_publish_product imagen_product resumen_product > codigo_product destacado_product oferta_product skin_name/); > __PACKAGE__->set_primary_key(qw/ id_product /); > __PACKAGE__->has_many( > 'productPrice' => > 'WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice', > {'foreign.idProduct' => 'self.id_product'}, > {cascade_delete => 0}, > ); > __PACKAGE__->has_one( > 'node' => 'WEBEXPERTO::Modulos::Catalogue::Model::Node', > {'foreign.id' => 'self.id_node'}, > {cascade_delete => 0}, > ); > 1; > > package WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice; > use strict; > use warnings; > use base qw/DBIx::Class/; > __PACKAGE__->load_components(qw/PK::Auto Core/); > __PACKAGE__->table('catalogue_productPrices'); > __PACKAGE__->add_columns(qw/id idProduct idPriceList idCurrency value/); > __PACKAGE__->set_primary_key(qw/ id /); > __PACKAGE__->has_one( > 'currency' =>'WEBEXPERTO::Modulos::Catalogue::Model::Currency', > {'foreign.id' => 'self.idCurrency' }, > ); > > __PACKAGE__->belongs_to( > 'priceList' =>'WEBEXPERTO::Modulos::Catalogue::Model::PriceList', > {'foreign.id' => 'self.idPriceList' }, > ); > 1; > > > > #CODE > my @productPrefetch; > push(@productPrefetch,'node'); > push(@productPrefetch,{'productPrice'=>'currency'});#has_many relation > my $searchAttributes = { > prefetch => \@productPrefetch, > rows => 3, > order_by=> 'RAND()', #<-- cause the bug > group_by => 'me.id_product', > }; > my $rsProducts = $schema->resultset('Product')->search({}, > $searchAttributes); > my $totalItems = $rsProducts->pager()->total_entries; > > > > What i expect is a set of products with has_many relation (productPrice) > loaded with all prices. What i get is three products but eachone repeted > 3 times, > seems that group_by is not working. > > Sql query looks like this: > SELECT me.id_product, me.id_node, me.name_product, > me.description_product, me.is_actived_product, > me.datetime_added_product, me.datetime_last_modified_product, > me.date_start_publish_product, me.date_finish_publish_product, > me.imagen_product, me.resumen_product, me.codigo_product, > me.destacado_product, me.oferta_product, me.skin_name, productPrice.id, > productPrice.idProduct, productPrice.idPriceList, > productPrice.idCurrency, productPrice.value, currency.id, currency.name, > currency.symbol FROM > (SELECT me.id_product, me.id_node, me.name_product, > me.description_product, me.is_actived_product, > me.datetime_added_product, me.datetime_last_modified_product, > me.date_start_publish_product, me.date_finish_publish_product, > me.imagen_product, me.resumen_product, me.codigo_product, > me.destacado_product, me.oferta_product, me.skin_name, me.stock_product, > me.iva_product, me.marca_product FROM catalogue_products me GROUP BY > me.id_product ORDER BY RAND() LIMIT 3) me > LEFT JOIN catalogue_productPrices productPrice ON productPrice.idProduct > = me.id_product LEFT JOIN catalogue_currencies currency ON currency.id = > productPrice.idCurrency ORDER BY RAND(), productPrice.idProduct; > > The last 'ORDER BY RAND()' mix up the product results (its seems like > group_by is ignored). If you dont specify order_by attribute (with > 'RAND()') its works just fine. > > If you need more details please let me know. Thanks again.
First of all this has nothing to do with GROUP BY (which is espected in the innermost select). In order to resolve this bug on the technical level, I need a proposal for SQL that *will* work in this situation. From all I know it is simply not possible to express what you want in pure SQL because: 1) All rows relevant to the same catalogue_product need to be grouped together (appear one after another). For this the order_by criteria for the outermost select *must* be "by PK/Unique column(s) of catalogue_product. This requirement is necessary to make ->next work (we fetch rows until the master-PK changes) 2) You want a random ordering of rows which is in direct contradiction with the above requirement. You either: 2a) Make the outermost select ordered by your criteria, which messes up the sequencin of different catalogue_products an gies you the result you observe 2b) Make the inner catalogue_product-only select ordered by your criteria - the outer order_by will reorder the results of the join again 2c) Make the inner catalogue_product-only select ordered by your desired criteria and DO NOT order the outermost join result - this will result in non-deterministic order of the final result, depending on how the rows are atually stored in the database. So there - 3 scenarios I can think of, none of which really work. If you can express it in *deterministic* SQL - I can make DBIC execute it, no questions asked. Cheers
Subject: [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [rt.cpan.org #54949] Search with has_many relation fails at using ORDER BY RAND()
Date: Thu, 25 Feb 2010 02:10:25 -0300 (ART)
To: "macevedo via RT" <bug-DBIx-Class [...] rt.cpan.org>
From: macevedo [...] web-experto.com.ar
Download (untitled) / with headers
text/plain 179b
En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Subject: [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [rt.cpan.org #54949] Search with has_many relation fails at using ORDER BY RAND()
Date: Thu, 25 Feb 2010 04:03:32 -0500
To: soporte [...] web-experto.com.ar
From: "macevedo via RT" <bug-DBIx-Class [...] rt.cpan.org>
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 5.4k
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > On Wed Feb 24 15:24:56 2010, webexperto wrote: Show quoted text
> Hi, first i apologies about my english, i will try to express my self > the best i can. I found this issue that i detail next. Thanks > > DBIx-Class: DBIx-Class-0.08119 > Perl Version: 5.8 > OS: Ubuntu 8.10 > > package WEBEXPERTO::Modulos::Catalogue::Model::Product; > use strict; > use warnings; > use base qw/DBIx::Class/; > use base qw/WEBEXPERTO::Core::MEB::Model::Model/; > __PACKAGE__->load_components(qw/PK::Auto ResultSetManager Core/); > __PACKAGE__->table('catalogue_products'); > __PACKAGE__->add_columns(qw/id_product id_node name_product > description_product is_actived_product datetime_added_product > datetime_last_modified_product date_start_publish_product > date_finish_publish_product imagen_product resumen_product > codigo_product destacado_product oferta_product skin_name/); > __PACKAGE__->set_primary_key(qw/ id_product /); > __PACKAGE__->has_many( > 'productPrice' => > 'WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice', > {'foreign.idProduct' => 'self.id_product'}, > {cascade_delete => 0}, > ); > __PACKAGE__->has_one( > 'node' => 'WEBEXPERTO::Modulos::Catalogue::Model::Node', > {'foreign.id' => 'self.id_node'}, > {cascade_delete => 0}, > ); > 1; > > package WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice; > use strict; > use warnings; > use base qw/DBIx::Class/; > __PACKAGE__->load_components(qw/PK::Auto Core/); > __PACKAGE__->table('catalogue_productPrices'); > __PACKAGE__->add_columns(qw/id idProduct idPriceList idCurrency value/); > __PACKAGE__->set_primary_key(qw/ id /); > __PACKAGE__->has_one( > 'currency' =>'WEBEXPERTO::Modulos::Catalogue::Model::Currency', > {'foreign.id' => 'self.idCurrency' }, > ); > > __PACKAGE__->belongs_to( > 'priceList' =>'WEBEXPERTO::Modulos::Catalogue::Model::PriceList', > {'foreign.id' => 'self.idPriceList' }, > ); > 1; > > > > #CODE > my @productPrefetch; > push(@productPrefetch,'node'); > push(@productPrefetch,{'productPrice'=>'currency'});#has_many relation > my $searchAttributes = { > prefetch => \@productPrefetch, > rows => 3, > order_by=> 'RAND()', #<-- cause the bug > group_by => 'me.id_product', > }; > my $rsProducts = $schema->resultset('Product')->search({}, > $searchAttributes); > my $totalItems = $rsProducts->pager()->total_entries; > > > > What i expect is a set of products with has_many relation (productPrice) > loaded with all prices. What i get is three products but eachone repeted > 3 times, > seems that group_by is not working. > > Sql query looks like this: > SELECT me.id_product, me.id_node, me.name_product, > me.description_product, me.is_actived_product, > me.datetime_added_product, me.datetime_last_modified_product, > me.date_start_publish_product, me.date_finish_publish_product, > me.imagen_product, me.resumen_product, me.codigo_product, > me.destacado_product, me.oferta_product, me.skin_name, productPrice.id, > productPrice.idProduct, productPrice.idPriceList, > productPrice.idCurrency, productPrice.value, currency.id, currency.name, > currency.symbol FROM > (SELECT me.id_product, me.id_node, me.name_product, > me.description_product, me.is_actived_product, > me.datetime_added_product, me.datetime_last_modified_product, > me.date_start_publish_product, me.date_finish_publish_product, > me.imagen_product, me.resumen_product, me.codigo_product, > me.destacado_product, me.oferta_product, me.skin_name, me.stock_product, > me.iva_product, me.marca_product FROM catalogue_products me GROUP BY > me.id_product ORDER BY RAND() LIMIT 3) me > LEFT JOIN catalogue_productPrices productPrice ON productPrice.idProduct > = me.id_product LEFT JOIN catalogue_currencies currency ON currency.id = > productPrice.idCurrency ORDER BY RAND(), productPrice.idProduct; > > The last 'ORDER BY RAND()' mix up the product results (its seems like > group_by is ignored). If you dont specify order_by attribute (with > 'RAND()') its works just fine. > > If you need more details please let me know. Thanks again.
First of all this has nothing to do with GROUP BY (which is espected in the innermost select). In order to resolve this bug on the technical level, I need a proposal for SQL that *will* work in this situation. From all I know it is simply not possible to express what you want in pure SQL because: 1) All rows relevant to the same catalogue_product need to be grouped together (appear one after another). For this the order_by criteria for the outermost select *must* be "by PK/Unique column(s) of catalogue_product. This requirement is necessary to make ->next work (we fetch rows until the master-PK changes) 2) You want a random ordering of rows which is in direct contradiction with the above requirement. You either: 2a) Make the outermost select ordered by your criteria, which messes up the sequencin of different catalogue_products an gies you the result you observe 2b) Make the inner catalogue_product-only select ordered by your criteria - the outer order_by will reorder the results of the join again 2c) Make the inner catalogue_product-only select ordered by your desired criteria and DO NOT order the outermost join result - this will result in non-deterministic order of the final result, depending on how the rows are atually stored in the database. So there - 3 scenarios I can think of, none of which really work. If you can express it in *deterministic* SQL - I can make DBIC execute it, no questions asked. Cheers
Subject: [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [rt.cpan.org #54949] Search with has_many relation fails at using ORDER BY RAND()
Date: Thu, 25 Feb 2010 02:11:25 -0300 (ART)
To: "macevedo via RT" <bug-DBIx-Class [...] rt.cpan.org>
From: macevedo [...] web-experto.com.ar
Download (untitled) / with headers
text/plain 179b
En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Subject: [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [Autoreply] [rt.cpan.org #54949] Search with has_many relation fails at using ORDER BY RAND()
Date: Thu, 25 Feb 2010 04:04:34 -0500
To: soporte [...] web-experto.com.ar
From: "macevedo via RT" <bug-DBIx-Class [...] rt.cpan.org>
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 237b
<URL: http://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 238b
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > En este momento me encuentro de vacaciones, por favor comunicarse al 527-1070 o enviar el email a soporte@web-experto.com.ar Saludo Atte. Martín Acevedo - Atención al cliente
Download (untitled) / with headers
text/plain 5.4k
<URL: https://rt.cpan.org/Ticket/Display.html?id=54949 > On Wed Feb 24 15:24:56 2010, webexperto wrote: Show quoted text
> Hi, first i apologies about my english, i will try to express my self > the best i can. I found this issue that i detail next. Thanks > > DBIx-Class: DBIx-Class-0.08119 > Perl Version: 5.8 > OS: Ubuntu 8.10 > > package WEBEXPERTO::Modulos::Catalogue::Model::Product; > use strict; > use warnings; > use base qw/DBIx::Class/; > use base qw/WEBEXPERTO::Core::MEB::Model::Model/; > __PACKAGE__->load_components(qw/PK::Auto ResultSetManager Core/); > __PACKAGE__->table('catalogue_products'); > __PACKAGE__->add_columns(qw/id_product id_node name_product > description_product is_actived_product datetime_added_product > datetime_last_modified_product date_start_publish_product > date_finish_publish_product imagen_product resumen_product > codigo_product destacado_product oferta_product skin_name/); > __PACKAGE__->set_primary_key(qw/ id_product /); > __PACKAGE__->has_many( > 'productPrice' => > 'WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice', > {'foreign.idProduct' => 'self.id_product'}, > {cascade_delete => 0}, > ); > __PACKAGE__->has_one( > 'node' => 'WEBEXPERTO::Modulos::Catalogue::Model::Node', > {'foreign.id' => 'self.id_node'}, > {cascade_delete => 0}, > ); > 1; > > package WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice; > use strict; > use warnings; > use base qw/DBIx::Class/; > __PACKAGE__->load_components(qw/PK::Auto Core/); > __PACKAGE__->table('catalogue_productPrices'); > __PACKAGE__->add_columns(qw/id idProduct idPriceList idCurrency value/); > __PACKAGE__->set_primary_key(qw/ id /); > __PACKAGE__->has_one( > 'currency' =>'WEBEXPERTO::Modulos::Catalogue::Model::Currency', > {'foreign.id' => 'self.idCurrency' }, > ); > > __PACKAGE__->belongs_to( > 'priceList' =>'WEBEXPERTO::Modulos::Catalogue::Model::PriceList', > {'foreign.id' => 'self.idPriceList' }, > ); > 1; > > > > #CODE > my @productPrefetch; > push(@productPrefetch,'node'); > push(@productPrefetch,{'productPrice'=>'currency'});#has_many relation > my $searchAttributes = { > prefetch => \@productPrefetch, > rows => 3, > order_by=> 'RAND()', #<-- cause the bug > group_by => 'me.id_product', > }; > my $rsProducts = $schema->resultset('Product')->search({}, > $searchAttributes); > my $totalItems = $rsProducts->pager()->total_entries; > > > > What i expect is a set of products with has_many relation (productPrice) > loaded with all prices. What i get is three products but eachone repeted > 3 times, > seems that group_by is not working. > > Sql query looks like this: > SELECT me.id_product, me.id_node, me.name_product, > me.description_product, me.is_actived_product, > me.datetime_added_product, me.datetime_last_modified_product, > me.date_start_publish_product, me.date_finish_publish_product, > me.imagen_product, me.resumen_product, me.codigo_product, > me.destacado_product, me.oferta_product, me.skin_name, productPrice.id, > productPrice.idProduct, productPrice.idPriceList, > productPrice.idCurrency, productPrice.value, currency.id, currency.name, > currency.symbol FROM > (SELECT me.id_product, me.id_node, me.name_product, > me.description_product, me.is_actived_product, > me.datetime_added_product, me.datetime_last_modified_product, > me.date_start_publish_product, me.date_finish_publish_product, > me.imagen_product, me.resumen_product, me.codigo_product, > me.destacado_product, me.oferta_product, me.skin_name, me.stock_product, > me.iva_product, me.marca_product FROM catalogue_products me GROUP BY > me.id_product ORDER BY RAND() LIMIT 3) me > LEFT JOIN catalogue_productPrices productPrice ON productPrice.idProduct > = me.id_product LEFT JOIN catalogue_currencies currency ON currency.id = > productPrice.idCurrency ORDER BY RAND(), productPrice.idProduct; > > The last 'ORDER BY RAND()' mix up the product results (its seems like > group_by is ignored). If you dont specify order_by attribute (with > 'RAND()') its works just fine. > > If you need more details please let me know. Thanks again.
First of all this has nothing to do with GROUP BY (which is espected in the innermost select). In order to resolve this bug on the technical level, I need a proposal for SQL that *will* work in this situation. From all I know it is simply not possible to express what you want in pure SQL because: 1) All rows relevant to the same catalogue_product need to be grouped together (appear one after another). For this the order_by criteria for the outermost select *must* be "by PK/Unique column(s) of catalogue_product. This requirement is necessary to make ->next work (we fetch rows until the master-PK changes) 2) You want a random ordering of rows which is in direct contradiction with the above requirement. You either: 2a) Make the outermost select ordered by your criteria, which messes up the sequencin of different catalogue_products an gies you the result you observe 2b) Make the inner catalogue_product-only select ordered by your criteria - the outer order_by will reorder the results of the join again 2c) Make the inner catalogue_product-only select ordered by your desired criteria and DO NOT order the outermost join result - this will result in non-deterministic order of the final result, depending on how the rows are atually stored in the database. So there - 3 scenarios I can think of, none of which really work. If you can express it in *deterministic* SQL - I can make DBIC execute it, no questions asked. Cheers
From: soporte [...] web-experto.com.ar
Download (untitled) / with headers
text/plain 6.4k
El Jue feb 25 03:44:49 2010, RIBASUSHI escribió: Show quoted text
> On Wed Feb 24 15:24:56 2010, webexperto wrote:
> > Hi, first i apologies about my english, i will try to express my self > > the best i can. I found this issue that i detail next. Thanks > > > > DBIx-Class: DBIx-Class-0.08119 > > Perl Version: 5.8 > > OS: Ubuntu 8.10 > > > > package WEBEXPERTO::Modulos::Catalogue::Model::Product; > > use strict; > > use warnings; > > use base qw/DBIx::Class/; > > use base qw/WEBEXPERTO::Core::MEB::Model::Model/; > > __PACKAGE__->load_components(qw/PK::Auto ResultSetManager Core/); > > __PACKAGE__->table('catalogue_products'); > > __PACKAGE__->add_columns(qw/id_product id_node name_product > > description_product is_actived_product datetime_added_product > > datetime_last_modified_product date_start_publish_product > > date_finish_publish_product imagen_product resumen_product > > codigo_product destacado_product oferta_product skin_name/); > > __PACKAGE__->set_primary_key(qw/ id_product /); > > __PACKAGE__->has_many( > > 'productPrice' => > > 'WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice', > > {'foreign.idProduct' => 'self.id_product'}, > > {cascade_delete => 0}, > > ); > > __PACKAGE__->has_one( > > 'node' => 'WEBEXPERTO::Modulos::Catalogue::Model::Node', > > {'foreign.id' => 'self.id_node'}, > > {cascade_delete => 0}, > > ); > > 1; > > > > package WEBEXPERTO::Modulos::Catalogue::Model::ProductPrice; > > use strict; > > use warnings; > > use base qw/DBIx::Class/; > > __PACKAGE__->load_components(qw/PK::Auto Core/); > > __PACKAGE__->table('catalogue_productPrices'); > > __PACKAGE__->add_columns(qw/id idProduct idPriceList idCurrency value/); > > __PACKAGE__->set_primary_key(qw/ id /); > > __PACKAGE__->has_one( > > 'currency' =>'WEBEXPERTO::Modulos::Catalogue::Model::Currency', > > {'foreign.id' => 'self.idCurrency' }, > > ); > > > > __PACKAGE__->belongs_to( > > 'priceList' =>'WEBEXPERTO::Modulos::Catalogue::Model::PriceList', > > {'foreign.id' => 'self.idPriceList' }, > > ); > > 1; > > > > > > > > #CODE > > my @productPrefetch; > > push(@productPrefetch,'node'); > > push(@productPrefetch,{'productPrice'=>'currency'});#has_many relation > > my $searchAttributes = { > > prefetch => \@productPrefetch, > > rows => 3, > > order_by=> 'RAND()', #<-- cause the bug > > group_by => 'me.id_product', > > }; > > my $rsProducts = $schema->resultset('Product')->search({}, > > $searchAttributes); > > my $totalItems = $rsProducts->pager()->total_entries; > > > > > > > > What i expect is a set of products with has_many relation (productPrice) > > loaded with all prices. What i get is three products but eachone repeted > > 3 times, > > seems that group_by is not working. > > > > Sql query looks like this: > > SELECT me.id_product, me.id_node, me.name_product, > > me.description_product, me.is_actived_product, > > me.datetime_added_product, me.datetime_last_modified_product, > > me.date_start_publish_product, me.date_finish_publish_product, > > me.imagen_product, me.resumen_product, me.codigo_product, > > me.destacado_product, me.oferta_product, me.skin_name, productPrice.id, > > productPrice.idProduct, productPrice.idPriceList, > > productPrice.idCurrency, productPrice.value, currency.id, currency.name, > > currency.symbol FROM > > (SELECT me.id_product, me.id_node, me.name_product, > > me.description_product, me.is_actived_product, > > me.datetime_added_product, me.datetime_last_modified_product, > > me.date_start_publish_product, me.date_finish_publish_product, > > me.imagen_product, me.resumen_product, me.codigo_product, > > me.destacado_product, me.oferta_product, me.skin_name, me.stock_product, > > me.iva_product, me.marca_product FROM catalogue_products me GROUP BY > > me.id_product ORDER BY RAND() LIMIT 3) me > > LEFT JOIN catalogue_productPrices productPrice ON productPrice.idProduct > > = me.id_product LEFT JOIN catalogue_currencies currency ON currency.id = > > productPrice.idCurrency ORDER BY RAND(), productPrice.idProduct; > > > > The last 'ORDER BY RAND()' mix up the product results (its seems like > > group_by is ignored). If you dont specify order_by attribute (with > > 'RAND()') its works just fine. > > > > If you need more details please let me know. Thanks again.
> > First of all this has nothing to do with GROUP BY (which is espected in > the innermost select). > > In order to resolve this bug on the technical level, I need a proposal > for SQL that *will* work in this situation. From all I know it is simply > not possible to express what you want in pure SQL because: > > 1) All rows relevant to the same catalogue_product need to be grouped > together (appear one after another). For this the order_by criteria for > the outermost select *must* be "by PK/Unique column(s) of > catalogue_product. This requirement is necessary to make ->next work (we > fetch rows until the master-PK changes) > > 2) You want a random ordering of rows which is in direct contradiction > with the above requirement. You either: > > 2a) Make the outermost select ordered by your criteria, which messes up > the sequencin of different catalogue_products an gies you the result you > observe > > 2b) Make the inner catalogue_product-only select ordered by your > criteria - the outer order_by will reorder the results of the join again > > 2c) Make the inner catalogue_product-only select ordered by your desired > criteria and DO NOT order the outermost join result - this will result > in non-deterministic order of the final result, depending on how the > rows are atually stored in the database. > > So there - 3 scenarios I can think of, none of which really work. If you > can express it in *deterministic* SQL - I can make DBIC execute it, no > questions asked. > > Cheers
Thanks for replay, the SQL i excpect looks like this: SELECT me.id_product, me.id_node, me.name_product, me.description_product, ........ FROM (SELECT me.id_product, me.id_node, me.name_product, me.description_product, ...... FROM catalogue_products me GROUP BY me.id_product ORDER BY RAND() LIMIT 3) me LEFT JOIN catalogue_productPrices productPrice ON productPrice.idProduct = me.id_product LEFT JOIN catalogue_currencies currency ON currency.id = productPrice.idCurrency ORDER BY productPrice.idProduct; Its the same as last sql query, but in the outer select i remove RAND() ordering (see attached files). The innermost select is ok. is there any way that dbix keep order_by off from outer select? Thank you!
Subject: outer_select_without_rand.jpeg
outer_select_without_rand.jpeg
Subject: outer_select_with_rand.jpeg
outer_select_with_rand.jpeg
Download (untitled) / with headers
text/plain 953b
Show quoted text
> Thanks for replay, the SQL i excpect looks like this: > > SELECT me.id_product, me.id_node, me.name_product, > me.description_product, ........ FROM > (SELECT me.id_product, me.id_node, me.name_product, > me.description_product, ...... FROM catalogue_products me GROUP BY > me.id_product ORDER BY RAND() LIMIT 3) me > LEFT JOIN catalogue_productPrices productPrice ON productPrice.idProduct > = me.id_product LEFT JOIN catalogue_currencies currency ON currency.id = > productPrice.idCurrency ORDER BY productPrice.idProduct; > > Its the same as last sql query, but in the outer select i remove RAND() > ordering (see attached files). The innermost select is ok. > is there any way that dbix keep order_by off from outer select?
This SQL will never return random results, you can run it as many times as you like, the final set will be ordered by productPrice.idProduct Or was your objective only to get 3 random rows with their stuff prefetched?
From: soporte [...] web-experto.com.ar
Download (untitled) / with headers
text/plain 1.4k
El Jue feb 25 09:52:14 2010, RIBASUSHI escribió: Show quoted text
> > Thanks for replay, the SQL i excpect looks like this: > > > > SELECT me.id_product, me.id_node, me.name_product, > > me.description_product, ........ FROM > > (SELECT me.id_product, me.id_node, me.name_product, > > me.description_product, ...... FROM catalogue_products me GROUP BY > > me.id_product ORDER BY RAND() LIMIT 3) me > > LEFT JOIN catalogue_productPrices productPrice ON productPrice.idProduct > > = me.id_product LEFT JOIN catalogue_currencies currency ON currency.id = > > productPrice.idCurrency ORDER BY productPrice.idProduct; > > > > Its the same as last sql query, but in the outer select i remove RAND() > > ordering (see attached files). The innermost select is ok. > > is there any way that dbix keep order_by off from outer select?
> > This SQL will never return random results, you can run it as many times > as you like, the final set will be ordered by productPrice.idProduct > > Or was your objective only to get 3 random rows with their stuff
prefetched? Yes, that was my objective! and the sql does return random, but in my opinion the final order_by should not be there, it works fine with just the innermost order_by. The final order_by is automaticaly added by DBIx-Class, it's not seted by me. And it makes rows appear not grouped by id_product, which cause a wrong prefetched. I finaly got 3 diferent random products, but in 9 product objects (they are repeted). Thanks!
Download (untitled) / with headers
text/plain 1.6k
On Thu Feb 25 10:50:40 2010, webexperto wrote: Show quoted text
> El Jue feb 25 09:52:14 2010, RIBASUSHI escribió:
> > > Thanks for replay, the SQL i excpect looks like this: > > > > > > SELECT me.id_product, me.id_node, me.name_product, > > > me.description_product, ........ FROM > > > (SELECT me.id_product, me.id_node, me.name_product, > > > me.description_product, ...... FROM catalogue_products me GROUP BY > > > me.id_product ORDER BY RAND() LIMIT 3) me > > > LEFT JOIN catalogue_productPrices productPrice ON
productPrice.idProduct Show quoted text
> > > = me.id_product LEFT JOIN catalogue_currencies currency ON
currency.id = Show quoted text
> > > productPrice.idCurrency ORDER BY productPrice.idProduct; > > > > > > Its the same as last sql query, but in the outer select i remove
RAND() Show quoted text
> > > ordering (see attached files). The innermost select is ok. > > > is there any way that dbix keep order_by off from outer select?
> > > > This SQL will never return random results, you can run it as many times > > as you like, the final set will be ordered by productPrice.idProduct > > > > Or was your objective only to get 3 random rows with their stuff
> prefetched? > > Yes, that was my objective! and the sql does return random, but in my > opinion the final order_by should not be there, it works fine with just > the innermost order_by. The final order_by is automaticaly added by > DBIx-Class, it's not seted by me. And it makes rows appear not grouped > by id_product, which cause a wrong prefetched. > I finaly got 3 diferent random products, but in 9 product objects (they > are repeted). > Thanks!
Right... this problem is much deeper than it initially appears, work is being done to resolve it, possibly by end of week.
Download (untitled) / with headers
text/plain 236b
Just a heads up that this bug has not been forgotten. The underlying machinery rewrite however proved quite the bitch, thus no sane ETA is currently available on when this will be finally fixed (but it definitely will be at some point).
Stalling until we solve the underlying problem in the DBIC codebase. Sorry for the delay :(
This is now fixed in the repo master: git://git.shadowcat.co.uk/dbsrgits/DBIx-Class.git
Re-stalling until parent RT#74584 is truly resolved
Hopefully this time DBIC 0.08240 nails this issue.
And production version resolving this is now on CPAN.


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.