[Dbix-class] -current branch: please test

[Nilson Santos Figueiredo Junior]

On 7/11/06, David Kamholz <davekam at pobox.com> wrote:
> Well, after a fair amount of thought on Matt's part and some work,
> we've implemented some improved resultset code, resolving some subtle
> bugs that came up in the last few days. The new implementation
> (codenamed ResultSet 2: Electric Boogaloo) has been committed to the -
> current branch. I would once again like to ask anyone who has an app
> with twisty and complex joins and prefetches to test it out. If all
> looks well, we will release it as a final RC in a couple days to here
> and the Catalyst list. At present there are no known bugs -- we're
> hoping this will become the 0.07 final.

I hate to be the one to say it again but my Catalyst application fails
right away with -current as it is.

I'm getting this error:
Coldn't render template "undef error -
DBIx::Class::ResultSet::count(): Error executing 'SELECT COUNT(
DISTINCT( task.id ) ) FROM task_user me  JOIN task task ON ( task.id =
me.task_id ) LEFT JOIN task_log logs ON ( logs.task_id = task.id )
LEFT JOIN task_log logs ON ( logs.task_id = task.id ) WHERE ( ( (
is_closed = ? ) AND ( me.user_id = ? ) ) )': Not unique table/alias:
'logs' at E:\cgi\MyTasks\root\user\tasklist.tt line 4"

As you can see from the query, the join is duplicated. This didn't
happen before (neither in trunk nor in -current).

The error happens when I issue a "tasks.count" on a resultset that was
produced as follows:

my $user = $c->model('Persistent::User')->find($user_id);
my $tasks = $user->tasks->search_hours_spent;

If I don't populate "hours_spent" through the search_hours_spent()
method, the error doesn't occur, so probably it's something in it
(again):

  sub search_hours_spent {
      my $self = shift;
      my $alias = $self->{attrs}{alias};

      $self->search(
          undef,
          {
              join     => [qw/logs/],
              select   => [(map {"${alias}.${_}"}
$self->result_source->columns), {sum => 'logs.hours_spent'} ],
              as       => [$self->result_source->columns, qw/hours_spent/],
              group_by => ["${alias}.id"]
          }
      );
  }

The "tasks" relationship is a many-to-many one, as follows:

  $pkg->has_many('task_users' => 'MyTasks::Schema::TaskUser', 'user_id');
  $pkg->many_to_many('tasks' => 'task_users', 'task');

The weirdest part of all of this is that there's another User
relationship, "created_tasks" for which the search_hours_spent()
method works flawlessly. It is a plain one-to-many relationship so
maybe the issue is somewhat related to many-to-many relationships.
This relationship (the one that works) is defined as:

  $pkg->has_many('created_tasks' => 'MyTasks::Schema::Task',  'creator_id');

The specific relationship being joined twice is defined as:

  $pkg->has_many('logs'       => 'MyTasks::Schema::TaskLog',  'user_id');

The related Schema classes:

    package MyTasks::Schema::User;
    use warnings;
    use strict;
    use base qw/DBIx::Class/;

    my $pkg = __PACKAGE__;
    $pkg->load_components(qw/PK::Auto Core/);
    $pkg->table('user');
    $pkg->add_columns(qw/id login passwd name type/);
    $pkg->set_primary_key('id');
    $pkg->add_unique_constraint( login => ['login'] );
    $pkg->add_unique_constraint( name  => ['name' ] );

    # foreign keys
    $pkg->has_many('logs'       => 'MyTasks::Schema::TaskLog',  'user_id');

    $pkg->has_many('created_tasks' => 'MyTasks::Schema::Task',  'creator_id');

    $pkg->has_many('task_users' => 'MyTasks::Schema::TaskUser', 'user_id');
    $pkg->many_to_many('tasks' => 'task_users', 'task');

    1;

    package MyTasks::Schema::Task;
    use warnings;
    use strict;
    use base qw/DBIx::Class/;

    my $pkg = __PACKAGE__;

    $pkg->load_components(qw/PK::Auto Core/);
    $pkg->table('task');
    $pkg->add_columns(qw/id project_id creator_id date deadline
priority description is_closed comments parent_id
support_request_id/);
    $pkg->set_primary_key('id');
    $pkg->resultset_class('MyTasks::Schema::ResultSet::Task');

    # foreign keys
    $pkg->belongs_to('project', 'MyTasks::Schema::Project', 'project_id');

    $pkg->belongs_to('creator', 'MyTasks::Schema::User', 'creator_id');

    $pkg->has_many('logs', 'MyTasks::Schema::TaskLog', 'task_id');

    $pkg->has_many('task_users' => 'MyTasks::Schema::TaskUser', 'task_id');
    $pkg->many_to_many('users' => 'task_users', 'user');

    $pkg->has_many('child_tasks' => 'MyTasks::Schema::Task', 'parent_id');
    $pkg->belongs_to('parent_task' => 'MyTasks::Schema::Task', 'parent_id');
    $pkg->belongs_to('support_request' =>
'MyTasks::Schema::SupportRequest', 'support_request_id');

    1;

    package MyTasks::Schema::TaskLog;
    use warnings;
    use strict;
    use base qw/DBIx::Class/;

    __PACKAGE__->load_components(qw/PK::Auto Core/);
    __PACKAGE__->table('task_log');
    __PACKAGE__->add_columns(qw/id task_id user_id date hours_spent comments/);
    __PACKAGE__->set_primary_key('id');

    # foreign keys
    __PACKAGE__->belongs_to('task', 'MyTasks::Schema::Task', 'task_id');
    __PACKAGE__->belongs_to('user', 'MyTasks::Schema::User', 'user_id');

    1;

    package MyTasks::Schema::TaskUser;
    use warnings;
    use strict;
    use base qw/DBIx::Class/;

    __PACKAGE__->load_components(qw/PK::Auto Core/);
    __PACKAGE__->table('task_user');
    __PACKAGE__->add_columns(qw/task_id user_id/);
    __PACKAGE__->set_primary_key(qw/task_id user_id/);

    # foreign keys
    __PACKAGE__->belongs_to('task', 'MyTasks::Schema::Task', 'task_id');
    __PACKAGE__->belongs_to('user', 'MyTasks::Schema::User', 'user_id');

    1;

I tried including as much information as possible. But, if necessary,
I can provide whatever else information is needed.

-Nilson Santos F. Jr.