Project

General

Profile

Actions

Feature #27875

closed

SQL custom query

Added by Tai Le Trinh Huu over 6 years ago. Updated 4 months ago.

Status:
Closed
Priority:
Normal
Assignee:
-
Category:
Issues filter
Target version:
-
Start date:
Due date:
% Done:

0%

Estimated time:
Resolution:
Duplicate

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 !


Related issues

Related to Redmine - Feature #5098: Custom SQL QueriesNew2010-03-16

Actions
Actions #1

Updated by Toshi MARUYAMA over 6 years ago

  • Tracker changed from Defect to Feature
Actions #2

Updated by Toshi MARUYAMA over 6 years ago

  • Category changed from Issues to Issues filter
Actions #3

Updated by Jeremy Bailey over 6 years 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
Actions #4

Updated by Go MAEDA 4 months ago

  • Status changed from New to Closed
  • Resolution set to Duplicate

Closing this issue as a duplicate of #5098.

Actions #5

Updated by Go MAEDA 4 months ago

Actions

Also available in: Atom PDF