i have a Group::Manager query like:
my $active = $self->get_groups(
distinct => 1,
fetch_only => [ $self->object_class->meta->table ],
## silence warning from RDBOM about redundant data -- there
is no
## redundant data anyway, because of 'fetch_only'
multi_many_ok => 1,
require_objects => ['collections.collection_assets'],
group_by => 't1.group_id',
sort_by => 'COUNT(DISTINCT collection_assets.asset_id) DESC',
);
which produces a query that looks (roughly) like
SELECT DISTINCT t1.*
FROM agroup t1 JOIN (collection t2 JOIN collection_asset t3 ON
(t2.collection_id = t3.collection_id)) ON (t1.group_id = t2.group_id)
GROUP BY t1.group_id ORDER BY COUNT(DISTINCT t3.asset_id) DESC
which works great for mysql 5.0.x, but mysql 4.1.x complains "Invalid
use of group function" and wants me to do something like:
SELECT DISTINCT t1.*, COUNT(DISTINCT t3.asset_id) as
temp_asset_count
FROM agroup t1 JOIN (collection t2 JOIN collection_asset t3 ON
(t2.collection_id = t3.collection_id)) ON (t1.group_id = t2.group_id)
GROUP BY t1.group_id ORDER BY temp_asset_count DESC
but i can't seem to coerce this out of the get_objects() call. here
is what i tried:
my $active = $self->get_groups(
distinct => 1,
select => [ 't1.*', 'COUNT(DISTINCT t3.asset_id) as
temp_asset_count' ],
multi_many_ok => 1,
require_objects => ['collections.collection_assets'],
group_by => 't1.group_id',
sort_by => 'temp_asset_count DESC',
);
but the generated query contains "ORDER BY t1.temp_asset_count DESC"
and i can't seem to get the "t1." not added to the ORDER BY clause.
i know i can resort to get_objects_from_sql, but are there any other
suggestions?
---
michael reece :: software engineer :: [EMAIL PROTECTED]
-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/
_______________________________________________
Rose-db-object mailing list
Rose-db-object@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/rose-db-object
