[Dbix-class] Paging and distinct

[dreel at bk.ru]

Здравствуйте, Matt.

Вы писали 17 октября 2007 г., 0:22:00:

MST> On Tue, Oct 16, 2007 at 05:35:26PM +0530, Ramprabhu Kaliaperumal wrote:
>> Hi All
>> 
>> I am getting syntax error when passing 'page/rows' and 'distinct' 
>> parameter in search attributes.
>> If I remove any one then it works fine. It seems paging and distinct not
>> working together.
>> Can anyone try this same and let me its know whether its working.

MST> Please only mail the list once.

MST> And no, that isn't going to work (should do in 09, but there's no ETA for
MST> that yet).

MST> Go back and figure out what's wrong with your DB design that made you need
MST> distinct in the first place.

The first way to solve this problem it's create a database view
including your complex query or something like your query. The view
encapsulates the query and DBIx now can't modyfy it.

The second way is using
$schema->storage->dbh->selectall_arrayref("YOUR QUERY");
as simple DBI.

THE THIRD decision/ you can override SQL::Abstract::Limit like
this:

BEGIN {

package DBIC::SQL::Abstract; # Would merge upstream, but nate doesn't reply :(

use base qw/SQL::Abstract::Limit/;

sub _Top {
my ( $self, $sql, $order, $rows, $offset ) = @_;

my $last = $rows + $offset;

my ( $order_by_up, $order_by_down ) = $self->_order_directions( $order );

## omitted for right sql  ##$sql =~ s/^\s*(SELECT|select)//;

$sql = <<"";
SELECT * FROM
(
SELECT TOP $rows * FROM
(
SELECT TOP $last * FROM
(
$sql
) AS doo
$order_by_up
) AS foo
$order_by_down
) AS bar
$order_by_up

return $sql;
}

}

http://www.karelia-on-line.ru/info.php?id=39

-- 
Regards,
 dreel                          mailto:dreel at bk.ru