How to find the articles of a published issue in the database

Software Support
1

Description of issue or problem I’m having:

For creating an index I read information directly from the database. I have a script that worked properly in our earlier version of OJS (3.1, if I remember correctly). With the upgrade to OJS 3.3.0.8, the structure of the database changed somewhat and I cannot find how I can get the list of articles that are included in a given issue of my journal.

Steps I took leading up to the issue:

In the old version for OJS 3.1, I used the SQL-statement “select published_submission_id, submission_id, date_published from published_submissions where issue_id = $id order by seq” (passed along the issue_id in $id. This gave me information only about the articles published in this issue.

I looked through the structure of the database under 3.3.0.8, but could not find any table that links the issue_id with either the publication_id or the submission_id.

What I tried to resolve the issue:

I checked the structure of the tables in the database. I listed the content of various tables (I have read access to the database, but not to the installation of OJS).

Application Version - e.g., OJS 3.1.2:

OJS 3.3.0.8

Additional information, such as screenshots and error log messages if applicable:

1 Like
2

Hy @Gunther_Maier

honestly I thought I can figure this out in a few minutes. Wrong; never worked on the issue level on the DB. Depending on your programming language for this script it might be possible to switch over to the REST API where you can fairly easily get (i) a list of issues and (ii) articles including the publications (publication galleys) for each of the issues.

May be the safer way as the API should change less frequently than the DB structure

3

Dear @retostauffer
many thanks for your feedback. I did go via the APIs. That works well. The only drawback is that it is considerably slower than a database query to the same machine.
Many thanks again
Gunther

4

For those that will land here in future with more recent OJS versions … Grab the issueId from the UI then replace <IssueId> with your value.

SELECT * FROM `publications`
WHERE publication_id IN (
    SELECT publication_id 
    FROM `publication_settings` 
    WHERE setting_name = 'issueId' 
    AND setting_value = <IssueId>
);

Output will look like this

image
image1600×622 80.3 KB