How to select articles In Review?

ramon · December 13, 2016, 6:42pm

Hello all,

I need a list of articles In Review queue, with full title and ID’s to send to Editorial Board.

I’m a bit confused as to the meaning of the columns in the table articles.
I assumed that each stage of the process would have a number:

0 = Incomplete
1 = In Review
2 = In Editing
and so forth…

But that doesn’t seem to be the case.
Can anyone complete my SQL query?
x being the journal_id I already know…

SELECT a.article_id, s.setting_value FROM article_settings AS s
LEFT JOIN articles AS a ON a.article_id = s.article_id
WHERE a.journal_id = x AND s.setting_name = "cleanTitle" AND a.status = ??

ctgraham · December 13, 2016, 9:33pm

(Assuming OJS 2.x)

Take a look at this function to discriminate between unassigned/in-review/in-editing.

github.com

pkp/ojs/blob/ojs-stable-2_4_8/classes/submission/editor/EditorSubmissionDAO.inc.php#L511-L587


	/**
	 * Function used for counting purposes for right nav bar
	 */
	function &getEditorSubmissionsCount($journalId) {
		$submissionsCount = array();
		for($i = 0; $i < 3; $i++) {
			$submissionsCount[$i] = 0;
		}

		// Fetch a count of unassigned submissions.
		// "e2" and "e" are used to fetch only a single assignment
		// if several exist.
		$result =& $this->retrieve(
			'SELECT	COUNT(*) AS unassigned_count
			FROM	articles a
				LEFT JOIN edit_assignments e ON (a.article_id = e.article_id)
				LEFT JOIN edit_assignments e2 ON (a.article_id = e2.article_id AND e.edit_id < e2.edit_id)
			WHERE	a.journal_id = ?
				AND a.submission_progress = 0
				AND a.status = ' . STATUS_QUEUED . '

This file has been truncated. show original

The articles.status field really only keys on these values:

github.com

pkp/ojs/blob/ojs-stable-2_4_8/classes/article/Article.inc.php#L22-L27


// Submission status constants
define('STATUS_ARCHIVED', 0);
define('STATUS_QUEUED', 1);
// define('STATUS_SCHEDULED', 2); // #2187: Scheduling queue removed.
define('STATUS_PUBLISHED', 3);
define('STATUS_DECLINED', 4);