Help with report of tickets by a user

[Albert Chin]

Hi. I need help designing a report to show all tickets where the user
is the owner, reporter, or tickets they have not created but tickets
they have left a comment on. It's the last part that's tricky. I can
accomplish the first two with:
SELECT p.value AS __color__,
t.milestone AS __group__,
id AS ticket, t.type AS type, owner, reporter,
time as created, changetime AS modified
FROM ticket t, enum p
WHERE owner = 'gary' OR reporter = 'gary'
ORDER BY t.milestone, p.value, t.type, t.time

I am running 0.12.1. Any help greatly appreciated.

--
albert chin (ch...@thewrittenword.com)

[Steffen Hoffmann]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Am 22.08.2012 04:33, schrieb Albert Chin:
> Hi. I need help designing a report to show all tickets where the user
> is the owner, reporter, or tickets they have not created but tickets
> they have left a comment on. It's the last part that's tricky. I can
> accomplish the first two with:
> SELECT p.value AS __color__,
> t.milestone AS __group__,
> id AS ticket, t.type AS type, owner, reporter,
> time as created, changetime AS modified
> FROM ticket t, enum p
> WHERE owner = 'gary' OR reporter = 'gary'
> ORDER BY t.milestone, p.value, t.type, t.time

No, this won't work as you might expect. You'll need to JOIN tables, or
you'll end up with a lot of duplicated rows otherwise (look for
'cartesian product', if you don't understand that).

This is one is a more appropriate report SQL statement, at least to show

"(current) user is owner or reporter"

SELECT
p.value AS __color__,
t.milestone AS __group__,
id AS ticket, t.type AS type, owner, reporter,
time as created, changetime AS modified
FROM ticket t

LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
WHERE owner = $USER OR reporter = $USER

ORDER BY t.milestone, p.value, t.type, t.time

Note, that I replaced you hard-coded username with one of the available
variables to dynamically show results depending on logged in user,
because you might rather want that instead of one-report-per-user.

About the comment, this is in another table, ticket_change. I'm just
looking at it. Will keep you updated, if I see a way to do this too.

Sincerely,

Steffen Hoffmann
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAlA+j7EACgkQ31DJeiZFuHcJkACg0T48GeonwUf9k6Sy6oet0ew3
jrsAnA+oQvOCSElJR6L9r4uUQPplzfmm
=Ofh/
-----END PGP SIGNATURE-----

[Steffen Hoffmann]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Am 29.08.2012 23:54, schrieb Steffen Hoffmann:
> Am 22.08.2012 04:33, schrieb Albert Chin:
>> Hi. I need help designing a report to show all tickets where the user
>> is the owner, reporter, or tickets they have not created but tickets
>> they have left a comment on. It's the last part that's tricky.

[...]

> This is one is a more appropriate report SQL statement, at least to show
>
> "(current) user is owner or reporter"

[...]

>
> About the comment, this is in another table, ticket_change. I'm just
> looking at it. Will keep you updated, if I see a way to do this too.

See http://stackoverflow.com/questions/12187847 for a possible solution,
that covers all three of your requirements:
reporter, ticket owner (both from 'ticket') as well as
comment author (from 'ticket_change')

and works so without multiple entries per ticket. This last aspect seems
likely the most tricky one, not the inclusion of comment authors in general.

Hope this helps.

Sincerely,

Steffen Hoffmann
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAlA+pu0ACgkQ31DJeiZFuHfJvQCgjk5Zzd8twXGzIPEfRQbVIzac
aZYAni5BahJEZBaF9wHpq7pLB8gkrtMD
=HB+g
-----END PGP SIGNATURE-----