Peter -
Sorry for the delay, I just now had time to get to this. It looks good! I've
included the generated SQL below so you can confirm that we're getting what
you're expecting. FYI, I ran the sql through the query analyzer and the new
SQL is *much* more efficient.
Many Thanks!
- Alan
my $items = $schema->resultset('Birds')->search(
{},
{order_by => {'-asc' => 'common_name'},
unsafe_subselect_ok => 1,
prefetch => [qw/genus species/],
page => 4,
rows => 10}
);
OLD:
SELECT me.id, me.genus_id, me.species_name_id, me.image_id,
me.north_america_map_id, me.washington_map_id, me.default_sound_id,
me.is_washington_bird, me.common_name, me.taxonomic_order, me.identification,
me.voice, me.habitat, me.behavior, me.diet, me.nesting, me.migration,
me.where_found, me.conservation_status, me.breeding, me.notes, genus.id,
genus.family_id, genus.genus_scientific_name, genus.genus_common_name,
genus.genus_description, species.id, species.species_scientific_name,
species.species_name_notes FROM ( SELECT me.id, me.genus_id,
me.species_name_id, me.image_id, me.north_america_map_id, me.washington_map_id,
me.default_sound_id, me.is_washington_bird, me.common_name, me.taxonomic_order,
me.identification, me.voice, me.habitat, me.behavior, me.diet, me.nesting,
me.migration, me.where_found, me.conservation_status, me.breeding, me.notes
FROM ( SELECT me.*, ROW_NUMBER() OVER( ORDER BY (SELECT(1)) ) AS
rno__row__index FROM ( SELECT me.id, me.genus_id, me.species_name_id,
me.image_id, me.north_america_map_id, me.washington_map_id,
me.default_sound_id, me.is_washington_bird, me.common_name, me.taxonomic_order,
me.identification, me.voice, me.habitat, me.behavior, me.diet, me.nesting,
me.migration, me.where_found, me.conservation_status, me.breeding, me.notes
FROM (SELECT TOP 4294967296 me.id, me.genus_id, me.species_name_id,
me.image_id, me.north_america_map_id, me.washington_map_id,
me.default_sound_id, me.is_washington_bird, me.common_name, me.taxonomic_order,
me.identification, me.voice, me.habitat, me.behavior, me.diet, me.nesting,
me.migration, me.where_found, me.conservation_status, me.breeding, me.notes
FROM birds me JOIN genera genus ON genus.id = me.genus_id JOIN species_names
species ON species.id = me.species_name_id ORDER BY common_name ASC) me ) me )
me WHERE rno__row__index BETWEEN 31 AND 40 ) me JOIN genera genus ON genus.id =
me.genus_id JOIN species_names species ON species.id = me.species_name_id ORDER
BY common_name ASC:
NEW:
SELECT id, genus_id, species_name_id, image_id, north_america_map_id,
washington_map_id, default_sound_id, is_washington_bird, common_name,
taxonomic_order, identification, voice, habitat, behavior, diet, nesting,
migration, where_found, conservation_status, breeding, notes, genus__id,
genus__family_id, genus__genus_scientific_name, genus__genus_common_name,
genus__genus_description, species__id, species__species_scientific_name,
species__species_name_notes FROM ( SELECT id, genus_id, species_name_id,
image_id, north_america_map_id, washington_map_id, default_sound_id,
is_washington_bird, common_name, taxonomic_order, identification, voice,
habitat, behavior, diet, nesting, migration, where_found, conservation_status,
breeding, notes, genus__id, genus__family_id, genus__genus_scientific_name,
genus__genus_common_name, genus__genus_description, species__id,
species__species_scientific_name, species__species_name_notes, ORDER__BY__1,
ROW_NUMBER() OVER( ORDER BY ORDER__BY__1 ASC ) AS rno__row__index FROM ( SELECT
me.id, me.genus_id, me.species_name_id, me.image_id, me.north_america_map_id,
me.washington_map_id, me.default_sound_id, me.is_washington_bird,
me.common_name, me.taxonomic_order, me.identification, me.voice, me.habitat,
me.behavior, me.diet, me.nesting, me.migration, me.where_found,
me.conservation_status, me.breeding, me.notes, genus.id AS genus__id,
genus.family_id AS genus__family_id, genus.genus_scientific_name AS
genus__genus_scientific_name, genus.genus_common_name AS
genus__genus_common_name, genus.genus_description AS genus__genus_description,
species.id AS species__id, species.species_scientific_name AS
species__species_scientific_name, species.species_name_notes AS
species__species_name_notes, common_name AS ORDER__BY__1 FROM birds me JOIN
genera genus ON genus.id = me.genus_id JOIN species_names species ON species.id
= me.species_name_id ) me ) me WHERE rno__row__index BETWEEN 31 AND 40 :
> -----Original Message-----
> From: Peter Rabbitson [mailto:[email protected]]
> Sent: Wednesday, May 05, 2010 3:35 AM
> To: DBIx::Class user and developer list
> Subject: Re: [Dbix-class] fate of mssql_limit_regression branch?
>
> Alan Humphrey wrote:
> > I've installed the trunk code and, for my purposes, the
> unsafe_subselect_ok attribute is probably going to be sufficient.
> Thank you for your thorough documentation.
> >
>
> Hi,
>
> Waking up this old thread to get some of your testing input. A thorough
> rewrite[1] of the subselecting limit emulations has just landed in
> trunk[2] and *in theory* it should allow you to get rid of almost all
> unsafe_subselect_ok flags in your code (as the queries now changed to
> include much less subqueries to arrive at the same result). Please test
> if time permits, before this lands on CPAN. Your input is appreciated!
>
> Cheers
>
> [1] http://dev.catalystframework.org/svnweb/bast/revision?rev=9305
> [2] http://dev.catalyst.perl.org/repos/bast/DBIx-Class/0.08/trunk/
>
> _______________________________________________
> 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-
> [email protected]
_______________________________________________
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/[email protected]
