[ojs2 dev] SQl query to get articles with available galleys

Software Support
1

Hi,
for a plug-in I try to query all articles (with a plugin-specific) setting which have accessible galleys. I really only need the article ids, not the objects thats why my first attempt was to formulate a sql-query for that. On the other hand, there are so many different ways why a galley could not be accessible, thats it is very complicated to formulate that query. Can anyone maybe help me formulate it or know a way how I could employ some of the DAOs to get what I want?
accessible, in may context means, that you can read/dl the galley if you register as a subscriber.
I would be so thankful for any help…
Thanks in advance,
paf

2

Hi @paf,

What exact version of OJS are you using? (Please include this with your posts.)

Can you describe the conditions you want to use to limit the articles? You mentioned a plugin-specific setting; can you be more specific?

Regards,
Alec Smecher
Public Knowledge Project Team

3

Hi Alec,
I use OJS 2.4.8.1.
My Plugin sets up a Pubid which is an internal identifier in our system. To get a List of all Articles having such a Id I can use

$dao = new DAO();                                                                                                                          
$sql = "select                                                     
   setting_value,                                   
   article_id
  from                                               
   article_settings                    
  where                                          
   setting_name = \"pub-id::other::xxx\"";
$res = $dao->retrieve($sql);
$box = $res->getAssoc();
$res->Close();

The Articles get the ID while being imported to the OJS. But thats not important. Important that I need only the Articles which would be available as Fulltext when a user enters the page - in other words which would appear as PDF-Buttons in issue/issue.tpl in this part:

        <td class="tocArticleGalleysPages{if $showCoverPage} showCoverImage{/if}">
            <div class="tocGalleys">
                {if $hasAccess || ($subscriptionRequired && $showGalleyLinks)}
                    {foreach from=$article->getGalleys() item=galley name=galleyList}
                        <a href="{url page="article" op="view" path=$articlePath|to_array:$galley->getBestGalleyId($currentJournal)}" {if $galley->getRemoteURL()}target="_blank" {/if}class="file">{$galley->getGalleyLabel()|escape}</a>
                        {if $subscriptionRequired && $showGalleyLinks && $restrictOnlyPdf}
                            {if $article->getAccessStatus() == $smarty.const.ARTICLE_ACCESS_OPEN || !$galley->isPdfGalley()}
                                <img class="accessLogo" src="{$baseUrl}/lib/pkp/templates/images/icons/fulltext_open_medium.gif" alt="{translate key="article.accessLogoOpen.altText"}" />
                            {else}
                                <img class="accessLogo" src="{$baseUrl}/lib/pkp/templates/images/icons/fulltext_restricted_medium.gif" alt="{translate key="article.accessLogoRestricted.altText"}" />
                            {/if}
                        {/if}
                    {/foreach}
                    {if $subscriptionRequired && $showGalleyLinks && !$restrictOnlyPdf}
                        {if $article->getAccessStatus() == $smarty.const.ARTICLE_ACCESS_OPEN}
                            <img class="accessLogo" src="{$baseUrl}/lib/pkp/templates/images/icons/fulltext_open_medium.gif" alt="{translate key="article.accessLogoOpen.altText"}" />
                        {else}
                            <img class="accessLogo" src="{$baseUrl}/lib/pkp/templates/images/icons/fulltext_restricted_medium.gif" alt="{translate key="article.accessLogoRestricted.altText"}" />
                        {/if}
                    {/if}
                {/if}
            </div>

The Context is a CLI-Script which I start with

require('../../../tools/bootstrap.inc.php')

Best Wishes,
Paf

4

Hi @paf,

The closest existing function is probably PublishedArticleDAO::getBySetting. See that function in the API documentation.

Regards,
Alec Smecher
Public Knowledge Project Team

5

Thanks. I ended up with a query like this:

  select
     setting_value,
     article_id
  from
     published_articles as p_a
     left join issues as i on i.issue_id = p_a.issue_id
     left join articles as a on a.article_id = p_a.article_id
     left join article_settings as a_s on p_a.article_id = a_s.article_id
     left join journals as j on j.journal_id = a.journal_id
  where
     setting_name='pub-id::other::xxx'
     and i.published = 1
     and j.enabled = 1
     and (i.access_status = 1 or p_a.access_status = 1)

Just for those, who came here googling.

1 Like