ACP Container Tests

-- record_list(1,2,3)
WITH
     search_org_list AS (
       SELECT DISTINCT id FROM actor.org_unit_descendants(1)
     ),
     luri_org_list AS (
       SELECT DISTINCT id FROM actor.org_unit_ancestors(1)
     )
SELECT  m.source AS id,
        m.source AS record,
        1.0/(1)::NUMERIC AS rel,
        1.0/(1)::NUMERIC AS rank,
        FIRST(mrd.attrs->'date1') AS tie_break
  FROM  metabib.metarecord_source_map m

        INNER JOIN metabib.record_attr mrd ON m.source = mrd.id
        INNER JOIN biblio.record_entry bre ON m.source = bre.id
        LEFT JOIN config.bib_source cbs ON bre.source = cbs.id
  WHERE 1=1
        AND (
          m.source IN (E'1',E'2',E'3')
        )
        -- Filter records based on visibility
        AND (
            cbs.transcendant IS TRUE
            OR
            EXISTS(
                SELECT 1 FROM asset.opac_visible_copies
                WHERE circ_lib IN ( SELECT * FROM search_org_list )
                    AND record = m.source
                LIMIT 1
            )
            OR
            EXISTS(
                SELECT 1 FROM asset.call_number acn
                    JOIN asset.uri_call_number_map aucnm ON acn.id = aucnm.call_number
                    JOIN asset.uri uri ON aucnm.uri = uri.id
                WHERE NOT acn.deleted AND uri.active AND acn.record = m.source AND acn.owning_lib IN (
                    SELECT * FROM luri_org_list
                )
                LIMIT 1
            )
        )

  GROUP BY 1
  ORDER BY 4 ASC NULLS LAST, 5 DESC NULLS LAST, 3 DESC
  LIMIT 25000

-- record_list(1,2,3) container(acp,staff_client,1,TOKEN)
WITH
     search_org_list AS (
       SELECT DISTINCT id FROM actor.org_unit_descendants(1)
     ),
     luri_org_list AS (
       SELECT DISTINCT id FROM actor.org_unit_ancestors(1)
     ),
     container_x33f4988 AS (
       SELECT cn.record AS record FROM container.copy_bucket_item ci
             JOIN container.copy_bucket c ON (c.id = ci.bucket) JOIN asset.copy cp ON (ci.target_copy = cp.id) JOIN asset.call_number cn ON (cp.call_number = cn.id)
       WHERE c.btype = $_6520$staff_client$_6520$
             AND c.id = E'1'
             AND (c.pub IS TRUE OR c.owner = 1)
       UNION
       SELECT pr.peer_record AS record FROM container.copy_bucket_item ci
             JOIN container.copy_bucket c ON (c.id = ci.bucket)
             JOIN biblio.peer_bib_copy_map pr ON ci.target_copy = pr.target_copy
       WHERE c.btype = $_6520$staff_client$_6520$
             AND c.id = E'1'
             AND (c.pub IS TRUE OR c.owner = 1)
     )
SELECT  m.source AS id,
        m.source AS record,
        1.0/(1)::NUMERIC AS rel,
        1.0/(1)::NUMERIC AS rank,
        FIRST(mrd.attrs->'date1') AS tie_break
  FROM  metabib.metarecord_source_map m

      LEFT JOIN container_x33f4988 ON container_x33f4988.record = m.source
        INNER JOIN metabib.record_attr mrd ON m.source = mrd.id
        INNER JOIN biblio.record_entry bre ON m.source = bre.id
        LEFT JOIN config.bib_source cbs ON bre.source = cbs.id
  WHERE 1=1
        AND (
          m.source IN (E'1',E'2',E'3')
          AND (container_x33f4988 IS NOT NULL)
        )
        -- Filter records based on visibility
        AND (
            cbs.transcendant IS TRUE
            OR
            EXISTS(
                SELECT 1 FROM asset.opac_visible_copies
                WHERE circ_lib IN ( SELECT * FROM search_org_list )
                    AND record = m.source
                LIMIT 1
            )
            OR
            EXISTS(
                SELECT 1 FROM asset.call_number acn
                    JOIN asset.uri_call_number_map aucnm ON acn.id = aucnm.call_number
                    JOIN asset.uri uri ON aucnm.uri = uri.id
                WHERE NOT acn.deleted AND uri.active AND acn.record = m.source AND acn.owning_lib IN (
                    SELECT * FROM luri_org_list
                )
                LIMIT 1
            )
        )

  GROUP BY 1
  ORDER BY 4 ASC NULLS LAST, 5 DESC NULLS LAST, 3 DESC
  LIMIT 25000

-- record_list(1,2,3) -container(acp,staff_client,1,TOKEN)
WITH
     search_org_list AS (
       SELECT DISTINCT id FROM actor.org_unit_descendants(1)
     ),
     luri_org_list AS (
       SELECT DISTINCT id FROM actor.org_unit_ancestors(1)
     ),
     container_x3542938 AS (
       SELECT cn.record AS record FROM container.copy_bucket_item ci
             JOIN container.copy_bucket c ON (c.id = ci.bucket) JOIN asset.copy cp ON (ci.target_copy = cp.id) JOIN asset.call_number cn ON (cp.call_number = cn.id)
       WHERE c.btype = $_6531$staff_client$_6531$
             AND c.id = E'1'
             AND (c.pub IS TRUE OR c.owner = 1)
       UNION
       SELECT pr.peer_record AS record FROM container.copy_bucket_item ci
             JOIN container.copy_bucket c ON (c.id = ci.bucket)
             JOIN biblio.peer_bib_copy_map pr ON ci.target_copy = pr.target_copy
       WHERE c.btype = $_6531$staff_client$_6531$
             AND c.id = E'1'
             AND (c.pub IS TRUE OR c.owner = 1)
     )
SELECT  m.source AS id,
        m.source AS record,
        1.0/(1)::NUMERIC AS rel,
        1.0/(1)::NUMERIC AS rank,
        FIRST(mrd.attrs->'date1') AS tie_break
  FROM  metabib.metarecord_source_map m

      LEFT JOIN container_x3542938 ON container_x3542938.record = m.source
        INNER JOIN metabib.record_attr mrd ON m.source = mrd.id
        INNER JOIN biblio.record_entry bre ON m.source = bre.id
        LEFT JOIN config.bib_source cbs ON bre.source = cbs.id
  WHERE 1=1
        AND (
          m.source IN (E'1',E'2',E'3')
          AND NOT (container_x3542938 IS NOT NULL)
        )
        -- Filter records based on visibility
        AND (
            cbs.transcendant IS TRUE
            OR
            EXISTS(
                SELECT 1 FROM asset.opac_visible_copies
                WHERE circ_lib IN ( SELECT * FROM search_org_list )
                    AND record = m.source
                LIMIT 1
            )
            OR
            EXISTS(
                SELECT 1 FROM asset.call_number acn
                    JOIN asset.uri_call_number_map aucnm ON acn.id = aucnm.call_number
                    JOIN asset.uri uri ON aucnm.uri = uri.id
                WHERE NOT acn.deleted AND uri.active AND acn.record = m.source AND acn.owning_lib IN (
                    SELECT * FROM luri_org_list
                )
                LIMIT 1
            )
        )

  GROUP BY 1
  ORDER BY 4 ASC NULLS LAST, 5 DESC NULLS LAST, 3 DESC
  LIMIT 25000