ERM - OpenAccess derived table for party 'openaccess_party'
Content
Overview
The purpose of this derived table is to show informations about the records in the table "folio_oa.party" and their linked tables and reference data.
Github Issue
https://github.com/folio-org/folio-analytics/issues/786 (ERM - Create derived table 'openaccess_party' for Metadb)
Attributes for the derived table
Folio Table | Folio Attribute | Description | Datatype | Sample Output | FK (From table) | PK (To table) | Notes |
|---|---|---|---|---|---|---|---|
folio_oa.party | p_id |
| UUID | ee25ff43-a800-4135-83a5-10ec374c0aa8 | Table: folio_oa.party Attribute: p_id | Table: folio_oa.request_party Attribute: rp_party_fk --------- Table: folio_oa.alternate_email_address Attribute: aea_owner_fk --------- Table: folio_oa.party_address Attribute: padd_owner_fk |
|
folio_oa.party | version |
| INTEGER | 0 |
|
|
|
folio_oa.party | p_title |
| TEXT |
|
|
|
|
folio_oa.party | p_family_name |
| TEXT | Kohler |
|
|
|
folio_oa.party | p_given_names |
| TEXT | Lukas |
|
|
|
folio_oa.party | p_full_name |
| TEXT | Lukas Kohler |
|
|
|
folio_oa.party | p_orcid_id |
| UUID |
|
|
|
|
folio_oa.party | p_main_email |
| TEXT | blubb@example.com |
|
|
|
folio_oa.alternate_email_address | aea_id |
| UUID | 584deb6d-3abc-45be-be94-94e777e43052 |
|
|
|
folio_oa.alternate_email_address | aea_version |
| INTEGER | 0 |
|
|
|
folio_oa.alternate_email_address | aea_email |
| TEXT | blubb2@example.de |
|
|
|
folio_oa.alternate_email_address | aea_owner_fk |
| UUID | 731adb4c-52ec-4ac8-9ac6-4ae0938aef6e | Table: folio_oa.alternate_email_address Attribute: aea_owner_fk | Table: folio_oa.party Attribute: p_id |
|
folio_oa.party | p_phone |
| TEXT |
|
|
|
|
folio_oa.party | p_mobile |
| TEXT |
|
|
|
|
folio_oa.party | p_street_address_fk |
| UUID |
| Table: folio_oa.party Attribute: p_street_address_fk | Table: folio_oa.party_address Attribute: padd_id |
|
folio_oa.party | p_institution_level_1_fk |
| UUID | 8aaa80aa85c4472e0185e3314006000c | Table: folio_oa.party Attribute: p_institution_level_1_fk | Table: folio_oa.refdata_value Attribute: rdv_id |
|
folio_oa.refdata_value | p_institution_level_1_value |
| TEXT | medizinische_fakultat |
|
|
|
folio_oa.refdata_value | p_institution_level_1_label |
| TEXT | Medizinische Fakultät |
|
|
|
folio_oa.party | p_institution_level_2 |
| TEXT |
|
|
|
|
Linking approach
folio_oa.party
LEFT JOIN folio_oa.alternate_email_address ON alternate_email_address.aea_owner_fk = party.p_id
LEFT JOIN folio_oa.refdata_value AS p_institution_level_1 ON p_institution_level_1.rdv_id = party.p_institution_level_1_fk
Explanation to special relations between tables
Relation derived tables "openaccess_publication_request" and "openaccess_party"
You can link the derived tables "openaccess_publication_request" and "openaccess_party" via the UUIDs
openaccess_publication_request.pr_request_contact_rp_party_fk = openaccess_party.p_id
openaccess_publication_request.pr_corresponding_author_rp_party_fk= openaccess_party.p_id
In practice, the link must be made twice from the derived table "openaccess_publication_request" to the derived table "openaccess_party".
openaccess_publication_request
LEFT JOIN openaccess_party AS openaccess_party_request_contact ON openaccess_party_request_contact.p_id = openaccess_publication_request.pr_request_contact_rp_party_fk
LEFT JOIN openaccess_party AS openaccess_party_corresponding_author ON openaccess_party_corresponding_author.p_id =
openaccess_publication_request.pr_corresponding_author_rp_party_fk