SQL::Abstract regression?

[Paul Makepeace]

I have,

__PACKAGE__->has_many(publication_story => 'IDL::Schema::Story',
'publication_uid');
__PACKAGE__->belongs_to(publication_type => 'IDL::Schema::CodeTree',
'publication_type_uid', { join_type => 'left'});

my @SEARCH_WITH_STORY_COUNT_ARGS = (
  join => [qw/publication_story/],
  select => [{ count => 'publication_story.uid' }, qw/me.uid me.name
me.short_name
    me.url me.description me.publication_type_uid me.region_uid
me.channel_type_uid
    me.circulation me.ave1 me.ave2 me.comments/,],
  as => [qw/story_count uid name short_name url description publication_type_uid
    region_uid channel_type_uid circulation ave1 ave2 comments/],
  group_by => [qw/me.uid/],
  prefetch => [qw/publication_type region/],
);

and this is generating this wrong-looking SQL,

DBIx::Class::ResultSet::search(): DBI Exception: DBD::mysql::st
execute failed: Unknown column 'publication_story.uid' in 'field list'
[for Statement "SELECT me.story_count, me.uid, me.name, me.short_name,
me.url, me.description, me.publication_type_uid, me.region_uid,
me.channel_type_uid, me.circulation, me.ave1, me.ave2, me.comments,
publication_type.uid, publication_type.parent_uid,
publication_type.value, publication_type.hide, publication_type.alias,
publication_type.order_priority, publication_type.show_in_lists,
publication_type.data_type, publication_type.cascade_data_type,
publication_type.description, region.uid, region.parent_uid,
region.value, region.hide, region.alias, region.order_priority,
region.show_in_lists, region.data_type, region.cascade_data_type,
region.description FROM (SELECT COUNT( publication_story.uid ) AS
story_count, me.uid, me.name, me.short_name, me.url, me.description,
me.publication_type_uid, me.region_uid, me.channel_type_uid,
me.circulation, me.ave1, me.ave2, me.comments FROM publication me
WHERE ( ( name LIKE ? OR short_name LIKE ? ) ) GROUP BY me.uid ORDER
BY name) me LEFT JOIN story publication_story ON
publication_story.publication_uid = me.uid LEFT JOIN code_tree
publication_type ON publication_type.uid = me.publication_type_uid
LEFT JOIN code_tree region ON region.uid = me.region_uid WHERE ( (
name LIKE ? OR short_name LIKE ? ) ) ORDER BY name"

specifically, FROM (SELECT COUNT( publication_story.uid ) AS
story_count, ... looks wrong. Or should I update my code somehow?

This error coincides with upgrading to latest DBIx::Class today.

Paul

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[Rob Kinyon]

On Mon, Jul 13, 2009 at 11:52, Paul Makepeace<[hidden email]> wrote:
[snip]
> specifically, FROM (SELECT COUNT( publication_story.uid ) AS
> story_count, ... looks wrong. Or should I update my code somehow?
>
> This error coincides with upgrading to latest DBIx::Class today.

What do you expect to see?

Rob

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[Paul Makepeace]

On Mon, Jul 13, 2009 at 11:03 AM, Rob Kinyon<[hidden email]> wrote:
> On Mon, Jul 13, 2009 at 11:52, Paul Makepeace<[hidden email]> wrote:
> [snip]
>> specifically, FROM (SELECT COUNT( publication_story.uid ) AS
>> story_count, ... looks wrong. Or should I update my code somehow?
>>
>> This error coincides with upgrading to latest DBIx::Class today.
>
> What do you expect to see?

My question is what do _you_ expect to see?

   select => [{ count => 'publication_story.uid' }, ...
   as => [qw/story_count ...

I would fairly reasonably expect to produce SELECT
COUNT(publication_story.uid) AS story_count and not have that appear
in the FROM clause (?!)

This has been working until today when I upgraded, so a recent version
of $module has broken this.

Paul


>
> Rob
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix-class@...
>

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[Rob Kinyon]

On Mon, Jul 13, 2009 at 12:11, Paul Makepeace<[hidden email]> wrote:

> On Mon, Jul 13, 2009 at 11:03 AM, Rob Kinyon<[hidden email]> wrote:
>> On Mon, Jul 13, 2009 at 11:52, Paul Makepeace<[hidden email]> wrote:
>> [snip]
>>> specifically, FROM (SELECT COUNT( publication_story.uid ) AS
>>> story_count, ... looks wrong. Or should I update my code somehow?
>>>
>>> This error coincides with upgrading to latest DBIx::Class today.
>>
>> What do you expect to see?
>
> My question is what do _you_ expect to see?
>
>   select => [{ count => 'publication_story.uid' }, ...
>   as => [qw/story_count ...
>
> I would fairly reasonably expect to produce SELECT
> COUNT(publication_story.uid) AS story_count and not have that appear
> in the FROM clause (?!)
>
> This has been working until today when I upgraded, so a recent version
> of $module has broken this.

Note: I have no idea what you used to get, so that's why I asked what
you expected. I don't have your query in my app, so I have no idea
what you are using this for. And, frankly, I don't have time to parse
a 100-term query to figure out the one thing that broke. If you tell
me what you think you should get, you get better answers.

Problem: as => doesn't actually affect the SQL. It only provides
$row->get_column('story_count'), not "COUNT(publication_story.uid) AS
story_count"

Suggestion: Try it without the prefetch. One of the major changes in
the last fix was changing how prefetch works to make it work better in
certain cases. This looks like a possible regression.

Rob

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[Stuart Dodds]

I had the exact same problem today.....the only way I could fix it was
to remove the prefetch from the search.


-----Original Message-----
From: Paul Makepeace <[hidden email]>
Reply-to: "DBIx::Class user and developer list"
<[hidden email]>
To: DBIx::Class user and developer list <[hidden email]>
Subject: [Dbix-class] SQL::Abstract regression?
Date: Mon, 13 Jul 2009 10:52:24 -0500

I have,

__PACKAGE__->has_many(publication_story => 'IDL::Schema::Story',
'publication_uid');
__PACKAGE__->belongs_to(publication_type => 'IDL::Schema::CodeTree',
'publication_type_uid', { join_type => 'left'});

my @SEARCH_WITH_STORY_COUNT_ARGS = (
  join => [qw/publication_story/],
  select => [{ count => 'publication_story.uid' }, qw/me.uid me.name
me.short_name
    me.url me.description me.publication_type_uid me.region_uid
me.channel_type_uid
    me.circulation me.ave1 me.ave2 me.comments/,],
  as => [qw/story_count uid name short_name url description publication_type_uid
    region_uid channel_type_uid circulation ave1 ave2 comments/],
  group_by => [qw/me.uid/],
  prefetch => [qw/publication_type region/],
);

and this is generating this wrong-looking SQL,

DBIx::Class::ResultSet::search(): DBI Exception: DBD::mysql::st
execute failed: Unknown column 'publication_story.uid' in 'field list'
[for Statement "SELECT me.story_count, me.uid, me.name, me.short_name,
me.url, me.description, me.publication_type_uid, me.region_uid,
me.channel_type_uid, me.circulation, me.ave1, me.ave2, me.comments,
publication_type.uid, publication_type.parent_uid,
publication_type.value, publication_type.hide, publication_type.alias,
publication_type.order_priority, publication_type.show_in_lists,
publication_type.data_type, publication_type.cascade_data_type,
publication_type.description, region.uid, region.parent_uid,
region.value, region.hide, region.alias, region.order_priority,
region.show_in_lists, region.data_type, region.cascade_data_type,
region.description FROM (SELECT COUNT( publication_story.uid ) AS
story_count, me.uid, me.name, me.short_name, me.url, me.description,
me.publication_type_uid, me.region_uid, me.channel_type_uid,
me.circulation, me.ave1, me.ave2, me.comments FROM publication me
WHERE ( ( name LIKE ? OR short_name LIKE ? ) ) GROUP BY me.uid ORDER
BY name) me LEFT JOIN story publication_story ON
publication_story.publication_uid = me.uid LEFT JOIN code_tree
publication_type ON publication_type.uid = me.publication_type_uid
LEFT JOIN code_tree region ON region.uid = me.region_uid WHERE ( (
name LIKE ? OR short_name LIKE ? ) ) ORDER BY name"

specifically, FROM (SELECT COUNT( publication_story.uid ) AS
story_count, ... looks wrong. Or should I update my code somehow?

This error coincides with upgrading to latest DBIx::Class today.

Paul

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...


_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[Peter Rabbitson-2]

On Mon, Jul 13, 2009 at 11:11:20AM -0500, Paul Makepeace wrote:

> On Mon, Jul 13, 2009 at 11:03 AM, Rob Kinyon<[hidden email]> wrote:
> > On Mon, Jul 13, 2009 at 11:52, Paul Makepeace<[hidden email]> wrote:
> > [snip]
> >> specifically, FROM (SELECT COUNT( publication_story.uid ) AS
> >> story_count, ... looks wrong. Or should I update my code somehow?
> >>
> >> This error coincides with upgrading to latest DBIx::Class today.
> >
> > What do you expect to see?
>
> My question is what do _you_ expect to see?

I expect to see exactly what you have pasted. Also being an ass will
not take you very far with support.

> I would fairly reasonably expect to produce SELECT
> COUNT(publication_story.uid) AS story_count and not have that appear
> in the FROM clause (?!)

Read the SQL more carefully then.

> This has been working until today when I upgraded, so a recent version
> of $module has broken this.

I need you to *manually* execute the following snippet against your
database:

SELECT COUNT( publication_story.uid ) AS
story_count, me.uid, me.name, me.short_name, me.url, me.description,
me.publication_type_uid, me.region_uid, me.channel_type_uid,
me.circulation, me.ave1, me.ave2, me.comments FROM publication me
WHERE ( ( name LIKE ? OR short_name LIKE ? ) ) GROUP BY me.uid ORDER
BY name

Let me know if the error persists or you get a somewhat reasonable
result back.

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[Peter Rabbitson-2]

On Mon, Jul 13, 2009 at 12:18:04PM -0400, Rob Kinyon wrote:

> On Mon, Jul 13, 2009 at 12:11, Paul Makepeace<[hidden email]> wrote:
> > On Mon, Jul 13, 2009 at 11:03 AM, Rob Kinyon<[hidden email]> wrote:
> >> On Mon, Jul 13, 2009 at 11:52, Paul Makepeace<[hidden email]> wrote:
> >> [snip]
> >>> specifically, FROM (SELECT COUNT( publication_story.uid ) AS
> >>> story_count, ... looks wrong. Or should I update my code somehow?
> >>>
> >>> This error coincides with upgrading to latest DBIx::Class today.
> >>
> >> What do you expect to see?
> >
> > My question is what do _you_ expect to see?
> >
> >   select => [{ count => 'publication_story.uid' }, ...
> >   as => [qw/story_count ...
> >
> > I would fairly reasonably expect to produce SELECT
> > COUNT(publication_story.uid) AS story_count and not have that appear
> > in the FROM clause (?!)
> >
> > This has been working until today when I upgraded, so a recent version
> > of $module has broken this.
>
> Note: I have no idea what you used to get, so that's why I asked what
> you expected. I don't have your query in my app, so I have no idea
> what you are using this for. And, frankly, I don't have time to parse
> a 100-term query to figure out the one thing that broke. If you tell
> me what you think you should get, you get better answers.
>
> Problem: as => doesn't actually affect the SQL. It only provides
> $row->get_column('story_count'), not "COUNT(publication_story.uid) AS
> story_count"
>

This is not entirely true - I use the supplied as in this case in order
to alias the internal count to the outside subquery (i.e. I need a name
to select the function result on the outside). I could have just
generated identifiers, but using the pre-supplied as seemed like a
reasonably good idea.

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[Paul Makepeace]

On Mon, Jul 13, 2009 at 12:01 PM, Peter Rabbitson<[hidden email]> wrote:

> On Mon, Jul 13, 2009 at 11:11:20AM -0500, Paul Makepeace wrote:
>> On Mon, Jul 13, 2009 at 11:03 AM, Rob Kinyon<[hidden email]> wrote:
>> > On Mon, Jul 13, 2009 at 11:52, Paul Makepeace<[hidden email]> wrote:
>> > [snip]
>> >> specifically, FROM (SELECT COUNT( publication_story.uid ) AS
>> >> story_count, ... looks wrong. Or should I update my code somehow?
>> >>
>> >> This error coincides with upgrading to latest DBIx::Class today.
>> >
>> > What do you expect to see?
>>
>> My question is what do _you_ expect to see?
>
> I expect to see exactly what you have pasted. Also being an ass will
> not take you very far with support.

Sorry I wasn't intending to come across as an ass - I had said in my
original email "specifically >here< is what looks wrong" so wasn't
sure what Rob was getting at. Knowing what he'd expect would help me
infer his intent.

>> I would fairly reasonably expect to produce SELECT
>> COUNT(publication_story.uid) AS story_count and not have that appear
>> in the FROM clause (?!)
>
> Read the SQL more carefully then.

I don't understand what you're getting at here. That clause is
appearing in a FROM clause whereas it should appear in the earlier
SELECT column list. Is that not how you're reading it?

>> This has been working until today when I upgraded, so a recent version
>> of $module has broken this.
>
> I need you to *manually* execute the following snippet against your
> database:
>
> SELECT COUNT( publication_story.uid ) AS
> story_count, me.uid, me.name, me.short_name, me.url, me.description,
> me.publication_type_uid, me.region_uid, me.channel_type_uid,
> me.circulation, me.ave1, me.ave2, me.comments FROM publication me
> WHERE ( ( name LIKE ? OR short_name LIKE ? ) ) GROUP BY me.uid ORDER
> BY name

(I filled in the ?s)

mysql> SELECT COUNT( publication_story.uid ) AS story_count, me.uid,
me.name, me.short_name, me.url, me.description,
me.publication_type_uid, me.region_uid, me.channel_type_uid,
me.circulation, me.ave1, me.ave2, me.comments FROM publication me
WHERE ( ( name LIKE '%red orbit%' OR short_name LIKE '%red orbit%' ) )
GROUP BY me.uid ORDER BY name;
ERROR 1054 (42S22): Unknown column 'publication_story.uid' in 'field list'


> Let me know if the error persists or you get a somewhat reasonable
> result back.

It seems reasonable to error out there given that publication_story
doesn't appear in the FROM clause.

Paul

>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix-class@...
>

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[Paul Makepeace]

Thanks Stuart, this worked for me too.

On Mon, Jul 13, 2009 at 11:32 AM, Stuart Dodds<[hidden email]> wrote:

> I had the exact same problem today.....the only way I could fix it was
> to remove the prefetch from the search.
>
>
> -----Original Message-----
> From: Paul Makepeace <[hidden email]>
> Reply-to: "DBIx::Class user and developer list"
> <[hidden email]>
> To: DBIx::Class user and developer list <[hidden email]>
> Subject: [Dbix-class] SQL::Abstract regression?
> Date: Mon, 13 Jul 2009 10:52:24 -0500
>
> I have,
>
> __PACKAGE__->has_many(publication_story => 'IDL::Schema::Story',
> 'publication_uid');
> __PACKAGE__->belongs_to(publication_type => 'IDL::Schema::CodeTree',
> 'publication_type_uid', { join_type => 'left'});
>
> my @SEARCH_WITH_STORY_COUNT_ARGS = (
>  join => [qw/publication_story/],
>  select => [{ count => 'publication_story.uid' }, qw/me.uid me.name
> me.short_name
>    me.url me.description me.publication_type_uid me.region_uid
> me.channel_type_uid
>    me.circulation me.ave1 me.ave2 me.comments/,],
>  as => [qw/story_count uid name short_name url description publication_type_uid
>    region_uid channel_type_uid circulation ave1 ave2 comments/],
>  group_by => [qw/me.uid/],
>  prefetch => [qw/publication_type region/],
> );
>
> and this is generating this wrong-looking SQL,
>
> DBIx::Class::ResultSet::search(): DBI Exception: DBD::mysql::st
> execute failed: Unknown column 'publication_story.uid' in 'field list'
> [for Statement "SELECT me.story_count, me.uid, me.name, me.short_name,
> me.url, me.description, me.publication_type_uid, me.region_uid,
> me.channel_type_uid, me.circulation, me.ave1, me.ave2, me.comments,
> publication_type.uid, publication_type.parent_uid,
> publication_type.value, publication_type.hide, publication_type.alias,
> publication_type.order_priority, publication_type.show_in_lists,
> publication_type.data_type, publication_type.cascade_data_type,
> publication_type.description, region.uid, region.parent_uid,
> region.value, region.hide, region.alias, region.order_priority,
> region.show_in_lists, region.data_type, region.cascade_data_type,
> region.description FROM (SELECT COUNT( publication_story.uid ) AS
> story_count, me.uid, me.name, me.short_name, me.url, me.description,
> me.publication_type_uid, me.region_uid, me.channel_type_uid,
> me.circulation, me.ave1, me.ave2, me.comments FROM publication me
> WHERE ( ( name LIKE ? OR short_name LIKE ? ) ) GROUP BY me.uid ORDER
> BY name) me LEFT JOIN story publication_story ON
> publication_story.publication_uid = me.uid LEFT JOIN code_tree
> publication_type ON publication_type.uid = me.publication_type_uid
> LEFT JOIN code_tree region ON region.uid = me.region_uid WHERE ( (
> name LIKE ? OR short_name LIKE ? ) ) ORDER BY name"
>
> specifically, FROM (SELECT COUNT( publication_story.uid ) AS
> story_count, ... looks wrong. Or should I update my code somehow?
>
> This error coincides with upgrading to latest DBIx::Class today.
>
> Paul
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix-class@...
>
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix-class@...
>

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[Peter Rabbitson-2]

On Mon, Jul 13, 2009 at 12:24:57PM -0500, Paul Makepeace wrote:

> On Mon, Jul 13, 2009 at 12:01 PM, Peter Rabbitson<[hidden email]> wrote:
> > On Mon, Jul 13, 2009 at 11:11:20AM -0500, Paul Makepeace wrote:
> >> On Mon, Jul 13, 2009 at 11:03 AM, Rob Kinyon<[hidden email]> wrote:
> >> > On Mon, Jul 13, 2009 at 11:52, Paul Makepeace<[hidden email]> wrote:
> >> > [snip]
> >> >> specifically, FROM (SELECT COUNT( publication_story.uid ) AS
> >> >> story_count, ... looks wrong. Or should I update my code somehow?
> >> >>
> >> >> This error coincides with upgrading to latest DBIx::Class today.
> >> >
> >> > What do you expect to see?
> >>
> >> My question is what do _you_ expect to see?
> >
> > I expect to see exactly what you have pasted. Also being an ass will
> > not take you very far with support.
>
> Sorry I wasn't intending to come across as an ass - I had said in my
> original email "specifically >here< is what looks wrong" so wasn't
> sure what Rob was getting at. Knowing what he'd expect would help me
> infer his intent.
>
> >> I would fairly reasonably expect to produce SELECT
> >> COUNT(publication_story.uid) AS story_count and not have that appear
> >> in the FROM clause (?!)
> >
> > Read the SQL more carefully then.
>
> I don't understand what you're getting at here. That clause is
> appearing in a FROM clause whereas it should appear in the earlier
> SELECT column list. Is that not how you're reading it?
>

What you are looking at is a subquery - a result of a SELECT is JOINed
to another table. As I said - read it more carefully, see where the
parenthesis balance out, and you'll understand what actually happen.

> >> This has been working until today when I upgraded, so a recent version
> >> of $module has broken this.
> >
> > I need you to *manually* execute the following snippet against your
> > database:
> >
> > SELECT COUNT( publication_story.uid ) AS
> > story_count, me.uid, me.name, me.short_name, me.url, me.description,
> > me.publication_type_uid, me.region_uid, me.channel_type_uid,
> > me.circulation, me.ave1, me.ave2, me.comments FROM publication me
> > WHERE ( ( name LIKE ? OR short_name LIKE ? ) ) GROUP BY me.uid ORDER
> > BY name
>
> (I filled in the ?s)
>
> mysql> SELECT COUNT( publication_story.uid ) AS story_count, me.uid,
> me.name, me.short_name, me.url, me.description,
> me.publication_type_uid, me.region_uid, me.channel_type_uid,
> me.circulation, me.ave1, me.ave2, me.comments FROM publication me
> WHERE ( ( name LIKE '%red orbit%' OR short_name LIKE '%red orbit%' ) )
> GROUP BY me.uid ORDER BY name;
> ERROR 1054 (42S22): Unknown column 'publication_story.uid' in 'field list'
>

Now I'm being an ass - I didn't read properly what I pasted. The problem
is that I am losing a JOIN where I shouldn't. This is a clear regression
I'll fix it sometime tonight (EST). Until then either do not use prefetch
or use 08107.


_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[Peter Rabbitson-2]

On Mon, Jul 13, 2009 at 10:52:24AM -0500, Paul Makepeace wrote:

> I have,
>
> __PACKAGE__->has_many(publication_story => 'IDL::Schema::Story',
> 'publication_uid');
> __PACKAGE__->belongs_to(publication_type => 'IDL::Schema::CodeTree',
> 'publication_type_uid', { join_type => 'left'});
>
> my @SEARCH_WITH_STORY_COUNT_ARGS = (
>   join => [qw/publication_story/],
>   select => [{ count => 'publication_story.uid' }, qw/me.uid me.name
> me.short_name
>     me.url me.description me.publication_type_uid me.region_uid
> me.channel_type_uid
>     me.circulation me.ave1 me.ave2 me.comments/,],
>   as => [qw/story_count uid name short_name url description publication_type_uid
>     region_uid channel_type_uid circulation ave1 ave2 comments/],
>   group_by => [qw/me.uid/],
>   prefetch => [qw/publication_type region/],
> );
>
> and this is generating this wrong-looking SQL,
>
> DBIx::Class::ResultSet::search(): DBI Exception: DBD::mysql::st
> execute failed: Unknown column 'publication_story.uid' in 'field list'
> [for Statement "SELECT me.story_count, me.uid, me.name, me.short_name,
> me.url, me.description, me.publication_type_uid, me.region_uid,
> me.channel_type_uid, me.circulation, me.ave1, me.ave2, me.comments,
> publication_type.uid, publication_type.parent_uid,
> publication_type.value, publication_type.hide, publication_type.alias,
> publication_type.order_priority, publication_type.show_in_lists,
> publication_type.data_type, publication_type.cascade_data_type,
> publication_type.description, region.uid, region.parent_uid,
> region.value, region.hide, region.alias, region.order_priority,
> region.show_in_lists, region.data_type, region.cascade_data_type,
> region.description FROM (SELECT COUNT( publication_story.uid ) AS
> story_count, me.uid, me.name, me.short_name, me.url, me.description,
> me.publication_type_uid, me.region_uid, me.channel_type_uid,
> me.circulation, me.ave1, me.ave2, me.comments FROM publication me
> WHERE ( ( name LIKE ? OR short_name LIKE ? ) ) GROUP BY me.uid ORDER
> BY name) me LEFT JOIN story publication_story ON
> publication_story.publication_uid = me.uid LEFT JOIN code_tree
> publication_type ON publication_type.uid = me.publication_type_uid
> LEFT JOIN code_tree region ON region.uid = me.region_uid WHERE ( (
> name LIKE ? OR short_name LIKE ? ) ) ORDER BY name"
>

Fixed by http://dev.catalyst.perl.org/svnweb/bast/revision/?rev=7046
Please test and report any additional problems.

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...