[storm] Subselect query

[James Henstridge]

On Fri, Jun 14, 2013 at 7:33 PM, Aurélien Bompard <aurelien at bompard.org> wrote:
>> This SQL query works:
>> SELECT DISTINCT sender_name, sender_email, (SELECT count(*) FROM
>> email e2 WHERE e2.sender_email = e1.sender_email) AS number FROM
>> email e1 ORDER BY number DESC;
>
> OK, the subselect isn't necessary, I've replaced it with a GROUP BY,
> which brings me closer to the solution, but not quite there yet. Here's
> my new query:
>
> SELECT sender_name, sender_email, COUNT(sender_email) AS number
> FROM email GROUP BY sender_email ORDER BY number DESC;
>
> Which I can execute in Storm as:
>
> store.find(Email, Count(Email.sender_email))).group_by(Email.sender_email)
>
> This returns me tuples of Email instances and int, which is nice.
> However I used to run the query without the count before, and I only
> returned the sender_name and sender_email columns by adding this:
>
> .values(Email.sender_name, Email.sender_email)
>
> Is there a way I can only return a (sender_name, sender_email, count)
> tuple ? Just adding the count in the values() call raises an
> AttributeError: 'Count' object has no attribute 'variable_factory'.

Sure.  Adjust the first argument to the store.find() call to
(Email.sender_name, Email.sender_email, Count(Email.sender_email)).
The tuples you retrieve from the result set will hold the column
values rather than the whole Email object.

James.