[storm] Selecting a count on a group

[James Henstridge]

On Wed, Mar 11, 2009 at 11:11 AM, Bryan Ward <bward1 at gmail.com> wrote:
> I have written an sql query that I would like to implement in storm, but I
> can't seem to figure out how.  Here is the MySQL query I am trying to
> replicate:
>
> SELECT h.*,COUNT(f.height_id) AS factorsCount
> FROM height AS h
> LEFT JOIN factors AS f ON (f.height_id = h.id)
> GROUP BY h.id
> HAVING factorsCount=4
> ORDER BY h.id;
>
> I can't figure out a way to access the the column aliased as factorsCount.
> Is this type of query supported or do I need to do I need to drop into raw
> SQL for these types of queries?

You should be able to write this query with Storm as follows, assuming
you have classes Height and Factors to represent the underlying
tables:

    result = store.using(LeftJoin(
        Height, Factors, Factors.height_id == Height.id)).find(
        (Height, Count(Factors.height_id)))
    result.group_by(Height)
    result.having(Count(Factors.height_id) == 4)
    result.order_by(Height.id)

The result set should then yield (height_instance, integer) tuples.

Of course, with the given query it shouldn't be necessary to do a left
join since all heights without corresponding factors records will have
a factorsCount of zero and be knocked out by the having clause.  So
you can replace the first statement with just:

    result = store.find((Height, Count(Factors.height_id)),
                        Factors.height_id == Height.id)

If you really want an alias to appear in the query, that is possible
too.  You can define the alias like so:

    factors_count = Alias(Count(Factors.height_id, "factorsCount")

And then replace all uses of the Count() expression in the query with
factors_count.  Storm will know to compile this to "expr AS name" in
column context and just "name" in other places.  As the end result is
the same, I usually wouldn't bother for something like this though.

Hopefully this gives you some ideas about how to solve your problem.

James.