SQL - How to select tickets based on custom-field value

[wachv]

Sorry to bother with such a question but I am working on a Mantis instance which has been deployed a long time ago and no one around can help with this issue.

Because of changes in the way we handle the tickets, we need to select all tickets belonging to a given project with a specific value in a defined custom-field, in order to update their category value. As we have around 3000 tickets, we do not consider the one-by-one manual update

Could someone help us to understand where the custom-field value associated to a bugID is stored so we can find out the SQL sentence to achieve the objective mentioned in the topic's title ?

The custom-field qualification is properly defined in mantis_custom_field_table; its ID is 13
In the custom-field-project-table, the custom_field ID 13 is well defined for the project_id 5 ; its sequence number is 50

[elmarculino]

I don't know if this is the better way, but this is how i do:

Code: Select all

   SELECT b.id as id,
      t.description AS descricao,
      c.name AS categoria,
      GROUP_CONCAT((CASE field_id WHEN 24 THEN s.value ELSE NULL END)) AS custom_field,
      GROUP_CONCAT((CASE field_id WHEN 25 THEN s.value ELSE NULL END)) AS custom_field2
   FROM mantis_bug_table b
   INNER JOIN mantis_bug_text_table t ON b.id=t.id
   INNER JOIN mantis_category_table c ON b.category_id=c.id
   INNER JOIN mantis_custom_field_string_table s ON b.id=s.bug_id
   WHERE b.project_id = 1
   GROUP BY b.id