chwheels ORM and GROUP BY clause

[PuMa]

Howdy wheelers,

My first cfwheels app is coming along nicely..I couldn't have done it
without the excellent documentation provided.
Saying that, I just hit a snag, and was wondering if anyone could
convert this MS SQL statement with the findAll() method.

<cfquery name="qryArchiveMenu" datasource="#dsn#">
SELECT
YEAR(createdat) AS yr,
MONTH(createdat) AS mth,
(CAST(YEAR(createdat) as varchar)+'-'+CAST(MONTH(createdat) as
varchar)) AS menudate,
COUNT(*) as feedcount
FROM feeds
WHERE
createdat >= dateadd(yyyy,-1,getdate())
GROUP BY
YEAR(createdat), MONTH(createdat)
ORDER BY
YEAR(createdat) DESC, MONTH(createdat) DESC
</cfquery>


The above query returns the count of monthly feeds for the past 12
months.
i.e :
- AUG-2010 (3)
- JULY-2010 (10)
and so on..

So, I was thinking to use some Calculated Properties and then use the
findAll() method, but then realize that there is no GROUP BY option.
How do you guys handle the GROUP BY statement ? or did I miss
something in the docs ?

[raulriera]

Have you tried dumping that whole sucker in a calculated property?
Wrap it around a parenthesis and drop it there... like I did here

<cfset property(name="percentageCompleted", sql="(SELECT ((SUM(CASE
WHEN isCompleted=1 THEN 1 ELSE 0 END))/COUNT(isCompleted))*100 FROM
treatmentplandetails WHERE treatmentplandetails.treatmentPlanId =
treatmentplans.id)")>

[Mike Henke]

lol, that is cool but kinda ugly. might be better to just do it in a
cffunction in the model. i think release 1.1 might have this feature

[Andy Bellenie]

Yes that's right, the next version of Wheels has support for group by
clauses. For now you can either:

a) use the bleeding edge version
b) write a custom query

Andy

> --
> You received this message because you are subscribed to the Google Groups "ColdFusion on Wheels" group.
> To post to this group, send email to cfwh...@googlegroups.com.
> To unsubscribe from this group, send email to cfwheels+u...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/cfwheels?hl=en.
>
>

[PuMa]

cheers guys,

@raulriera, I tried your method but sadly CF rejected it, got an
exception "The ORDER BY clause is invalid in views, inline
functions..."

No worries, will just wrap my custom query in a function.

Once 1.1 is out,I will update it.

On Aug 5, 5:49 am, Andy Bellenie <andybelle...@gmail.com> wrote:
> Yes that's right, the next version of Wheels has support for group by
> clauses. For now you can either:
>
> a) use the bleeding edge version
> b) write a custom query
>
> Andy
>

[Mike Henke]

Hey Raul,

How would I do something like this getCategoriesWithCounts with Wheels
ORM? Say I have an entries table with categoryid and a categories
table with id and category.

Thanks,

[raulriera]

Do you mean something like this Mike?

// gallery model
<cfset property(name="photoCount", sql="(SELECT COUNT(*) FROM photos
WHERE photos.galleryId = galleries.id)")>

Your example will be

<cfset property(name="categoriesCount", sql="(SELECT COUNT(*) FROM
categories WHERE categories.id = entries.categoryId)")>

Then you will have that property on your finder methods

[Mike Henke]

Sweet. I'll give it a try tonight.