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
Attachments
outer_select_with_rand.jpeg
outer_select_without_rand.jpeg

History Show all quoted text
  Wed Feb 24 15:24:56 2010 soporte [...] web-experto.com.ar - Ticket created
Subject: Search with has_many relation fails at using ORDER BY RAND()
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.
  Thu Feb 25 03:44:49 2010 ribasushi [...] leporine.io - Correspondence added
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
  Thu Feb 25 03:44:52 2010 The RT System itself - Status changed from 'new' to 'open'
  Thu Feb 25 03:46:02 2010 macevedo [...] web-experto.com.ar - Correspondence added
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
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>
<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
  Thu Feb 25 03:47:12 2010 macevedo [...] web-experto.com.ar - Correspondence added
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
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>
<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
<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
  Thu Feb 25 03:48:23 2010 macevedo [...] web-experto.com.ar - Correspondence added
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
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>
<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
<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
<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
  Thu Feb 25 03:49:34 2010 macevedo [...] web-experto.com.ar - Correspondence added
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
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>
<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
<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
<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
<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
  Thu Feb 25 03:50:34 2010 macevedo [...] web-experto.com.ar - Correspondence added
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
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>
<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
<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
<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
<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
<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
  Thu Feb 25 03:51:50 2010 macevedo [...] web-experto.com.ar - Correspondence added
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
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>
<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
<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
<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
<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
<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
<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
  Thu Feb 25 03:52:51 2010 macevedo [...] web-experto.com.ar - Correspondence added
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
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>
<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
<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
<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
<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
<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
<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
<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
  Thu Feb 25 03:53:52 2010 macevedo [...] web-experto.com.ar - Correspondence added
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
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>
<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
<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
<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
<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
<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
<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
<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
<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
  Thu Feb 25 03:54:56 2010 macevedo [...] web-experto.com.ar - Correspondence added
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
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>
<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
<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
<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
<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
<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
<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
<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
<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
<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
  Thu Feb 25 03:55:57 2010 macevedo [...] web-experto.com.ar - Correspondence added
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
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>
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
  Thu Feb 25 03:57:02 2010 macevedo [...] web-experto.com.ar - Correspondence added
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
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>
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
  Thu Feb 25 03:58:03 2010 macevedo [...] web-experto.com.ar - Correspondence added
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
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>
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
  Thu Feb 25 03:59:07 2010 macevedo [...] web-experto.com.ar - Correspondence added
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
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>
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
  Thu Feb 25 04:00:17 2010 macevedo [...] web-experto.com.ar - Correspondence added
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
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>
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
  Thu Feb 25 04:01:22 2010 macevedo [...] web-experto.com.ar - Correspondence added
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
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>
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
  Thu Feb 25 04:02:26 2010 macevedo [...] web-experto.com.ar - Correspondence added
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
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>
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
  Thu Feb 25 04:03:30 2010 macevedo [...] web-experto.com.ar - Correspondence added
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
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>
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
  Thu Feb 25 04:04:31 2010 macevedo [...] web-experto.com.ar - Correspondence added
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
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>
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
  Thu Feb 25 04:05:34 2010 macevedo [...] web-experto.com.ar - Correspondence added
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
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>
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
<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
  Thu Feb 25 08:37:25 2010 soporte [...] web-experto.com.ar - Correspondence added
From: soporte [...] web-experto.com.ar
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
Download outer_select_without_rand.jpeg
image/jpeg 150.5k
outer_select_without_rand.jpeg
Subject: outer_select_with_rand.jpeg
Download outer_select_with_rand.jpeg
image/jpeg 177.2k
outer_select_with_rand.jpeg
  Thu Feb 25 09:52:14 2010 ribasushi [...] leporine.io - Correspondence added
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?
  Thu Feb 25 10:50:40 2010 soporte [...] web-experto.com.ar - Correspondence added
From: soporte [...] web-experto.com.ar
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!
  Thu Feb 25 10:54:53 2010 ribasushi [...] leporine.io - Correspondence added
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.
  Sun Sep 12 08:23:23 2010 ribasushi [...] leporine.io - Correspondence added
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).
  Wed Feb 01 03:34:05 2012 ribasushi [...] leporine.io - Correspondence added
Stalling until we solve the underlying problem in the DBIC codebase. Sorry for the delay :(
  Wed Feb 01 03:34:14 2012 ribasushi [...] leporine.io - Status changed from 'open' to 'stalled'
  Wed Feb 01 03:34:14 2012 ribasushi [...] leporine.io - Dependency on ticket #74584 added
  Tue Apr 17 09:46:24 2012 ribasushi [...] leporine.io - Correspondence added
This is now fixed in the repo master: git://git.shadowcat.co.uk/dbsrgits/DBIx-Class.git
  Tue Apr 17 09:46:25 2012 The RT System itself - Status changed from 'stalled' to 'open'
  Tue Apr 17 09:46:25 2012 ribasushi [...] leporine.io - Status changed from 'open' to 'patched'
  Thu Aug 30 13:21:10 2012 ribasushi [...] leporine.io - Correspondence added
Re-stalling until parent RT#74584 is truly resolved
  Thu Aug 30 13:21:11 2012 The RT System itself - Status changed from 'patched' to 'open'
  Thu Aug 30 13:21:11 2012 ribasushi [...] leporine.io - Status changed from 'open' to 'stalled'
  Thu Feb 14 06:09:59 2013 ribasushi [...] leporine.io - Fixed in 0.08240-TRIAL added
  Thu Feb 14 06:09:59 2013 ribasushi [...] leporine.io - Correspondence added
Hopefully this time DBIC 0.08240 nails this issue.
  Thu Feb 14 06:10:00 2013 The RT System itself - Status changed from 'stalled' to 'open'
  Thu Feb 14 06:10:00 2013 ribasushi [...] leporine.io - Status changed from 'open' to 'patched'
  Tue Apr 30 02:49:31 2013 ribasushi [...] leporine.io - Fixed in 0.08250 added
  Tue Apr 30 02:49:32 2013 ribasushi [...] leporine.io - Fixed in 0.08240-TRIAL deleted
  Tue Apr 30 02:49:32 2013 ribasushi [...] leporine.io - Correspondence added
And production version resolving this is now on CPAN.
  Tue Apr 30 02:49:33 2013 ribasushi [...] leporine.io - Status changed from 'patched' to 'resolved'