[FOLIO-1251] JOIN between resources mod-feesfines and other modules Created: 16/May/18  Updated: 12/Nov/18  Resolved: 09/Jul/18

Status: Closed
Project: FOLIO
Components: None
Affects versions: None
Fix versions: None

Type: Task Priority: P2
Reporter: lluvia Manilla Assignee: lluvia Manilla
Resolution: Done Votes: 0
Labels: resourceaccess
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original estimate: Not Specified

Issue links:
Blocks
blocks UIU-238 Display Fees/Fines History (Open/Clos... Closed
blocks UIU-239 Display Fee/Fine Details Closed
blocks FOLIO-1318 Include mod-feefines in Vagrant and A... Closed
Sprint:

 Description   

Initially I created views by performing join between tables of different modules through template_create_tenant.sql for a specific resource. Like the following:

– View Fees Fines History
CREATE VIEW diku_mod_feesfines.feefine_history_view AS SELECT id AS id,to_jsonb(t) AS jsonb
FROM (
SELECT a.jsonb->>'id' AS "id",
SUBSTRING(a.jsonb->'metadata'->>'createdDate' FROM 1 FOR 10) AS "dateCreated",
SUBSTRING(a.jsonb->'metadata'->>'updatedDate' FROM 1 FOR 10) AS "dateUpdated",
f.jsonb->'feeFineType' AS "feeFineType",
a.jsonb->'amount' AS "charged",
a.jsonb->'remaining' AS "remaining",
a.jsonb->'status'->>'name' AS "status",
a.jsonb->'paymentStatus'->>'name' AS "paymentStatus",
o.jsonb->'desc' AS "feeFineOwner",
ins.jsonb->'title' AS "item",
i.jsonb->'barcode' AS "barcode",
m.jsonb->'name' AS "itemType",
h.jsonb->'callNumber' AS "callNumber",
s.jsonb->'name' AS "location",
a.jsonb->'itemId' AS "itemId",
a.jsonb->'loanId' AS "loanId",
a.jsonb->'userId' AS "userId"
FROM diku_mod_feesfines.accounts a
INNER JOIN diku_mod_users.users u ON u.jsonb->>'id' = a.jsonb->>'userId'
INNER JOIN diku_mod_feesfines.feefines f ON f.jsonb->>'id' = a.jsonb->>'feeFineId'
INNER JOIN diku_mod_feesfines.owners o ON o.jsonb->>'id' = a.jsonb->>'ownerId'
LEFT JOIN diku_mod_circulation_storage.loan l ON a.jsonb->>'loanId' = l.jsonb->>'id'
LEFT JOIN diku_mod_inventory_storage.item i ON i.jsonb->>'id' = a.jsonb->>'itemId'
LEFT JOIN diku_mod_inventory_storage.material_type m ON i.jsonb->>'materialTypeId' = m.jsonb->>'id'
LEFT JOIN diku_mod_inventory_storage.holdings_record h ON i.jsonb->>'holdingsRecordId' = h.jsonb->>'id'
LEFT JOIN diku_mod_inventory_storage.instance ins ON ins.jsonb->>'id' = h.jsonb->>'instanceId'
LEFT JOIN diku_mod_inventory_storage.shelflocation s ON h.jsonb->'permanentLocationId' = s.jsonb->'id'
) t;

--View item information
CREATE VIEW myuniversity_mymodule.item_information_view AS SELECT id AS id, to_jsonb(t) AS jsonb
FROM (
SELECT i.jsonb->>'id' AS "id",
ins.jsonb->'title' AS "instance",
h.jsonb->'callNumber' AS "callNumber",
i.jsonb->'barcode' AS "barcode",
i.jsonb -> 'materialTypeId' AS "materialTypeId",
i.jsonb->'status'->>'name' AS "itemStatus",
s.jsonb->'name' AS "location"
FROM diku_mod_inventory_storage.instance ins
INNER JOIN diku_mod_inventory_storage.holdings_record h ON ins.jsonb->'id' = h.jsonb->'instanceId'
INNER JOIN diku_mod_inventory_storage.item i ON h.jsonb->'id' = i.jsonb->'holdingsRecordId'
LEFT JOIN diku_mod_inventory_storage.shelflocation s ON h.jsonb->'permanentLocationId' = s.jsonb->'id'
) t;

When I change the way to create tables and views from the schema.json, I performed the tests to create the views related to different modules and it no were created them, because it only allows to make views between tables of the module itself.

However, we think that to optimize the recovery of information, it is better to build the view of these tables. For example, in our current programming we call a resource, but if we preside over this resource we have to make more calls, at least 7, for example for our case we need to show related information of the resources:

feesfines
users
loan-storage/loans
item-storage/items
instance-storage/instances
holdings-storage/holdings
shelf-locations



 Comments   
Comment by John Malconian [ 17/May/18 ]

Hi lluvia Manilla - Not sure what you are asking from me here or if maybe this needs to be assigned to someone else?

Comment by lluvia Manilla [ 18/May/18 ]

Sorry John Malconian, I did not know how you were assigned to you. Who do you recommend assigning?

Comment by Holly Mistlebauer [ 18/May/18 ]

Jakub Skoczen Lluvia from UNAM needs help with this issue. Is this the correct way to report this type of an issue? Thanks, Holly

Comment by Holly Mistlebauer [ 18/May/18 ]

Jakub Skoczen I also just posted this on Slack. Thanks!

Comment by Mike Taylor [ 18/May/18 ]

You cannot, at the SQL level, create joins between different modules – because you don't even know what other modules exist in the system, and you have no way of discovering what their tables and fields are even if you do know. You have to call other modules via their WSAPIs.

Comment by shale99 [ 23/May/18 ]

Former user - can you describe the use case that requires this view - can this be achieved via api calls to other modules?

Comment by lluvia Manilla [ 23/May/18 ]

Hi shale99!

View Fees Fines History.
It requires selecting the items by barcode or by title of all database. UIU-219: "charge-manual-fee-1.png"

View item information
Requires the display of barcode data, title, type of material, location and data of our module. For example UIU-238: "approved - 7 fees with a few filters (coming from loan page).png" and UIU-239:
"fee-fine-detail-1.jpg"

Comment by Holly Mistlebauer [ 24/May/18 ]

shale99, I had assigned this to Jakob but thought that I could perhaps move it to you since you are helping Lluvia. Is that o.k.?

Comment by Holly Mistlebauer [ 31/May/18 ]

Hi Lluvia. Jakub (the Tech Lead) and I have emailed back and forth about this issue. He said that Mike Taylor is correct: "You cannot, at the SQL level, create joins between different modules – because you don't even know what other modules exist in the system, and you have no way of discovering what their tables and fields are even if you do know. You have to call other modules via their WSAPIs." Jakob told me that "there are many examples on how to do cross-module joins on the API level in the system — the User Detail screen uses this to show Loans and other information and is probably the best example." I recommend that you look at that code and see how it is being done. Sorry it has taken so long to resolve this issue. If you need more help with this, please let me know and I will ask Jakob to assign someone.
Thanks,
Holly

Comment by David Crossley [ 01/Jun/18 ]

lluvia Manilla and others, in case you had not seen it, i had paraphrased Mike's explanation and linked to some other docs. Julian expanded.

https://dev.folio.org/guides/cross-module-joins/ and linked from the Start docs.

I will expand that to add the guidance via User Details front end.

Comment by Holly Mistlebauer [ 01/Jun/18 ]

Thanks David Crossley! Very helpful!

Comment by lluvia Manilla [ 01/Jun/18 ]

Thanks a lot, David Crossley! This week I was doing tests according to the section “Querying multiple modules via HTTP”.

Comment by Holly Mistlebauer [ 07/Jun/18 ]

lluvia Manilla, Jakub emailed me to let me know that he will ask Zeev or Marc to reach out to you about this issue. Hopefully you will hear something soon. Thanks...

Comment by Jakub Skoczen [ 08/Jun/18 ]

lluvia Manilla while it is true that the join on the DB-level would perform better the down side of that approach across FOLIO would be tight coupling of modules and loss of replaceability and modularity.

We have not been involved with mod-feesfines but it looks like it's similar in it's requirements with the loans endpoint which is part of the circulation domain. Generally, if you need to join things like user data or item information for a particular fee/fine, this operation should be fairly cheap and you can perform a live look-up. For cases when you need to filter/search or sort the fee/fines by a specific datum that would be stored in another module it probably makes sense to cache that datum locally in fee/fines.

Marc Johnson shale99 can we point lluvia Manilla to existing examples, e.g for loans?

Comment by Holly Mistlebauer [ 15/Jun/18 ]

Lluvia, have you received help from Marc or Zeev? Jakub told me that he would ask Marc or Zeev to reach out to you. If that hasn't happened, I will ask again. Sorry for the delay.

Comment by Holly Mistlebauer [ 18/Jun/18 ]

Hi Lluvia! Jakub tried to reach out to you last week but didn't hear back from you. Jakub says this module needs to be completely restructured to: a) use the APIs to join data; b) cache data on the fee/fine structure c) combine it with mod-circulation-storage. Marc is back this week so it would be best if you (or whoever is responsible for mod-feefines) reached out to Jakub and Marc on Slack. Thanks, Holly

Comment by lluvia Manilla [ 18/Jun/18 ]

Hi Holly! I will communicate with Jakub. Thank you very much.

Comment by lluvia Manilla [ 09/Jul/18 ]

Hi Jakub, close this issue because Wayne informed me that he was blocking the issue of FOLIO-1318 Closed , and previously I proposed the solution, which was already implemented.

Generated at Thu Feb 08 23:11:57 UTC 2024 using Jira 1001.0.0-SNAPSHOT#100246-sha1:7a5c50119eb0633d306e14180817ddef5e80c75d.