Editorial Activity dashboard issues in OJS 3.4.0.8

Software Support
1

Describe the issue or problem
One of our editors reported an issue with some of the figures in the Editorial Activity dashboard, specifically the percentages in the Total > Submissions Declined and the Total > Rejection Rate columns. See screenshot below for details.

Total > Submissions Declined:
The percentage in the Total > Submissions Declined field does not seem to reflect the editorial activity correctly. According to the dashboard,330 items were submitted and 100 were accepted, so how can there be 317 articles declined? The 214 “other” submissions were all published and none declined, so where did the 317 articles declined come from?

Total > Rejection Rate:
It’s been my understanding that the Acceptance Rate and the Rejection Rate do not include the journal’s 214 imported submissions. If that is the case, the Total > Acceptance Rate of ~30% seems to be correct (330 total non-imported submissions received, 100 of which were accepted), but the Total > Rejection Rate of 96% seems to be incorrect. I would have expected a Total Rejection rate of something nearer 70%, not 96%. Is this an error, and, if it is not, where is that figure coming from?

Here is the screenshot of the Editorial Workflow dashboard, captured today:

JISEditorialWorkflowStats_20250430
JISEditorialWorkflowStats_20250430647×514 59.5 KB

Steps I took leading up to the issue
For example:

  1. Log in to OJS as a Journal Editor.
  2. Click on Statistics > Editorial Activity
  3. Review the statistics displayed

What application are you using?
OJS 3.4.0.8

Additional information
There are two other forum posts concerning OJS 3.4.x and the Editorial Activity dashboard that might or might not apply to this situation:
Editorial activity interpretation
Editorial activity statistics look strange - OJS 3.4.0.4

2

Hi @mreedks,

Curious if you have articles imported via QuickSubmit or NativeXML and if this might be affecting this?
This looks to me like it is possibly this bug: Improve Editorial Activity calculations · Issue #9813 · pkp/pkp-lib · GitHub
However, this should’ve been fixed as of 3.4.0-6, and if you’re running a later version, it should be accounted for. @bozana - any ideas?

-Roger
PKP Team

4

@rcgillis It was 7 years ago that the journal came over to us from Human Kinetics, so I don’t remember whether the 214 articles were added through Quick Submit or through an XML import. I checked the articles and the Activity Logs are blank, so I suspect that it was done by XML. We were using OJS 3.1.1.2 at the time, if that makes any difference.

This did seem like a variant on the issues that I reported in Editorial activity statistics look strange - OJS 3.4.0.4 - #34 by bozana and it had been my understanding from that post that the fix had been applied to 3.4.0.6 and beyond, but we’re still seeing odd calculations in 3.4.0.8, so I wanted to report it.

5

@rcgillis Upon further investigation, it doesn’t look like Quick Submit vs. NativeXML import makes a difference in this case. Another much smaller journal that was added at around the same time is also showing the calculation problem, and those 40 back articles were Quick Submitted. See screenshot below for details.

image
image998×652 20.1 KB

I checked more of our other journals, including ones that started on OJS and didn’t have any back content, and there is something wrong with the calculations on each.

Sorry to be the bearer of bad news!

6

Hi @mreedks,

Hmmm… When I look into the code everything seems fine to me… So maybe we can try to understand the first case you have mentioned:
Maybe you can try to see if that all is true using the SQL:
Run this to see the result for initially declined submissions (exchanging ??? with that journal ID):

select DISTINCT s.submission_id, pi.publication_id, ed.decision 
from `submissions` as `s` 
left join `publications` as `pi` on `pi`.`publication_id` = (
select `pi2`.`publication_id` 
from `publications` as `pi2` 
where `pi2`.`submission_id` = `s`.`submission_id` and `pi2`.`status` = 3 
order by `pi2`.`date_published` asc limit 1
) 
left join `edit_decisions` as `ed` on `s`.`submission_id` = `ed`.`submission_id` 
where `s`.`context_id` in (???) and `s`.`submission_progress` = '' and (`pi`.`date_published` is null or CAST(s.date_submitted AS DATE) <= pi.date_published) and `ed`.`decision` in (8) and `s`.`status` = 4;

And run this to see the result for after review declined submissions (exchanging ??? with that journal ID):

select DISTINCT s.submission_id, pi.publication_id, ed.decision 
from `submissions` as `s` 
left join `publications` as `pi` on `pi`.`publication_id` = (
select `pi2`.`publication_id` 
from `publications` as `pi2` 
where `pi2`.`submission_id` = `s`.`submission_id` and `pi2`.`status` = 3 
order by `pi2`.`date_published` asc limit 1
) 
left join `edit_decisions` as `ed` on `s`.`submission_id` = `ed`.`submission_id` 
where `s`.`context_id` in (???) and `s`.`submission_progress` = '' and (`pi`.`date_published` is null or CAST(s.date_submitted AS DATE) <= pi.date_published) and `ed`.`decision` in (6) and `s`.`status` = 4;

This way you can see what submissions are considered declined on the editorial activity dashboard and double check if it is correct.
I would be also interested to know what you see in the column publication_id in the results. – It could be that already published submission is later declined…

Thanks!
Bozana

7

Thank you, @bozana. We’ll run the scripts and get back to you.

8

@bozana Both queries resulted in publication_id = null:

±--------------±---------------±---------+
| submission_id | publication_id | decision |
±--------------±---------------±---------+
| 11579 | NULL | 8 |
| 11674 | NULL | 8 |
| 11677 | NULL | 8 |
| 11727 | NULL | 8 |
| 11746 | NULL | 8 |
| 11757 | NULL | 8 |
| 11882 | NULL | 8 |
| 12047 | NULL | 8 |
| 12052 | NULL | 8 |
| 12055 | NULL | 8 |
| 13216 | NULL | 8 |
| 13273 | NULL | 8 |
| 13339 | NULL | 8 |
| 13370 | NULL | 8 |
| 13380 | NULL | 8 |
| 13383 | NULL | 8 |
| 13414 | NULL | 8 |
| 13442 | NULL | 8 |
| 13463 | NULL | 8 |
| 13465 | NULL | 8 |
| 13473 | NULL | 8 |
| 13482 | NULL | 8 |
| 13560 | NULL | 8 |
| 13631 | NULL | 8 |
| 13643 | NULL | 8 |
| 13648 | NULL | 8 |
| 13678 | NULL | 8 |
| 13682 | NULL | 8 |
| 13685 | NULL | 8 |
| 13717 | NULL | 8 |
| 13732 | NULL | 8 |
| 13733 | NULL | 8 |
| 13790 | NULL | 8 |
| 13796 | NULL | 8 |
| 13823 | NULL | 8 |
| 13860 | NULL | 8 |
| 13879 | NULL | 8 |
| 14546 | NULL | 8 |
| 14550 | NULL | 8 |
| 14552 | NULL | 8 |
| 14598 | NULL | 8 |
| 14624 | NULL | 8 |
| 14636 | NULL | 8 |
| 14650 | NULL | 8 |
| 14668 | NULL | 8 |
| 14687 | NULL | 8 |
| 14750 | NULL | 8 |
| 14825 | NULL | 8 |
| 14833 | NULL | 8 |
| 14834 | NULL | 8 |
| 15000 | NULL | 8 |
| 15088 | NULL | 8 |
| 15165 | NULL | 8 |
| 15240 | NULL | 8 |
| 15242 | NULL | 8 |
| 15279 | NULL | 8 |
| 15280 | NULL | 8 |
| 15384 | NULL | 8 |
| 15395 | NULL | 8 |
| 15401 | NULL | 8 |
| 15412 | NULL | 8 |
| 15420 | NULL | 8 |
| 15427 | NULL | 8 |
| 15498 | NULL | 8 |
| 15510 | NULL | 8 |
| 15580 | NULL | 8 |
| 15582 | NULL | 8 |
| 15605 | NULL | 8 |
| 15664 | NULL | 8 |
| 15685 | NULL | 8 |
| 15736 | NULL | 8 |
| 15800 | NULL | 8 |
| 15810 | NULL | 8 |
| 15815 | NULL | 8 |
| 15865 | NULL | 8 |
| 15867 | NULL | 8 |
| 15877 | NULL | 8 |
| 15887 | NULL | 8 |
| 15896 | NULL | 8 |
| 15898 | NULL | 8 |
| 15907 | NULL | 8 |
| 15926 | NULL | 8 |
| 15959 | NULL | 8 |
| 16001 | NULL | 8 |
| 16163 | NULL | 8 |
| 16282 | NULL | 8 |
| 16287 | NULL | 8 |
| 16344 | NULL | 8 |
| 16391 | NULL | 8 |
| 16394 | NULL | 8 |
| 16455 | NULL | 8 |
| 16495 | NULL | 8 |
| 16586 | NULL | 8 |
| 16761 | NULL | 8 |
| 16954 | NULL | 8 |
| 17004 | NULL | 8 |
| 17007 | NULL | 8 |
| 17008 | NULL | 8 |
| 17034 | NULL | 8 |
| 17039 | NULL | 8 |
| 17601 | NULL | 8 |
| 17907 | NULL | 8 |
| 17925 | NULL | 8 |
| 17934 | NULL | 8 |
| 18103 | NULL | 8 |
| 18215 | NULL | 8 |
| 18220 | NULL | 8 |
| 18233 | NULL | 8 |
| 18234 | NULL | 8 |
| 18235 | NULL | 8 |
| 18279 | NULL | 8 |
| 18346 | NULL | 8 |
| 18390 | NULL | 8 |
| 18425 | NULL | 8 |
| 18483 | NULL | 8 |
| 18484 | NULL | 8 |
| 18536 | NULL | 8 |
| 18537 | NULL | 8 |
| 18539 | NULL | 8 |
| 18576 | NULL | 8 |
| 18586 | NULL | 8 |
| 18620 | NULL | 8 |
| 18623 | NULL | 8 |
| 18737 | NULL | 8 |
| 18749 | NULL | 8 |
| 18954 | NULL | 8 |
| 18957 | NULL | 8 |
| 18974 | NULL | 8 |
| 18980 | NULL | 8 |
| 19497 | NULL | 8 |
| 19504 | NULL | 8 |
| 19519 | NULL | 8 |
| 19539 | NULL | 8 |
| 19666 | NULL | 8 |
| 19740 | NULL | 8 |
| 20033 | NULL | 8 |
| 20038 | NULL | 8 |
| 20272 | NULL | 8 |
| 20341 | NULL | 8 |
| 20353 | NULL | 8 |
| 20358 | NULL | 8 |
| 20359 | NULL | 8 |
| 20488 | NULL | 8 |
| 20578 | NULL | 8 |
| 20946 | NULL | 8 |
| 20951 | NULL | 8 |
| 20955 | NULL | 8 |
| 20968 | NULL | 8 |
| 21019 | NULL | 8 |
| 21044 | NULL | 8 |
| 21192 | NULL | 8 |
| 21213 | NULL | 8 |
| 21247 | NULL | 8 |
| 21267 | NULL | 8 |
| 21291 | NULL | 8 |
| 21306 | NULL | 8 |
| 21318 | NULL | 8 |
| 21402 | NULL | 8 |
| 21447 | NULL | 8 |
| 21449 | NULL | 8 |
| 21451 | NULL | 8 |
| 21475 | NULL | 8 |
| 21476 | NULL | 8 |
| 21519 | NULL | 8 |
| 21538 | NULL | 8 |
| 21639 | NULL | 8 |
| 21668 | NULL | 8 |
| 21677 | NULL | 8 |
| 21835 | NULL | 8 |
| 21858 | NULL | 8 |
| 21869 | NULL | 8 |
| 21906 | NULL | 8 |
| 22151 | NULL | 8 |
| 22183 | NULL | 8 |
| 22184 | NULL | 8 |
| 22235 | NULL | 8 |
| 22237 | NULL | 8 |
| 22334 | NULL | 8 |
| 22365 | NULL | 8 |
| 22393 | NULL | 8 |
| 22500 | NULL | 8 |
| 22901 | NULL | 8 |
| 22974 | NULL | 8 |
| 23029 | NULL | 8 |
| 23051 | NULL | 8 |
| 23204 | NULL | 8 |
| 23273 | NULL | 8 |
| 23308 | NULL | 8 |
| 23316 | NULL | 8 |
| 23336 | NULL | 8 |
| 23667 | NULL | 8 |
| 23788 | NULL | 8 |
| 23820 | NULL | 8 |
| 23842 | NULL | 8 |
| 23886 | NULL | 8 |
±--------------±---------------±---------+
195 rows in set (0.047 sec)

±--------------±---------------±---------+
| submission_id | publication_id | decision |
±--------------±---------------±---------+
| 11579 | NULL | 6 |
| 11674 | NULL | 6 |
| 11677 | NULL | 6 |
| 11727 | NULL | 6 |
| 11746 | NULL | 6 |
| 11757 | NULL | 6 |
| 12047 | NULL | 6 |
| 12055 | NULL | 6 |
| 13216 | NULL | 6 |
| 13273 | NULL | 6 |
| 13339 | NULL | 6 |
| 13370 | NULL | 6 |
| 13380 | NULL | 6 |
| 13383 | NULL | 6 |
| 13465 | NULL | 6 |
| 13473 | NULL | 6 |
| 13482 | NULL | 6 |
| 13560 | NULL | 6 |
| 13631 | NULL | 6 |
| 13648 | NULL | 6 |
| 13678 | NULL | 6 |
| 13682 | NULL | 6 |
| 13685 | NULL | 6 |
| 13732 | NULL | 6 |
| 13790 | NULL | 6 |
| 13823 | NULL | 6 |
| 13860 | NULL | 6 |
| 13879 | NULL | 6 |
| 14546 | NULL | 6 |
| 14550 | NULL | 6 |
| 14552 | NULL | 6 |
| 14624 | NULL | 6 |
| 14636 | NULL | 6 |
| 14687 | NULL | 6 |
| 14750 | NULL | 6 |
| 14825 | NULL | 6 |
| 14833 | NULL | 6 |
| 15165 | NULL | 6 |
| 15240 | NULL | 6 |
| 15242 | NULL | 6 |
| 15280 | NULL | 6 |
| 15401 | NULL | 6 |
| 15412 | NULL | 6 |
| 15420 | NULL | 6 |
| 15427 | NULL | 6 |
| 15498 | NULL | 6 |
| 15510 | NULL | 6 |
| 15580 | NULL | 6 |
| 15664 | NULL | 6 |
| 15685 | NULL | 6 |
| 15736 | NULL | 6 |
| 15800 | NULL | 6 |
| 15810 | NULL | 6 |
| 15865 | NULL | 6 |
| 15867 | NULL | 6 |
| 15877 | NULL | 6 |
| 15896 | NULL | 6 |
| 15898 | NULL | 6 |
| 15959 | NULL | 6 |
| 16163 | NULL | 6 |
| 16282 | NULL | 6 |
| 16287 | NULL | 6 |
| 16344 | NULL | 6 |
| 16391 | NULL | 6 |
| 16394 | NULL | 6 |
| 16455 | NULL | 6 |
| 16495 | NULL | 6 |
| 16586 | NULL | 6 |
| 16761 | NULL | 6 |
| 16954 | NULL | 6 |
| 17004 | NULL | 6 |
| 17008 | NULL | 6 |
| 17039 | NULL | 6 |
| 17601 | NULL | 6 |
| 17907 | NULL | 6 |
| 17925 | NULL | 6 |
| 17934 | NULL | 6 |
| 18215 | NULL | 6 |
| 18220 | NULL | 6 |
| 18233 | NULL | 6 |
| 18234 | NULL | 6 |
| 18425 | NULL | 6 |
| 18483 | NULL | 6 |
| 18537 | NULL | 6 |
| 18539 | NULL | 6 |
| 18576 | NULL | 6 |
| 18623 | NULL | 6 |
| 18737 | NULL | 6 |
| 18749 | NULL | 6 |
| 18954 | NULL | 6 |
| 19497 | NULL | 6 |
| 19539 | NULL | 6 |
| 19666 | NULL | 6 |
| 20033 | NULL | 6 |
| 20341 | NULL | 6 |
| 20353 | NULL | 6 |
| 20358 | NULL | 6 |
| 20578 | NULL | 6 |
| 20955 | NULL | 6 |
| 20968 | NULL | 6 |
| 21192 | NULL | 6 |
| 21306 | NULL | 6 |
| 21402 | NULL | 6 |
| 21449 | NULL | 6 |
| 21451 | NULL | 6 |
| 21538 | NULL | 6 |
| 21639 | NULL | 6 |
| 21677 | NULL | 6 |
| 21832 | NULL | 6 |
| 22105 | NULL | 6 |
| 22160 | NULL | 6 |
| 22243 | NULL | 6 |
| 22317 | NULL | 6 |
| 22345 | NULL | 6 |
| 22488 | NULL | 6 |
| 22814 | NULL | 6 |
| 22999 | NULL | 6 |
| 23217 | NULL | 6 |
| 23323 | NULL | 6 |
| 23326 | NULL | 6 |
| 23351 | NULL | 6 |
| 23682 | NULL | 6 |
| 23795 | NULL | 6 |
±--------------±---------------±---------+
123 rows in set (0.050 sec)

9

For the smaller journal I referenced above, the first query showed one row and the second query was empty set:

±--------------±---------------±---------+
| submission_id | publication_id | decision |
±--------------±---------------±---------+
| 14666 | NULL | 8 |
±--------------±---------------±---------+
1 row in set (0.010 sec)

Empty set (0.001 sec)

10

Hi @mreedks

Those all looks like declined and not published. Could you take a look at few of those submissions to see what actually happened there, what are those submissions and how are they declined? – to find an answer on your question how come there are so many declined…

Best,
Bozana

11

@bozana, I double-checked a random sampling of 11 articles from the two queries done for the larger journal and noted below whether the rejection happened before or after peer review. None appear to have been published, then declined.

Please note that the relative percentages are probably not representative.

(Note: It would be really nice if Archived items could be filtered by whether they were declined!)

| 11674 | NULL | 6 | - Declined after peer review

| 12052 | NULL | 8 | - Declined before peer review (Desk reject)

| 13380 | NULL | 6 | - Declined after peer review

| 13442 | NULL | 8 | - Declined before peer review (Desk reject)

| 15580 | NULL | 6 | - Declined after peer review

| 16586 | NULL | 6 | - Declined after peer review

| 20358 | NULL | 6 | - Declined after peer review

| 21832 | NULL | 6 | - Declined after peer review

| 23667 | NULL | 8 | - Declined before peer review (Desk reject)

| 23795 | NULL | 6 | - Declined after peer review

| 23886 | NULL | 8 | - Declined before peer review (Desk reject)

12

Hi @mreedks

So the number of declined submission seems to be correctly displayed on the editorial activity dashboard, right?

I will then now take a look into the percentages are correctly i.e. how exactly are they function… and will come back to you…

Best,
Bozana

13

@bozana Today, I verified that there were 210 declined submissions in the UI’s Submissions > Archived pages. The Editorial Activity dashboard > Submissions Declined is showing 319 items declined . So, there’s a discrepancy of 109 declined items between two sources, so one of them must be incorrect. (Note: There have been two more items declined since I took that first screenshot and we ran the queries)

I did an analysis of the responses to the two queries that you asked us to run and that I reported above: Editorial Activity dashboard issues in OJS 3.4.0.8 - #8 by mreedks. The two queries returned 318 responses; 108 submissions appeared in the results of both queries, 87 appeared only in the results of the first query, and 15 appeared only in the results of the second query. If I can, I’ll attach my spreadsheet showing which submissions fall into each category.

What other information would be helpful for you to troubleshoot this?

14

Hi @mreedks,

It seems that for those 108 submissions there are both decisions (6 and 8) in the edit_decisions table. That’s why they are listed under both numbers, initially and after review declined.
This seems to be the difference in numbers you see under submission > archive and on editorial activity dashboard.

Best,
Bozana

15

@Bozana, thank you! It would be exceptionally rare that a submission would be Declined in the Submission stage, then to have the editor go to the Submissions > Archived list, open the submission, reverse the decision to Decline and then pass the submission on to peer review.

Would a request for “Revise and Resubmit” after the first round of peer review show up in the results of both queries if the submission was declined after submitting those revisions?

16

Hi @mreedks,

The queries above (that are also used for the numbers on the editorial activity dashboard) only consider decision = 8 (initial decline) and 6 (decline after a review).

Only one thing leading to the wrong numbers in that table I can think of is, that the upgrade script APP\migration\upgrade\v3_4_0\I7725_DecisionConstantsUpdate failed/did not run correctly when upgrading to 3.4. So that those 108 submissions are only declined after a review:
In 3.3 the number 8 was used for the external review decision (= a decision to send a submission to the external review stage). In 3.4 it should be number 3. It could be that for some reason this was not upgraded correctly.
In 3.3 the number 4 was used for decline after a review which seems to be correctly updated to 6 during the upgrade. Number 6 did not exist in 3.3.
(It is a little bit strange to me that for some numbers upgrade was correct and for the others it was not… :thinking: )

Could you maybe take a look at a few of those 108 submissions that have both decisions in the in the edit_decisions table and figure out what actually happened with those submissions?
Maybe even to somehow check all submissions that have edit_decision=8 and date_decided < upgrade date to 3.4 – if they are really initially declined…
Maybe also to double check/investigate other decisions in that table for those submissions in chronological order, if they would make sense…

Best,
Bozana

17

Thank you, @bozana! I think that you set us on the right track to troubleshoot this.

We spent some time looking at the table and as far as we can tell, external review decisions to Decline that were number 8 never got changed over to 3 when we upgraded to 3.4. So far, the other numbers seem to have upgraded successfully, but we’re still looking and spot-checking the numbers in the table against what actually happened in the workflow for those 108 submissions, so we’ll see what we find out.

We’re also trying to determine why some numbers upgraded and others didn’t. We’ll come back if we have more data or more questions.

– Marianne

18

Hi @mreedks

Maybe you still have the 3.3 backup and could compare the table edit_decisions.

Also, please take a look what is the stage_id for those not updated entries, where decision = 8. Our upgrade script only considers the stage_id = 3. So could it maybe be that the stage_id for those entries is a different number?

Thanks!
Bozana

19

@bozana We looked at the edit_decision table and found that all items with a decision=8 have a stage ID of 1, not 3.

20

Ah sorry @mreedks, my bad, it is stage_id = 1 not 3 – I was looking wrongly in the code… So that should be fine… S. ojs/classes/migration/upgrade/v3_4_0/I7725_DecisionConstantsUpdate.php at stable-3_4_0 · pkp/ojs · GitHub

21

revisiting this topic to try to fix / understand why the totals for ‘submission accepted’ and ‘submission declined’ are incorrect– or maybe it’s just the ‘submission declined’ that’s incorrect–

here is what the gui page looks like

brave_screenshot_journals.ku.edu
brave_screenshot_journals.ku.edu2068×1478 175 KB

and using the previously suggested queries here is what the database is returning for some of these queries:

MariaDB [ojs]> select count(*) from submissions where context_

id = 34;

±---------+

| count(*) |

±---------+

| 572 |

±---------+

1 row in set (0.002 sec)

572 - 216 (other submissions) = 356 (submissions received) – seems fine

but the 115 (submissions accepted) and 332 (submissions declined) – seems incorrect and maybe it’s just the submissions declined that has some bad data but not sure how to figure out how to fix this / figure out what went wrong

select DISTINCT s.submission_id, pi.publication_id, ed.decision

from `submissions` as `s`

left join `publications` as `pi` on `pi`.`publication_id` = (

select `pi2`.`publication_id`

from `publications` as `pi2`

where `pi2`.`submission_id` = `s`.`submission_id` and `pi2`.`status` = 3

order by `pi2`.`date_published` asc limit 1

)

left join `edit_decisions` as `ed` on `s`.`submission_id` = `ed`.`submission_id`

where `s`.`context_id` in (34) and `s`.`submission_progress` = ‘’ and (`pi`.`date_published` is null or CAST(s.date_submitted AS DATE) <= pi.date_published) and `ed`.`decision` in (8) and `s`.`status` = 4;

MariaDB [ojs]> select DISTINCT s.submission_id, pi.publication_id, ed.decision

-> from `submissions` as `s`

-> left join `publications` as `pi` on `pi`.`publication_id` = (

-> select `pi2`.`publication_id`

-> from `publications` as `pi2`

-> where `pi2`.`submission_id` = `s`.`submission_id` and `pi2`.`status` = 3

-> order by `pi2`.`date_published` asc limit 1

-> )

-> left join `edit_decisions` as `ed` on `s`.`submission_id` = `ed`.`submission_id`

-> where `s`.`context_id` in (34) and `s`.`submission_progress` = ‘’ and (`pi`.`date_published` is null or CAST(s.date_submitted AS DATE) <= pi.date_published) and `ed`.`decision` in (8) and `s`.`status` = 4;

±--------------±---------------±---------+

| submission_id | publication_id | decision |

±--------------±---------------±---------+

| 11579 | NULL | 8 |

| 11674 | NULL | 8 |

| 11677 | NULL | 8 |

| 11727 | NULL | 8 |

| 11746 | NULL | 8 |

| 11757 | NULL | 8 |

| 11882 | NULL | 8 |

| 12047 | NULL | 8 |

| 12052 | NULL | 8 |

| 12055 | NULL | 8 |

| 13216 | NULL | 8 |

| 13273 | NULL | 8 |

| 13339 | NULL | 8 |

| 13370 | NULL | 8 |

| 13380 | NULL | 8 |

| 13383 | NULL | 8 |

| 13414 | NULL | 8 |

| 13442 | NULL | 8 |

| 13463 | NULL | 8 |

| 13465 | NULL | 8 |

| 13473 | NULL | 8 |

| 13482 | NULL | 8 |

| 13560 | NULL | 8 |

| 13631 | NULL | 8 |

| 13643 | NULL | 8 |

| 13648 | NULL | 8 |

| 13678 | NULL | 8 |

| 13682 | NULL | 8 |

| 13685 | NULL | 8 |

| 13717 | NULL | 8 |

| 13732 | NULL | 8 |

| 13733 | NULL | 8 |

| 13790 | NULL | 8 |

| 13796 | NULL | 8 |

| 13823 | NULL | 8 |

| 13860 | NULL | 8 |

| 13879 | NULL | 8 |

| 14546 | NULL | 8 |

| 14550 | NULL | 8 |

| 14552 | NULL | 8 |

| 14598 | NULL | 8 |

| 14624 | NULL | 8 |

| 14636 | NULL | 8 |

| 14650 | NULL | 8 |

| 14668 | NULL | 8 |

| 14687 | NULL | 8 |

| 14750 | NULL | 8 |

| 14825 | NULL | 8 |

| 14833 | NULL | 8 |

| 14834 | NULL | 8 |

| 15000 | NULL | 8 |

| 15088 | NULL | 8 |

| 15165 | NULL | 8 |

| 15240 | NULL | 8 |

| 15242 | NULL | 8 |

| 15279 | NULL | 8 |

| 15280 | NULL | 8 |

| 15384 | NULL | 8 |

| 15395 | NULL | 8 |

| 15401 | NULL | 8 |

| 15412 | NULL | 8 |

| 15420 | NULL | 8 |

| 15427 | NULL | 8 |

| 15498 | NULL | 8 |

| 15510 | NULL | 8 |

| 15580 | NULL | 8 |

| 15582 | NULL | 8 |

| 15605 | NULL | 8 |

| 15664 | NULL | 8 |

| 15685 | NULL | 8 |

| 15736 | NULL | 8 |

| 15800 | NULL | 8 |

| 15810 | NULL | 8 |

| 15815 | NULL | 8 |

| 15865 | NULL | 8 |

| 15867 | NULL | 8 |

| 15877 | NULL | 8 |

| 15887 | NULL | 8 |

| 15896 | NULL | 8 |

| 15898 | NULL | 8 |

| 15907 | NULL | 8 |

| 15926 | NULL | 8 |

| 15959 | NULL | 8 |

| 16001 | NULL | 8 |

| 16163 | NULL | 8 |

| 16282 | NULL | 8 |

| 16287 | NULL | 8 |

| 16344 | NULL | 8 |

| 16391 | NULL | 8 |

| 16394 | NULL | 8 |

| 16455 | NULL | 8 |

| 16495 | NULL | 8 |

| 16586 | NULL | 8 |

| 16761 | NULL | 8 |

| 16954 | NULL | 8 |

| 17004 | NULL | 8 |

| 17007 | NULL | 8 |

| 17008 | NULL | 8 |

| 17034 | NULL | 8 |

| 17039 | NULL | 8 |

| 17601 | NULL | 8 |

| 17907 | NULL | 8 |

| 17925 | NULL | 8 |

| 17934 | NULL | 8 |

| 18103 | NULL | 8 |

| 18215 | NULL | 8 |

| 18220 | NULL | 8 |

| 18233 | NULL | 8 |

| 18234 | NULL | 8 |

| 18235 | NULL | 8 |

| 18279 | NULL | 8 |

| 18346 | NULL | 8 |

| 18390 | NULL | 8 |

| 18425 | NULL | 8 |

| 18483 | NULL | 8 |

| 18484 | NULL | 8 |

| 18536 | NULL | 8 |

| 18537 | NULL | 8 |

| 18539 | NULL | 8 |

| 18576 | NULL | 8 |

| 18586 | NULL | 8 |

| 18620 | NULL | 8 |

| 18623 | NULL | 8 |

| 18737 | NULL | 8 |

| 18749 | NULL | 8 |

| 18954 | NULL | 8 |

| 18957 | NULL | 8 |

| 18974 | NULL | 8 |

| 18980 | NULL | 8 |

| 19497 | NULL | 8 |

| 19504 | NULL | 8 |

| 19519 | NULL | 8 |

| 19539 | NULL | 8 |

| 19666 | NULL | 8 |

| 19740 | NULL | 8 |

| 20033 | NULL | 8 |

| 20038 | NULL | 8 |

| 20272 | NULL | 8 |

| 20341 | NULL | 8 |

| 20353 | NULL | 8 |

| 20358 | NULL | 8 |

| 20359 | NULL | 8 |

| 20488 | NULL | 8 |

| 20578 | NULL | 8 |

| 20946 | NULL | 8 |

| 20951 | NULL | 8 |

| 20955 | NULL | 8 |

| 20968 | NULL | 8 |

| 21019 | NULL | 8 |

| 21044 | NULL | 8 |

| 21192 | NULL | 8 |

| 21213 | NULL | 8 |

| 21247 | NULL | 8 |

| 21267 | NULL | 8 |

| 21291 | NULL | 8 |

| 21306 | NULL | 8 |

| 21318 | NULL | 8 |

| 21402 | NULL | 8 |

| 21447 | NULL | 8 |

| 21449 | NULL | 8 |

| 21451 | NULL | 8 |

| 21475 | NULL | 8 |

| 21476 | NULL | 8 |

| 21519 | NULL | 8 |

| 21538 | NULL | 8 |

| 21639 | NULL | 8 |

| 21668 | NULL | 8 |

| 21677 | NULL | 8 |

| 21835 | NULL | 8 |

| 21858 | NULL | 8 |

| 21869 | NULL | 8 |

| 21906 | NULL | 8 |

| 22151 | NULL | 8 |

| 22183 | NULL | 8 |

| 22184 | NULL | 8 |

| 22235 | NULL | 8 |

| 22237 | NULL | 8 |

| 22334 | NULL | 8 |

| 22365 | NULL | 8 |

| 22393 | NULL | 8 |

| 22500 | NULL | 8 |

| 22901 | NULL | 8 |

| 22974 | NULL | 8 |

| 23029 | NULL | 8 |

| 23051 | NULL | 8 |

| 23204 | NULL | 8 |

| 23273 | NULL | 8 |

| 23308 | NULL | 8 |

| 23316 | NULL | 8 |

| 23336 | NULL | 8 |

| 23667 | NULL | 8 |

| 23788 | NULL | 8 |

| 23820 | NULL | 8 |

| 23842 | NULL | 8 |

| 23886 | NULL | 8 |

| 23926 | NULL | 8 |

| 23975 | NULL | 8 |

| 24008 | NULL | 8 |

| 24140 | NULL | 8 |

| 24195 | NULL | 8 |

| 24338 | NULL | 8 |

| 24347 | NULL | 8 |

| 24359 | NULL | 8 |

| 24361 | NULL | 8 |

±--------------±---------------±---------+

204 rows in set (0.146 sec)

and

select DISTINCT s.submission_id, pi.publication_id, ed.decision

from `submissions` as `s`

left join `publications` as `pi` on `pi`.`publication_id` = (

select `pi2`.`publication_id`

from `publications` as `pi2`

where `pi2`.`submission_id` = `s`.`submission_id` and `pi2`.`status` = 3

order by `pi2`.`date_published` asc limit 1

)

left join `edit_decisions` as `ed` on `s`.`submission_id` = `ed`.`submission_id`

where `s`.`context_id` in (34) and `s`.`submission_progress` = ‘’ and (`pi`.`date_published` is null or CAST(s.date_submitted AS DATE) <= pi.date_published) and `ed`.`decision` in (6) and `s`.`status` = 4;

MariaDB [ojs]> select DISTINCT s.submission_id, pi.publication_id, ed.decision

-> from `submissions` as `s`

-> left join `publications` as `pi` on `pi`.`publication_id` = (

-> select `pi2`.`publication_id`

-> from `publications` as `pi2`

-> where `pi2`.`submission_id` = `s`.`submission_id` and `pi2`.`status` = 3

-> order by `pi2`.`date_published` asc limit 1

-> )

-> left join `edit_decisions` as `ed` on `s`.`submission_id` = `ed`.`submission_id`

-> where `s`.`context_id` in (34) and `s`.`submission_progress` = ‘’ and (`pi`.`date_published` is null or CAST(s.date_submitted AS DATE) <= pi.date_published) and `ed`.`decision` in (6) and `s`.`status` = 4;

±--------------±---------------±---------+

| submission_id | publication_id | decision |

±--------------±---------------±---------+

| 11579 | NULL | 6 |

| 11674 | NULL | 6 |

| 11677 | NULL | 6 |

| 11727 | NULL | 6 |

| 11746 | NULL | 6 |

| 11757 | NULL | 6 |

| 12047 | NULL | 6 |

| 12055 | NULL | 6 |

| 13216 | NULL | 6 |

| 13273 | NULL | 6 |

| 13339 | NULL | 6 |

| 13370 | NULL | 6 |

| 13380 | NULL | 6 |

| 13383 | NULL | 6 |

| 13465 | NULL | 6 |

| 13473 | NULL | 6 |

| 13482 | NULL | 6 |

| 13560 | NULL | 6 |

| 13631 | NULL | 6 |

| 13648 | NULL | 6 |

| 13678 | NULL | 6 |

| 13682 | NULL | 6 |

| 13685 | NULL | 6 |

| 13732 | NULL | 6 |

| 13790 | NULL | 6 |

| 13823 | NULL | 6 |

| 13860 | NULL | 6 |

| 13879 | NULL | 6 |

| 14546 | NULL | 6 |

| 14550 | NULL | 6 |

| 14552 | NULL | 6 |

| 14624 | NULL | 6 |

| 14636 | NULL | 6 |

| 14687 | NULL | 6 |

| 14750 | NULL | 6 |

| 14825 | NULL | 6 |

| 14833 | NULL | 6 |

| 15165 | NULL | 6 |

| 15240 | NULL | 6 |

| 15242 | NULL | 6 |

| 15280 | NULL | 6 |

| 15401 | NULL | 6 |

| 15412 | NULL | 6 |

| 15420 | NULL | 6 |

| 15427 | NULL | 6 |

| 15498 | NULL | 6 |

| 15510 | NULL | 6 |

| 15580 | NULL | 6 |

| 15664 | NULL | 6 |

| 15685 | NULL | 6 |

| 15736 | NULL | 6 |

| 15800 | NULL | 6 |

| 15810 | NULL | 6 |

| 15865 | NULL | 6 |

| 15867 | NULL | 6 |

| 15877 | NULL | 6 |

| 15896 | NULL | 6 |

| 15898 | NULL | 6 |

| 15959 | NULL | 6 |

| 16163 | NULL | 6 |

| 16282 | NULL | 6 |

| 16287 | NULL | 6 |

| 16344 | NULL | 6 |

| 16391 | NULL | 6 |

| 16394 | NULL | 6 |

| 16455 | NULL | 6 |

| 16495 | NULL | 6 |

| 16586 | NULL | 6 |

| 16761 | NULL | 6 |

| 16954 | NULL | 6 |

| 17004 | NULL | 6 |

| 17008 | NULL | 6 |

| 17039 | NULL | 6 |

| 17601 | NULL | 6 |

| 17907 | NULL | 6 |

| 17925 | NULL | 6 |

| 17934 | NULL | 6 |

| 18215 | NULL | 6 |

| 18220 | NULL | 6 |

| 18233 | NULL | 6 |

| 18234 | NULL | 6 |

| 18425 | NULL | 6 |

| 18483 | NULL | 6 |

| 18537 | NULL | 6 |

| 18539 | NULL | 6 |

| 18576 | NULL | 6 |

| 18623 | NULL | 6 |

| 18737 | NULL | 6 |

| 18749 | NULL | 6 |

| 18954 | NULL | 6 |

| 19497 | NULL | 6 |

| 19539 | NULL | 6 |

| 19666 | NULL | 6 |

| 20033 | NULL | 6 |

| 20341 | NULL | 6 |

| 20353 | NULL | 6 |

| 20358 | NULL | 6 |

| 20578 | NULL | 6 |

| 20955 | NULL | 6 |

| 20968 | NULL | 6 |

| 21192 | NULL | 6 |

| 21306 | NULL | 6 |

| 21402 | NULL | 6 |

| 21449 | NULL | 6 |

| 21451 | NULL | 6 |

| 21538 | NULL | 6 |

| 21639 | NULL | 6 |

| 21677 | NULL | 6 |

| 21832 | NULL | 6 |

| 22105 | NULL | 6 |

| 22160 | NULL | 6 |

| 22243 | NULL | 6 |

| 22317 | NULL | 6 |

| 22345 | NULL | 6 |

| 22488 | NULL | 6 |

| 22814 | NULL | 6 |

| 22999 | NULL | 6 |

| 23217 | NULL | 6 |

| 23323 | NULL | 6 |

| 23326 | NULL | 6 |

| 23351 | NULL | 6 |

| 23408 | NULL | 6 |

| 23682 | NULL | 6 |

| 23795 | NULL | 6 |

| 23809 | NULL | 6 |

| 24017 | NULL | 6 |

| 24082 | NULL | 6 |

| 24294 | NULL | 6 |

±--------------±---------------±---------+

128 rows in set (0.215 sec)

and then so using those tables you suggested to look at i compared two at random (one that is published and one that was declined)–

MariaDB [ojs]> select * from submissions where context_id = 34 and submission_id in (22288, 22317);

±--------------±-----------±--------------------±--------------------±-------±---------±-----------------------±--------------------±----------±-------±--------------------+

| submission_id | context_id | date_submitted | last_modified | status | stage_id | current_publication_id | date_last_activity | work_type | locale | submission_progress |

±--------------±-----------±--------------------±--------------------±-------±---------±-----------------------±--------------------±----------±-------±--------------------+

| 22288 | 34 | 2024-04-18 14:57:22 | 2024-04-18 14:57:22 | 3 | 5 | 24204 | 2025-05-22 21:58:17 | 0 | en | |

| 22317 | 34 | 2024-04-29 12:48:18 | 2024-04-29 12:48:18 | 4 | 3 | 24233 | 2024-05-18 22:22:46 | 0 | en | |

±--------------±-----------±--------------------±--------------------±-------±---------±-----------------------±--------------------±----------±-------±--------------------+

2 rows in set (0.001 sec)

MariaDB [ojs]> select * from edit_decisions where submission_id in (22288, 22317);

±-----------------±--------------±------±----------±---------±--------------------±----------------±---------+

| edit_decision_id | submission_id | round | editor_id | decision | date_decided | review_round_id | stage_id |

±-----------------±--------------±------±----------±---------±--------------------±----------------±---------+

| 15270 | 22288 | NULL | 5210 | 3 | 2024-04-18 15:16:30 | NULL | 1 |

| 15571 | 22288 | 1 | 5210 | 5 | 2024-05-21 16:33:56 | 4456 | 3 |

| 15645 | 22288 | 1 | 5210 | 14 | 2024-06-02 16:02:13 | 4456 | 3 |

| 15717 | 22288 | 2 | 5210 | 2 | 2024-06-05 20:38:47 | 4534 | 3 |

| 18793 | 22288 | NULL | 5443 | 7 | 2025-04-14 13:44:54 | NULL | 4 |

| 15410 | 22317 | NULL | 5210 | 3 | 2024-05-03 11:13:58 | NULL | 1 |

| 15543 | 22317 | 1 | 5210 | 6 | 2024-05-18 22:22:45 | 4491 | 3 |

±-----------------±--------------±------±----------±---------±--------------------±----------------±---------+

7 rows in set (0.005 sec)

MariaDB [ojs]> select * from publications where submission_id in (22288, 22317);

±---------------±--------------±---------------±--------------------±-------------------±-----------±----±--------------±-------±---------±--------±-------+

| publication_id | access_status | date_published | last_modified | primary_contact_id | section_id | seq | submission_id | status | url_path | version | doi_id |

±---------------±--------------±---------------±--------------------±-------------------±-----------±----±--------------±-------±---------±--------±-------+

| 24204 | 0 | 2025-05-22 | 2025-05-22 21:58:17 | 39117 | 183 | 0 | 22288 | 3 | NULL | 1 | 14951 |

| 24233 | 0 | NULL | 2024-04-29 12:41:03 | 39185 | 183 | 0 | 22317 | 1 | NULL | 1 | NULL |

±---------------±--------------±---------------±--------------------±-------------------±-----------±----±--------------±-------±---------±--------±-------+

2 rows in set (0.001 sec)

unfortunately i don’t have a copy of the database when we were at version 3.3.0-*

any ideas for me?

thanks!