Feature #27875

SQL custom query

Added by Tai Le Trinh Huu 4 months ago. Updated 4 months ago.

Status:NewStart date:
Priority:NormalDue date:
Assignee:-% Done:

0%

Category:Issues filter
Target version:-
Resolution:

Description

dear friend,
I would like to get help with a custom query which I can get data from Redmine Database with below field and condition
Issue ID/ Tracker/ Subject / Author/ Assignee / Created / Start time / Closed / Status /Custom field 1/ Custom field 2 / Category / Priority
and the condition to get data from date to date ( between ddmmyyyy - ddmmyyyy)
thank you so much !

History

#1 Updated by Toshi MARUYAMA 4 months ago

  • Tracker changed from Defect to Feature

#2 Updated by Toshi MARUYAMA 4 months ago

  • Category changed from Issues to Issues filter

#3 Updated by Jeremy Bailey 4 months ago

Hope this helps you, this is tested for me on PostgreSQL but may not work so well on MySQL. I've had to write similar queries in the past.

I'm assuming that the custom fields are for issues, not projects or something. You'll need to look up the custom field values from the Redmine admin menu for each of your two custom fields, and replace 2 and 80 with these ids. The dates can be changed in the query.

You'll most likely want to use ISO date format.

SELECT  issues.id
        ,trackers.name as "tracker" 
        ,issues.subject
        ,authors.login as "author" 
        ,assignees.login as "assignee" 
        ,issues.created_on
        ,issues.start_date
        ,issues.closed_on
        ,issue_statuses.name as "status" 
        ,custom1.value as "custom_1" 
        ,custom2.value as "custom_2" 
        ,issue_categories.name as "category"  
        ,enumerations.name as "priority" 
FROM issues
LEFT JOIN trackers
  ON trackers.id = issues.tracker_id
LEFT JOIN users authors
  ON authors.id = issues.author_id
LEFT JOIN users assignees
  ON assignees.id = issues.assigned_to_id  
LEFT JOIN enumerations
   ON enumerations.id = issues.priority_id
  AND enumerations.type = 'IssuePriority'
LEFT JOIN issue_statuses ON issue_statuses.id = issues.status_id
LEFT JOIN custom_values custom1
  ON custom1.customized_id = issues.id
  AND custom1.customized_type = 'Issue'
LEFT JOIN custom_values custom2
  ON custom2.customized_id = issues.id
  AND custom2.customized_type = 'Issue'
LEFT JOIN issue_categories
  ON issue_categories.id = issues.category_id
WHERE issues.id IS NOT NULL
  AND issues.start_date BETWEEN '2016-04-01' AND '2018-01-03'
  AND custom1.custom_field_id = '2'
  AND custom2.custom_field_id = '80'
GROUP BY issues.id, trackers.name, authors.login, assignees.login, custom1.value, custom2.value, issue_statuses.name, enumerations.name, issue_categories.name

Also available in: Atom PDF