I have the following problem ( fully documented with test case below ).
I have 3 tables (projects, employees, and project_employee_maps).
When I do a search for projects that have me on them, via the search
my $result = My::Project::Manager->get_projects
(
query => [
'employees.name' => 'jud',
],
with_objects => [ 'employees' ],
);
I get the expected results (1 item in the test case). However, when I look at
the list of employees for projects in my result set, they only contain
the employee that was in my search( me ), instead of the full list of employees
that are associated with the project.
Running DBI_TRACE=2 on my test script, I see that this is the only DB call that
gets made
for that operatation:
...
<- prepare('SELECT
t1.id,
t1.name,
t3.id,
t3.name
FROM
projects t1
JOIN project_employee_maps t2 ON (t2.project_id = t1.id)
JOIN employees t3 ON (t2.employee_id = t3.id)
WHERE
t3.name = ?
ORDER BY t1.id')= DBI::st=HASH(0x96bc108) at Manager.pm line 1661
...
The result set makes sense given this query, but is incorrect.
I could force the employee list to be reloaded by doing something like
$_->employees( undef ) for @$result;
but I'd hate to have to remember to do that for every query set,
and I suspect that I'd need to do it for every relationship that I
mention in my query terms.
Is there a way to specify that the Rose::DB::Object::Manager should
reload the result set afterwards, or a straight-forward way to get
it done automatically when necessary?
FWIW, I don't see an obvious way to fix the SQL to get the required
information in one query.
Thanks,
- jud
#################### TEST CASE ############################
#!/usr/bin/env perl
use strict;
use warnings;
use Test::More tests => 4;
##################################################
package My::DB;
use base 'Rose::DB';
__PACKAGE__->use_private_registry;
__PACKAGE__->default_domain( 'test' );
__PACKAGE__->default_type( 'tag_test' );
__PACKAGE__->register_db(
type => 'tag_test',
domain => 'test',
database => 'tag_test',
driver => 'mysql',
username => 'rosetest',
password => 'rose-secret',
host => '127.0.0.1',
port => 3306,
);
##################################################
package My::Object;
use base 'Rose::DB::Object';
sub init_db { My::DB->new( ) }
##################################################
package My::Project;
use base 'My::Object';
__PACKAGE__->meta->setup(
table => 'projects',
columns => [
id => { type => 'int', not_null => 1, },
name => { type => 'varchar', length => 255, not_null => 1, },
],
primary_key_columns => 'id',
unique_key => 'name',
relationships => [
employees => { type => 'many to many' },
],
);
__PACKAGE__->meta->make_manager_class( );
##################################################
package My::Employee;
use base 'My::Object';
__PACKAGE__->meta->setup(
table => 'employees',
columns => [
id => { type => 'int', not_null => 1, },
name => { type => 'varchar', length => 255, not_null => 1, },
],
primary_key_columns => 'id',
unique_key => 'name',
);
__PACKAGE__->meta->make_manager_class( );
##################################################
package My::ProjectEmployeeMap;
use base 'My::Object';
__PACKAGE__->meta->setup(
columns => [
project_id => { type => 'int', not_null => 1, },
employee_id => { type => 'int', not_null => 1, },
],
primary_key_columns => [ qw(project_id employee_id ) ],
foreign_keys => [
project => {class => 'My::Project' },
employee => {class => 'My::Employee' },
],
);
__PACKAGE__->meta->make_manager_class( );
##################################################
package main;
my $db = My::DB->new;
my ($p, $result, @results, @employees );
$result = My::Project::Manager->get_projects
(
query => [
'employees.name' => 'jud'
],
require_objects => [ 'employees' ],
);
is( @$result, 1, "got 1 result" );
my $e1 = $result->[0];
@employees = $e1->employees;
is( scalar( @employees ), 3, 'have 3 employees after search' );
ok( ( grep { $employees[0]->name eq 'jud' } @employees ),
'got the employee from our search criteria'
);
$e1->employees( undef );
@employees = $e1->employees;
is( scalar( @employees ), 3, "have 3 employees after reload" );
__DATA__
/* WARNING: THIS RECREATES THE DATABASE */
DROP DATABASE IF EXISTS tag_test;
CREATE DATABASE tag_test;
use tag_test;
CREATE TABLE IF NOT EXISTS projects (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
UNIQUE KEY (name),
PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE employees (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
PRIMARY KEY(id),
UNIQUE KEY (name)
) ENGINE=INNODB;
CREATE TABLE IF NOT EXISTS project_employee_maps (
project_id INT NOT NULL,
employee_id INT NOT NULL,
PRIMARY KEY (project_id, employee_id),
FOREIGN KEY (project_id) REFERENCES projects (id),
FOREIGN KEY (employee_id) REFERENCES employees (id)
) ENGINE=INNODB;
INSERT INTO projects VALUES ( 1, 'p1' );
INSERT INTO projects VALUES ( 2, 'p2' );
INSERT INTO employees VALUES ( 1, 'jud' );
INSERT INTO employees VALUES ( 2, 'mike' );
INSERT INTO employees VALUES ( 3, 'cory' );
INSERT INTO project_employee_maps VALUES ( 1, 1);
INSERT INTO project_employee_maps VALUES ( 1, 2);
INSERT INTO project_employee_maps VALUES ( 1, 3);
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys-and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Rose-db-object mailing list
Rose-db-object@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/rose-db-object
