NicksByMessages.pm

package App::Ircql::Query::NicksByMessages;

use strict;
use warnings;

use Sub::Exporter -setup => {
	exports => [ qw(nicks_by_messages) ],
	groups => {
		default => [ qw(nicks_by_messages) ],
	},
};

sub nicks_by_messages {
	my($dbh) = @_;
	return $dbh->selectall_arrayref('
		SELECT nick, COUNT(body)
		FROM message
		GROUP BY nick
		ORDER BY COUNT(body) DESC'
	);
}

1

NicksByMessages.pm

package Test::Query::NicksByMessages;

use strict;
use warnings;
use App::Ircql::Query::NicksByMessages;

use DBI;
use base 'Test::Class';
use Test::Most;

sub fixture : Test(setup) {
	my($self) = @_;
	$self->{dbh} = DBI->connect('dbi:SQLite:dbname=:memory:', '', '', {
		RaiseError => 1
	});
	$self->{dbh}->do('CREATE TABLE message (id INTEGER PRIMARY KEY,
	                  time, nick, body);');
	my $sth = $self->{dbh}->prepare('INSERT INTO message (nick, body)
	                                 VALUES (?, ?)');

	my %count = (
		nick_1 => 30,
		nick_2 => 20,
		nick_3 => 25,
		nick_4 => 50,
	);

	while (my($n, $c) = each %count) {
		foreach (1..$c) {
			$sth->execute($n, "Message $_");
		}
	}
}

sub count : Test {
	my($self) = @_;
	is_deeply(nicks_by_messages($self->{dbh}), [
		[nick_4 => 50],
		[nick_1 => 30],
		[nick_3 => 25],
		[nick_2 => 20],
	]);
}

__PACKAGE__->new()->runtests();