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.
>
> But (and you knew there was going to be a "but", right?) the trunk code still
> breaks on the following (same place as above):
>
> $items = $c->model('BirdWebDB::Birds')->search(
> {},
> {order_by => {'-'. $sort_order => $sort_column},
> unsafe_subselect_ok => 1,
> join => ['shadebirds'],
> prefetch => [qw/genus species/],
> page => $page,
> rows => $limit}
> );
>
> [error] DBI Exception: DBD::Sybase::st execute failed: Server message
> number=306 severity=16 state=2 line=1 server=SQLB29 text=The text, ntext, and
> image data types cannot be compared or sorted, except when using IS NULL or
> LIKE operator.
> [for Statement "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.genu
s_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
shadecoffee_bird_info shadebirds ON shadebirds.bird_id = me.id JOIN genera
genus ON genus.id = me.genus_id JOIN species_names species ON species.id =
me.species_name_id ORDER BY taxonomic_order ASC) me ) me ) me WHERE
rno__row__index BETWEEN 1 AND 10 ) me JOIN shadecoffee_bird_info shadebirds O
N shadebirds.bird_id = me.id JOIN genera genus ON genus.id = me.genus_id JOIN
species_names species ON species.id = me.species_name_id GROUP BY 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 ORDER
BY taxonomic_order ASC"]
>
> After playing with the generated SQL it turns out the problem is in the GROUP
> BY statement which has a bunch of text columns.
>
> Let me know if there's anything I can do.
>
One step ahead of you. This group_by should not have been there, and the
issue has been resolved by a merge from about 4 hours ago. Please update
your checkout and try again.
_______________________________________________
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]
