[Dbix-class] Join and Precache not producing what I expect.

Curtis Fletcher curtis.fletcher at pharmaventures.com
Mon Sep 24 13:13:48 GMT 2007


Hi all.
I've been using Catalyst/DBIx::Class for a few months now. Being used to
rolling my own SQL the hardest part was getting to grips with an ORM,
but I think I'm just about there now, but I'm getting some odd results
when precaching results.

Apologies for the length but I've reduced this down as much as I can.

The problem is that I'm getting more objects that I would expect from
the DBIx:Class during precaching, the SQL that is generated looks fine
and gives me the rows I'd expect. But the conversion of those result
rows seems to be wrong given the classes I've defined. I'm guessing this
is a bug. I've come across a few of them with some of my joins that
disappeared as I upgraded DBIx:Class (Usually just as I'd got a test
case and were prepping an email to the list :) )

Schema and test case (Using DBIx-Class-0.08007):

package Schema::Products;
use base 'DBIx::Class';

__PACKAGE__->load_components("PK::Auto", "Core");
__PACKAGE__->table("4w_products");
__PACKAGE__->add_columns("id",{},"state",{},
"sku",{},"title",{},"story_id",{},"image_id",{},"handler_uri",{},"weight
",{});
__PACKAGE__->set_primary_key("id");
__PACKAGE__->has_many("prices_accessor","Prices",{ "foreign.product_id"
=> "self.id" });
__PACKAGE__->has_many("product_notes_accessor","ProductNotes",{
"foreign.product_id" => "self.id" });
1;

package Schema::Prices;
use base 'DBIx::Class';

__PACKAGE__->load_components("PK::Auto", "Core");
__PACKAGE__->table("4w_prices");
__PACKAGE__->add_columns("id",{},"product_id",{},"currency",{},"price",{
},"tax",{},"tax_name",{});
__PACKAGE__->set_primary_key("id");
__PACKAGE__->belongs_to("product_accessor", "Blocks", { id =>
"product_id"});

1;

package Schema::ProductNotes;
use base 'DBIx::Class';

__PACKAGE__->load_components("PK::Auto", "Core");
__PACKAGE__->table("4w_product_notes");
__PACKAGE__->add_columns(  "product_id",{},  "note_id",{},
"currency",{},  "note_visibility",{},);
__PACKAGE__->set_primary_key("product_id", "note_id","currency");
__PACKAGE__->belongs_to("product_accessor","Products",{ id =>
"product_id"},{join_type => 'left'},);
1;


package Schema;
use base qw/DBIx::Class::Schema/;
__PACKAGE__->load_classes(qw/Prices Products ProductNotes/);
1;

package test;
use DBIx::Class;

my $schema = Schema->connect('dbi:mysql:dbname=test', 'xxxx', 'xxxx' );

my @search = 
(
	{
		'-and'=>
		[
			'me.id' =>970,
			'me.state'=>1,
			'-or'=>
			[
				'product_notes_accessor.product_id'
=>undef,
				'note_visibility' => 2
			]
		]
	},
	{
		'join' =>
		[
			'product_notes_accessor',
		],
		'prefetch' =>
		[
			'prices_accessor',
			'product_notes_accessor',
		],
		'cache' => 1,
		
	},
);
my $r1 = $schema->resultset('Products')->search_rs(@search);

while (my $i1 = $r1->next)
{
	print "".ref($i1).':'.$i1->id."\n";

	my $r7 = $i1->prices_accessor;
	while (my $i7 = $r7->next)
	{
		print "\t".ref($i7).':'.$i7->id."\n";
	}
	my $r8 = $i1->product_notes_accessor;
	while (my $i8 = $r8->next)
	{
		print "\t".ref($i8).':'.$i8->note_id."\n";
	}
}
1;

This Produces:

Schema::Products:970
        Schema::Prices:3502
        Schema::Prices:3505
        Schema::Prices:3502
        Schema::Prices:3503
        Schema::Prices:3505
        Schema::ProductNotes:1
        Schema::ProductNotes:2
        Schema::ProductNotes:1
        Schema::ProductNotes:2
        Schema::ProductNotes:1

There should be only 3 Prices and 2 ProductNotes. The DBIC_TRACE SQL
looks fine:

SELECT me.id, me.state, me.sku, me.title, me.story_id, me.image_id,
me.handler_uri, me.weight, prices_accessor.id,
prices_accessor.product_id, prices_accessor.currency,
prices_accessor.price, prices_accessor.tax, prices_accessor.tax_name,
product_notes_accessor.product_id, product_notes_accessor.note_id,
product_notes_accessor.currency, product_notes_accessor.note_visibility
FROM 4w_products me LEFT JOIN 4w_product_notes product_notes_accessor ON
( product_notes_accessor.product_id = me.id ) LEFT JOIN 4w_prices
prices_accessor ON ( prices_accessor.product_id = me.id ) WHERE ( ( (
me.id = ? ) AND ( me.state = ? ) AND ( ( (
product_notes_accessor.product_id IS NULL ) OR ( note_visibility = ? ) )
) ) ) ORDER BY prices_accessor.product_id,
product_notes_accessor.product_id: '970', '1', '2'

The data for these test tables is:

INSERT INTO `4w_prices` VALUES 
	(3502,970,'GBP','4500.00',0.175,'VAT'),
	(3503,970,'USD','8550.00',0,''),
	(3505,970,'EUR','6525.00',0,'');
INSERT INTO `4w_product_notes` VALUES 
	(970,1,'GBP',2),
	(970,2,'GBP',2);
INSERT INTO `4w_products` VALUES (970,1,'PR-RRPL-2007','Product
title',1117,0,65,0);
INSERT INTO `4w_stories` VALUES (1117,1,'Product title','2007-08-28
19:38:50');
INSERT INTO `4w_story_joiner` VALUES (1117,1257,0);
INSERT INTO `4w_storyhtml` VALUES (1257,1,'Product
title','blurb',0,0,0,'2007-01-01 00:00:00');

As I said I'm still fairly new to ORM's and DBIx:Class specifically but
I know my way around SQL so if there anything I'm missing I'd really
appreciate a nudge in the right direction. Otherwise I guess this is a
bug report.

--  

Curtis Fletcher | Software Developer | PharmaVentures Ltd. |
curtis.fletcher at pharmaventures.com | www.pharmaventures.com 




More information about the DBIx-Class mailing list