SQL for getting all submissions in all issues [OJS]

On OJS, I’m trying to extract data to get all categories for all submissions in all issues.
I figured out the categories part but I can’t seem to figure out which table(s) would tie the issue_id to the submission_id. So in short, what would the SQL be to get all submissions/publications in all issues?


So I think it would be something like this

p.publication_id, p.date_published, 
p.submission_id,  ps.setting_value AS submission_title ,
GROUP_CONCAT(DISTINCT cs.setting_value SEPARATOR '|' ) AS category_name
, ps2.setting_name, ps2.setting_value AS issue_id, i.volume, i.number, i.year
publications p 

LEFT JOIN publication_categories pc ON (
p.publication_id = pc.publication_id
LEFT JOIN category_settings cs ON (pc.category_id = cs.category_id AND cs.setting_name = 'title')

LEFT JOIN publication_settings ps ON (p.publication_id = ps.publication_id )
LEFT JOIN publication_settings ps2 ON (p.publication_id = ps2.publication_id AND ps2.setting_name = 'issueId')
LEFT JOIN issues i ON (ps2.setting_value = i.issue_id)

WHERE p.publication_id = ps.publication_id AND ps.setting_name = 'title'
GROUP BY p.submission_id
1 Like