[MODINVSTOR-27] Join requirements for items-holdings-instances Created: 14/Nov/17  Updated: 15/Jan/19

Status: Open
Project: mod-inventory-storage
Components: None
Affects versions: None
Fix versions: None

Type: New Feature Priority: P3
Reporter: Niels Erik Nielsen Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: core
Remaining Estimate: Not Specified
Time Spent: 4 hours, 15 minutes
Original estimate: Not Specified

Issue links:
Blocks
is blocked by MODINVSTOR-25 Create holdings end-point Closed
is blocked by MODINV-16 Migrate to RAML Module Builder Closed
Relates
relates to UIIN-3 Search Instances v1 Closed
relates to UIIN-5 Create Item Associated with Holding Closed
relates to CIRC-49 include permanent location from holdi... Closed
relates to UIIN-32 Filter Instance List by Resource Type... Closed
Sprint:
Development Team: Core: Platform

 Description   

Specify the business logic requirements for searching, sorting (and details display) and thus for joining items, holdings and instances and possibly the set of reference tables.

There are two main join scenarios:

  • "small scale join" where the client needs just the few holdings and items associated with an instance for example and would like to have type references translated from UUIDs to type labels (ie 'ISBN' or 'sound recording').
  • "large scale join" where large data sets of instances, holdings and items are brought up, joined and sorted.

Small scale joins – ie pulling in reference table translations – could conceivably be done in business logic module. Large scale joins more likely on the database level.



 Comments   
Comment by Niels Erik Nielsen [ 15/Nov/17 ]

I'm not entirely clear on whether we're developing the Codex search, or a native Inventory search, or both at this point.

The Codex search would not require any joins (assuming that the Codex API is basically our instance record minus a few fields)

The Inventory UX prototype so far has one suggestion (which is just a suggestion at this point) for filtering, that would require a potentially somewhat large scale join between 'instance' and 'holdings-record' (filtering by location). This would be beyond the Codex requirements afaict. But I can spec that out in pseudo code or something.

There is nothing in the UX yet that seems to require a three-way join between 'instance', 'holdings-record' and 'item'. I don't know if there will be. We have a 'format' on 'item' for example. Searching by format could result in a very large join, but would we need to search on that, since we also have instance-format and instance-type on 'instance'? I could spec it out regardless, though.

I suppose we could eventually make use of a three-way join for the very distinct search of items by barcode. I can make an example of that too.

Comment by Niels Erik Nielsen [ 15/Nov/17 ]

Searching for instances by location (from 'holdings-record' table that does not yet exist):

find instance(attributes)  
from instance and holdings-record, joined on instance.uuid
where   holdings-record(location) = 'some location'  

location could be a UUID, so potentially a join with a 'location' table as well.

Finding an instance (one that would be) by item barcode:

find instance(attributes) and holdings(location, callnumber, holdingsstatements) and item (format, status, temp location, barcode)
from instance and holdings-record and item, joined on instance UUID and holdings UUID
where item.barcode = 'some barcode'

Searching for instances by item format (??):

find instance(attributes) 
from instance and holdings-record and item, joined on instance UUID and holdings UUID
where item.formatID = 'some UUID'

As for sorting, we're so far displaying title, creators(repeated), identifiers(repeated), publishers(repeated), publication dates(repeated). Guess these would be the initial sorting candidates for instances results lists.

Comment by shale99 [ 03/Dec/17 ]

cases 2 and 3 seem to be very similar (bar-code matching / format matching)

data population: (existing indexes in mod-inv-storage not shown here, fields that need to be searchable / sortable need indexes as well)

  INSERT INTO cql7_mod_inventory_storage.holdings_record
  SELECT id, 
  jsonb_build_object(
	'id', id, 
	'instanceId', instance_id , 
	'permanentLocationId' ,  permanentLocationId)
  FROM (select gen_random_uuid() AS id , generate_series(10000000, 13000001) || '-a3e4-465c-82f1-acade4e8e170' as instance_id , 
        'd9cd0bed-1b49-4b5e-a7bd-064b8d17723' || trunc(random() * 9) as permanentLocationId) as alias;   
  

  INSERT INTO cql7_mod_inventory_storage.instance
  SELECT id, 
  jsonb_build_object(
      'id', id, 
      'title', title, 
      'source' , source, 
      'edition' , edition, 
      'instanceTypeId' , '2b94c631-fca9-a892-c730-03ee529ffe2c',
  	  'creators', json_build_array(jsonb_build_object('name', 'ari sigal' , 'creatorTypeId' , '2b94c631-fca9-a892-c730-03ee529ffe2a')))
  FROM (select (generate_series(10000000, 13000001) || '-a3e4-465c-82f1-acade4e8e170')::uuid AS id ,
        (md5(random()::text)) || ' lörd the root  '  || (md5(random()::text)) AS title,
        (md5(random()::text)) as source,  trunc(random() * 20) || ' ed.' as edition) as alias;   
  

  INSERT INTO cql7_mod_inventory_storage.item
  SELECT id, 
  jsonb_build_object(
      'id', id, 
      'barcode' ,  barcode , 
      'status' , jsonb_build_object('name', barcode || '21ag'),     
      'materialTypeId' , '1c302429-5d78-4f8b-a367-3437bf1ab56e', 
      'permanentLoanTypeId', '2c302429-5d78-4f8b-a367-3437bf1ab56e',
  	  'temporaryLoanTypeId', '2c302429-5d78-4f8b-a367-3437bf1ab56e', 
      'title', title, 
      'permanentLocationId' , permanentLocationId)
  FROM (select gen_random_uuid() AS id , 
        trunc(random() * 200000) || 'g' AS barcode,  
        ((md5(random()::text)) || ' zeev shalev '  || (md5(random()::text))) AS title,
        (generate_series(10000000, 13000001) || '-a3e4-465c-82f1-acade4e8e170')::uuid AS permanentLocationId) as alias;   
		
 //Searching for instances by location		
  CREATE OR REPLACE VIEW cql7_mod_inventory_storage.instance_holding_view AS 
  select 
   u._id,u.jsonb as jsonb, g.jsonb as h_jsonb from cql7_mod_inventory_storage.instance u  
   join 
   cql7_mod_inventory_storage.holdings_record g on lower(f_unaccent(u.jsonb->>'id')) = lower(f_unaccent(g.jsonb->>'instanceId'))
   
  //Finding an instance (one that would be) by item barcode:
  CREATE OR REPLACE VIEW cql7_mod_inventory_storage.item_instance_holding_view AS 
  select 
  it._id,it.jsonb as jsonb, ho.jsonb as h_jsonb, ins.jsonb as ins_jsonb from cql7_mod_inventory_storage.instance ins  
   join cql7_mod_inventory_storage.holdings_record ho on lower(f_unaccent(ins.jsonb->>'id')) = lower(f_unaccent(ho.jsonb->>'instanceId'))
   join cql7_mod_inventory_storage.item it on lower(f_unaccent(it.jsonb->>'permanentLocationId')) = lower(f_unaccent(ho.jsonb->>'instanceId'))
    

INDEXES 
instance  (lower(f_unaccent(jsonb->>'id')));
item  (lower(f_unaccent(jsonb->>'permanentLocationId')));
holdings_record (lower(f_unaccent(jsonb->>'instanceId')));
holdings_record using gin (lower ( f_unaccent(jsonb->>'permanentLocationId'))  gin_trgm_ops);

QUERIES

//find instances by location
select * FROM cql7_mod_inventory_storage.instance_holding_view WHERE lower(f_unaccent(instance_holding_view.h_jsonb->>'permanentLocationId')) ~ lower(f_unaccent('d9cd0bed')) ORDER BY lower(f_unaccent(instance_holding_view.jsonb->>'title')) limit 30 ;

//Finding an instance (one that would be) by item barcode: (SORT BY FIELDS IN INSTANCE TABLE ONLY!)
select ins_jsonb from cql7_mod_inventory_storage.item_instance_holding_view where lower(f_unaccent(jsonb->>'barcode')) ~ '5f0bc5' limit 10;

select ins_jsonb from cql7_mod_inventory_storage.item_instance_holding_view where lower(f_unaccent(jsonb->>'barcode')) ~ '5f0bc5a84750f6be3358f03330f02' order by lower(f_unaccent(ins_jsonb->>'title')) limit 10;

Comment by shale99 [ 03/Dec/17 ]

this view can be added manually (writing the actual sql) in the schema.json.
support for this declaratively will be supported with issue RMB-104 Closed

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