my_projects

[jreubens]

• owner_organisation/group should be added to the available output fields. Currently you don't know to which group the project belongs; Ideally a PI should be added as well, but this is not yet available in the database. --> should be added soon

[stijnvanhoey]

I can find an owner_organization field linked to the tags or linked to the receivers in the database and an owner_pi linked to the tags.

@bwydoogh do you tackle this on the dbase level, extending the vliz.projects_view view? Or should I setup an appropriate query? If the latter, please explain me how to properly connect the owner_organization with the projects.

[bwydoogh]

@stijnvanhoey The table tags has a field owner_group_fk, referring to the table etn_group (and there you can grab the field name). The same is true for the receivers-table.

Regarding PI: no changes foreseen yet (we will get that information from IMIS and will not store it in ETN. @jreubens Correct? This was what we discussed last week, right?)

[jreubens]

regarding PI : no changes are indeed foreseen currently

[stijnvanhoey]

@bwydoogh I think this need to be tackled by the vliz.projects.view itself, as I do not have the rights to access the etn_group table:

Error: <SQL> 'SELECT * 
FROM vliz.etn_group'
  nanodbc/nanodbc.cpp:1587: 42501: ERROR: permission denied for relation etn_group;
Error while executing the query 

[bwydoogh]

@stijnvanhoey No, that would not be the right place (as this is information on tag and receiver level). I granted access to etn_group (there are no secrets in that table). So please try again.

[stijnvanhoey]

@bwydoogh I think it would be good to have a quick control on my attempt to setup the query to make sure the animal/network owner group is linked properly. If I am correct, I have to JOIN differently for both animal versus network projects:

SELECT projects.*, 
      CASE 
        WHEN projects.type IN ('network') THEN network_temp.name
        WHEN projects.type IN ('animal') THEN animal_temp.name 
        ELSE NULL
      END AS owner_organization
   FROM vliz.projects_view AS projects
     LEFT JOIN (
        SELECT  DISTINCT deployments.projectcode AS network_projectcode, etn_group.name
        FROM vliz.deployments_view AS deployments
         JOIN vliz.receivers AS receivers ON (receivers.id_pk = deployments.receiver_fk)
         JOIN vliz.etn_group AS etn_group ON (etn_group.id_pk = receivers.owner_group_fk)
        ) AS network_temp ON (network_temp.network_projectcode = projects.projectcode) 
            AND projects.type IN ('network')
     LEFT JOIN (
        SELECT DISTINCT animals.projectcode AS animal_projectcode, etn_group.name
        FROM vliz.animals_view animals 
          JOIN vliz.animal_tag_release AS animal_tag_release ON (animals.id_pk = animal_tag_release.animal_fk)
          JOIN vliz.tags AS tags ON (animal_tag_release.tag_fk = tags.id_pk)
          JOIN vliz.etn_group AS etn_group ON (etn_group.id_pk = tags.owner_group_fk)
        ) animal_temp ON (animal_temp.animal_projectcode = projects.projectcode) 
            AND projects.type IN ('animal')

• is it ok to join on the projectcode?
• looking for animal projects, I get double owner-organizations (cfr. phd_verhelst), as well as projects without owner_organization:
• I also get network pojects without an owner organization.

[bwydoogh]

@jreubens @stijnvanhoey I don't know how I should interpret the question of @jreubens but a project has no owning group. A receiver has its owning group and a project isn't link to a receiver but to a receiver deployment. And a group gets access to a project. But there is no immediate 1-on-1 relationship between a project and a group. So, @jreubens, what exactly do you want?

[jreubens]

I'll discuss this with @bwydoogh on dblevel and come back to this one!

[jreubens]

another remark, currently also the CPOD projects are listed (as they are part of the same database) This should be avoided @bwydoogh I guess this should also be tackled on DB level?

[bwydoogh]

@jreubens On project level we already have a field project_type. But that field is not present in the view. I'll add it (... and done ;-)...). Is this sufficient to continue?

[jreubens]

Just discussed with @bwydoogh. On project level there is the fiels 'administrators'. We will add an extra field to assign one of the administrators as PI. Once we have this field available, the link can be made and added to the output

[stijnvanhoey]

@jreubens, if I understand correctly, the package does not need adjustments regarding this issue? All on dbase level?

[jreubens]

adjustment is indeed on DB level. @bwydoogh is administrator (and later PI) available on the view?

[bwydoogh]

@jreubens No, administrators is not part of any view. It is a concept which exists in the ETN dataportal only, and I strongly believe this should stay like that. Administrators (how it is functionally defined right now) get some additional access rights in the Dataportal. Why should those administrators be exposed by this package? PI (Principal Investigator) is something else... that has some functional meaning. And yes, once the concept of a PI has been implemented we can add it to the project_view.

[bwydoogh]

And yes, once the concept of a PI has been implemented we can add it to the project_view.

Done, and available by the field principal_investigator (project_view)

[stijnvanhoey]

@damianooldoni should we add this in a separate unit-test (although it would test dbase-structure instead of package functionality...), just to make sure thus keeps available?

[damianooldoni]

@stijnvanhoey : it sounds reasonable, yes. A unit-test testing the presence of field principal_investigator in output df of get_projects() sounds basic but useful. A link to this issue as comment in unit-test file as well, I think.

[peterdesmet]

Trying to close this issue. From what I gather, all action items are completed/discussed and are all relating to the projects view

• principal_investigator is available in the project view, with an email address
• There is a test for the presence of this field: https://github.com/inbo/etn/blob/aa0b739f3866c3b83126fb275846cd921c724c6d/tests/testthat/test-get_projects.R#L14
• owner_organization is not added to the project view
• group is not added to the project view

Knowing the principal_investigator of a project seems sufficient to me, but @jreubens @PieterjanVerhelst @IPauwels please reopen if that is not the case.