Editorial activity statistics look strange - OJS 3.4.0.4

Software Support
,
22

@Tarcisio_Pereira, it seems it is because of the first two points, found bugs, listed in this issue Improve Editorial Activity calculations · Issue #9813 · pkp/pkp-lib · GitHub. MySQL and PostgreSQL differently check if a string value is = 0: the string value in the column submission_progress is now actually an empty string '' and MySQL lets this through as = 0, but PostgreSQL does not. So I need to change e.g. this line pkp-lib/classes/services/queryBuilders/PKPStatsEditorialQueryBuilder.php at stable-3_4_0 · pkp/pkp-lib · GitHub
with
$q->where('s.submission_progress', '=', 0)->orWhere('s.submission_progress', '=', '');
to fix it.

1 Like
23

@Tarcisio_Pereira and @mreedks, once the found bugs listed in the issue Improve Editorial Activity calculations · Issue #9813 · pkp/pkp-lib · GitHub are fixed, I will let you know. Those should fix all the issues you have reported here.
The fix will be available in the next release. Eventually you would like to apply the patch immediately.

1 Like
24

@bozana Thank you very much for your help.

25

@bozana Yes, that part looks better. Thank you for identifying the bug for Acceptance and Rejection rates.

26

Hi @bozana

Thank you for your help.
I applied the fix, but the data is still very strange.

Take a look at this sample:
image
and editorial activity

image
image918×859 63.6 KB

This situation is repeated in several journals, the numbers are too high to match reality.

Regards,
Tarcisio Pereira.

27

Hi @Tarcisio_Pereira

Hmmm…

I forgot to use ‘(’ and ‘)’ around where submission_progress = 0 or submission_progress = '''. But I am not sure if this would solve the problem.

Currently the patch would use this SQL for Submissions Received Total:

select COUNT(*) 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
) 
where "s"."context_id" in (1) and "s"."submission_progress" = 0 or "s"."submission_progress" = '' and ("pi"."date_published" is null or CAST(s.date_submitted AS DATE) <= pi.date_published)

but I think this would actually be correct:

select COUNT(*) 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
) 
where "s"."context_id" in (1) and ("s"."submission_progress" = 0 or "s"."submission_progress" = '') and ("pi"."date_published" is null or CAST(s.date_submitted AS DATE) <= pi.date_published)

Could you please test the two SQLs for your journal? Please do not forget to change the context ID in the SQLs to mach your.
Does it make difference, the two numbers that you get?

Thanks!
Bozana

EDIT: s. the new changes in PR: pkp/pkp-lib#9813 fix bugs in editorial activity stats by bozana · Pull Request #9848 · pkp/pkp-lib · GitHub

And maybe to double check: Have you applied the changes from this PR: pkp/pkp-lib#9813 fix bugs in editorial activity stats by bozana · Pull Request #9848 · pkp/pkp-lib · GitHub?

28

Hi @bozana.

I double checked and the fix is correctly applied.
For both queries:

ERROR: operator does not exist: character varying = integer
LINE 7: …ontext_id" in (94) and (“s”.“submission_progress” = 0 or “s”.

s.submission_progress is a character varying(50).

So I believe that the correct query, for PostgreSQL, should have 0 in single quotes.
For the first query, with 0 in single quotes:

count
78936
(1 row)

For the second query:

count
805
(1 row)

Regards,
Tarcisio Pereira.

29

So does the second query makes sense – is that number correct for total received submissions?

30

I added also single quotes in the PR. I used rebase, so you would need to somehow differently apply these last two changes:

  1. change was:
$q->where(
  fn (Builder $q) => $q->where('s.submission_progress', '=', '0')
  ->orWhere('s.submission_progress', '=', '')
);

instead of

$q->where('s.submission_progress', '=', 0)
  ->orWhere('s.submission_progress', '=', '')
  1. change were the single quotes around the 0 at two places in the PR.

(Maybe you can revert those two files to original and apply the PR again…)

I hope that the new changes in that PR will then lead to correct numbers?

1 Like
31

Hi @bozana

Thank you, now the numbers seem to be correct!

Regards,
Tarcisio Pereira

1 Like
32

Great to hear that :tada: :slight_smile:

1 Like