sql query for attach id in current comment

Question

Hello,

got 2 different comments with attachmets with same name, but with different content.

need select from jira db which returns attach id for current comment for current attach file  name.

something like: select [attach file id] .... where [commentid]=xxxx and [attach file name]='xxxxxxxx'

any ideas?

Виктор Максимов_hesend · Answer

In-depth analysis of the problem showed that files with the same name are saved in the issue, but there is no data for linking a specific file to a specific comment (or tag [^] inside the comment). This is sad :)  so you have to insert short stubs that solve this problem at the script level.

If I am mistaken I will be glad to see the solution.

kortov · Answer

AFAIK there are 2 ways to get comments:

with users as (select LOWER_USER_NAME, DISPLAY_NAME, UPDATED_DATE,
row_number() over(partition by LOWER_USER_NAME
                   order by UPDATED_DATE desc) seq
from CWD_USER)
select ja.id as id, ja.ISSUEID as issueId, dbms_lob.substr( ja.ACTIONBODY, 4000, 1 ) as content,
  ja.AUTHOR as commentLogin, users.DISPLAY_NAME as commentFio, ja.CREATED as createdDate, ja.id
from
  (
select ISSUEID, ID, CREATED,
row_number() over(partition by ISSUEID
        order by CREATED desc) seq
from JIRAACTION
where ISSUEID = {ISSUEID}
  ) lastComments
join JIRAACTION ja on lastComments.id = ja.ID
    join users on ja.AUTHOR = users.LOWER_USER_NAME and users.seq = 1
where ja.ACTIONTYPE = 'comment'

{jiraUrl}/rest/api/2/issue/{issueKeyOrId}/comment

And there no mention about id of comment. And as I can see, JIRA doesn't keep ids of of attachements in comments, so if you upload two attachements with the same name in two comments the link to attachement in comments will be the same