dragonchild has asked for the wisdom of the Perl Monks concerning the following question:

I've got a really big itch with DBIx::Class and I want to scratch it really bad, but I want to make sure I don't scratch a big hole by missing something blindingly obvious.

I want to add auto-groupby magic to queries that have a function in the SELECT or ORDER BY clauses that would require a group by. It pisses me off that I have to enumerate all my columns when DBIC has all the necessary information. Well, I think it does.

So, I'm looking for an example of where the ordering of the groupby can cause a different resultset to be returned. Assume that ordering is irrelevant.

My criteria for good software:
  1. Does it work?
  2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?
  • Comment on (somewhat OT) Seeking a SQL counter-example

Replies are listed 'Best First'.
Re: (somewhat OT) Seeking a SQL counter-example
by extremely (Priest) on Feb 28, 2007 at 21:26 UTC
    IDSalesIDCityIDMoney
    110201000
    210222000
    311204000
    411228000
    510203000
    610225000
    711207000
    811229000
    select from table SalesID,CityID,Sum(Money) Groupby SalesID,CityID
 -- or
select from table SalesID,CityID,Sum(Money) Groupby CityID, SalesID

    [download]

    Those should vary only in output order of the summed rows. The danger of automatic grouping is when/if someone picks "ID" in the select list... then it all goes to heck. The "common case" for Group By is to list all columns not aggregated in the select_column clause. Automatically forcing group-by seems to be almost assuredly doomed to run into keys and foreign-keys when they are incorrectly added to the columns list.

    In your limited case, tho, I'm thinking you're safe.

    --
    $you = new YOU;
    honk() if $you->love(perl)

[reply]
[d/l]
      Assuming that ordering is unimportant (because if ordering is important, you'll put an ORDER BY clause in), then does this mean that automagical grouping if there is a function that requires it (count, max, min, sum, etc) is bad or ok?
      My criteria for good software:
      1. Does it work?
      2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?
[reply]
Re: (somewhat OT) Seeking a SQL counter-example
by terce (Friar) on Feb 28, 2007 at 23:13 UTC

    A minor issue - it's valid SQL syntax to include columns in the GROUP BY clause which aren't listed in the SELECT clause (although it's not often used, and I don't know if DBIx::Class supports it).

    Adding auto-groupby would break this unless the user is allowed to override the auto-grouping.

    It might also become a maintenance trap for the unwary; the existing DBIx::Class syntax is clearly recognisable as a GROUP BY query to somebody familiar with SQL. An auto-grouped version might not - it depends on the implementation

    Otherwise, I don't think column order in GROUP BY makes a difference

[reply]
[d/l]

Back to Seekers of Perl Wisdom