[Wt-interest] Can SQL have 'having'

[Matthew Sherborne]

Hi guys,

I my program, I want to be able to say, get me the rows that have 'all'
these tags.

I can do it with group by and having, but Wt doesn't seem to support the
'having' clause, and my SQL foo is not strong enough to do it without (yet).

Here's my postgres query that does what I want:

SELECT tr.*
string_agg(tag.name, ', ' order by tag.name) as tags
FROM transaction tr
LEFT JOIN transaction_tag tt
ON tr.id = tt.transaction_id
JOIN account a
ON tr.account_id = a.id
JOIN tag
ON tag.id = tt.tag_id
group by tr."id", tr."version", tr."date", tr."amount", tr."comments",
tr."account_id", account_name
having array_agg(tag.id) @> ARRAY[36, 48]::BIGINT[]
order by tr.date
limit 10;

Any SQL help appreciated, or ways to get Wt to support a 'having()' func,
similar to 'where()'.

Many Thanks
Matthew Sherborne

[Matthew Sherborne]

I have created a pull request for it. It works for me..

https://github.com/kdeforche/wt/pull/70

Allows you to go: dbo::Query<std::string, int>("select name, sum(employees)
from department").grouyBy("name").having("sum(employees) > 5");

Please pull, or demand tests or comment developer guys.

Many Thanks
Matthew Sherborne

Post by Matthew Sherborne
Hi guys,
I my program, I want to be able to say, get me the rows that have 'all'
these tags.
I can do it with group by and having, but Wt doesn't seem to support the
'having' clause, and my SQL foo is not strong enough to do it without (yet).
SELECT tr.*
string_agg(tag.name, ', ' order by tag.name) as tags
FROM transaction tr
LEFT JOIN transaction_tag tt
ON tr.id = tt.transaction_id
JOIN account a
ON tr.account_id = a.id
JOIN tag
ON tag.id = tt.tag_id
group by tr."id", tr."version", tr."date", tr."amount", tr."comments",
tr."account_id", account_name
order by tr.date
limit 10;
Any SQL help appreciated, or ways to get Wt to support a 'having()' func,
similar to 'where()'.
Many Thanks
Matthew Sherborne