SQL Assistance - Getting from Users to Issues

Submitted by mrlynn on 2009-09-14

Greetings and thanks for a great product! I'm just starting to use it to manage work for my team of engineers.

I'm attempting to write some custom reporting to show open issues, features, bugs, etc. by user. I'm not quite sure about the relationship between issues and users since there doesn't appear to be a direct link in the schema. I see a table called stamps which has user_id and stamp_id... and since the issues table also has stamp_id, can I assume that I can relate users to issues through stamps?

I'm sure there must be some hairy MySQL join statement to get me there but I'm fair from an advanced sql user.

Any help greatly appreciated.

Regards,
Mike

The stamps table has a relationship with the following tables and columns: issues.issue_id, changes.change_id, files.file_id and comments.comment_id. That's why identifiers of issues, changes, files and attributes are unique and also why there are no direct relations with users in these tables. Such structure makes incremental updating of data easier. The stamp_id column of issues is the last modification "stamp" of the issue, in other words the ID of the latest change, file or comment.

This is the query the server uses to get issue details: 

$query = 'SELECT i.folder_id, i.issue_name, i.stamp_id,'
            . ' sc.stamp_time AS created, sc.user_id AS created_by,'
            . ' sm.stamp_time AS modified, sm.user_id AS modified_by'
            . ' FROM {issues} AS i'
            . ' JOIN {stamps} AS sc ON sc.stamp_id = i.issue_id'
            . ' JOIN {stamps} AS sm ON sm.stamp_id = i.stamp_id';

Sorry for late response, I just returned from vacation.

Regards,
Michał

thanks!

I see that stamps holds creation and modification information. I'm actually looking for some way to report on issues by 'Assigned to' and 'Status' not equal to 'Closed' attribute values so that I can prepare a weekly issues review report.

Ok - I give up... I don't think I'll ever understand the concept of multi-table, multi-criterion joins... I can't quite understand how to create the sql statement that will extract issues that have multiple criteria for selection... something like...

select user_id,user_name,attr_value as status, issue_name from users,attr_values,issues...

but I only want issues that have an attr_values.attr_value!='closed' and... I know I somehow have to also correlate user_name to attr_values to establish issue assignment to users.

I realize this is not a specific issue or problem with your code but I'd appreciate any assistance you can lend. Without some way to get a report of issues assigned to specific team members, the product becomes very difficult to use... ultimately, I'd like to see some form of reporting capability in the product that would roll-up issues by who they are assigned to. Something like an open-issues report that could be used periodically for a team review. As it is now, I have to go project by project, folder by folder to review open issues.

Thanks,
Mike

The query could look like this (assuming you want to display attributes with ID 1 and 4):

SELECTi.issue_id, i.issue_name, sm.stamp_time AS modified_date, um.user_name AS modified_by, a1.attr_value AS v1, a4.attr_value AS v4
FROM issues AS i
INNER JOIN stamps AS sm ON sm.stamp_id = i.issue_id
INNER JOIN users AS um ON um.user_id = sm.user_id
LEFT OUTER JOIN attr_values AS a1 ON a1.issue_id = i.issue_id AND a1.attr_id = 1
LEFT OUTER JOIN attr_values AS a4 ON a4.issue_id = i.issue_id AND a4.attr_id = 4
WHERE i.folder_id = 1 AND a1.attr_value = 'Closed' ORDER BY i.issue_id ASC

Let me know if you need more assistance, I can help you create the that queries you need.

Version 1.x will have the possibility of periodically mailing reports with issues matching selected criteria, for now you can use the dashboard to simplify the review process.

Regards,
Michał