search

sanger / unified_warehouse

MIT License
2 stars 9 forks source link

GPL-673 Example SQL MLWH query to find COG-UK ids for CP plates sent off site #129

Closed rl15 closed 3 weeks ago

rl15 commented 4 years ago

Description GPL-673 | As leads for PAMs cotrack (Ian W, Jason O & Christopher J) we would like to identify the COG-UK samples in the 96well CP plate from the MLWH to track samples that have been picked and then sent off site for sequencing.

Who the primary contacts are for this work Ian W (PAM) Jason O (PAM) Christopher J (PAM) Danni W

Additional context or information Ian W wrote (Friday, 2 October 2020 at 08:48)

We need to track any samples that are sent off site for sequencing.

As I understand it, after speaking to Danni, the process is as follows: the sample is cherry-picked from the Lighthouse plate (robot event) into a different, consolidated plate A new plate is created i.e. not the same plate id as the Lighthouse plate The new plate is scanned into a location of type 'Delivered' in LabWhere

...

Can you please confirm:

  1. How we identify the 384 (changed to 96 well later in day) well plate which the samples are moved into
  2. I think we can use a modified form of the query from 4.2.0 on https://ssg-confluence.internal.sanger.ac.uk/display/PSDPUB/h.+Progression+reporting to identify when this plate has been delivered
  3. If/when it's likely to be possible to tell where a plate has been delivered to (rl15 - agreed on 30th Sept not responsibility for core LIMS)
rl15 commented 4 years ago

Barcode of the 96 well plate COG-UK ids in that plate From plates scanned into location TBD in LabWhere

TWJW-SANGER commented 4 years ago

Need to

May want to run old tracking and this report concurrently for a period to validate process. (Probably no way to check historic data)

TWJW-SANGER commented 4 years ago 
USE mlwarehouse;

SELECT 
DISTINCT
cherry_pick.cherry_pick_plate AS plate_barcode
,sample.supplier_name AS cog_uk_id
FROM
-- subquery to get plates and samples that are cherry picks
(
    SELECT 
    DISTINCT
    labware.friendly_name AS cherry_pick_plate
    , sample.friendly_name AS sample_name

    FROM mlwh_events.events e
    JOIN mlwh_events.event_types et ON (e.event_type_id=et.id)

    -- look for sample
    JOIN mlwh_events.roles sample_role ON (sample_role.event_id=e.id)
    JOIN mlwh_events.role_types sample_role_rt ON (sample_role.role_type_id=sample_role_rt.id)
    JOIN mlwh_events.subjects sample ON (sample_role.subject_id=sample.id)

    -- look for cherry picked plate
    JOIN mlwh_events.roles labware_r ON (labware_r.event_id=e.id)
    JOIN mlwh_events.role_types labware_rt ON (labware_r.role_type_id=labware_rt.id)
    JOIN mlwh_events.subjects labware ON (labware_r.subject_id=labware.id)

    WHERE
    et.key = "slf_cherrypicking"
    AND sample_role_rt.key = "sample"
    AND labware_rt.key = "labware"
) AS cherry_pick
-- join against sample table AND study to only get Heron samples
JOIN sample ON sample.name=cherry_pick.sample_name
JOIN stock_resource stock ON stock.id_sample_tmp=sample.id_sample_tmp
JOIN study ON stock.id_study_tmp=study.id_study_tmp
JOIN
-- subquery to find plates that have ended up in delivered location
(
    SELECT 
     labware.friendly_name AS delivered_plate

    FROM mlwh_events.events e
    JOIN mlwh_events.metadata md ON e.id = md.event_id -- This set of JOINS against labwhere events

    -- plate / labware id
    JOIN mlwh_events.roles labware_r ON e.id = labware_r.event_id
    JOIN mlwh_events.subjects labware ON labware.id = labware_r.subject_id
    JOIN mlwh_events.role_types labware_rt ON labware_r.role_type_id = labware_rt.id

    -- location 
    JOIN mlwh_events.roles location_r ON e.id = location_r.event_id
    JOIN mlwh_events.subjects location ON location.id = location_r.subject_id
    JOIN mlwh_events.role_types location_rt ON location_r.role_type_id = location_rt.id

    -- make sure we're getting the last event for this plate to avoid
    -- issues with plates being scanned in and then out of target location
    JOIN
    (
        SELECT 
        labware.friendly_name AS delivered_plate
        , MAX(e.occured_at) AS occured_at
        FROM mlwh_events.events e

        -- plate / labware id
        JOIN mlwh_events.roles labware_r ON e.id = labware_r.event_id
        JOIN mlwh_events.subjects labware ON labware.id = labware_r.subject_id
        JOIN mlwh_events.role_types labware_rt ON labware_r.role_type_id = labware_rt.id

        WHERE e.lims_id='LABWHERE'
        AND labware_rt.key = 'labware'

        GROUP BY delivered_plate

    ) AS last_plate_event ON labware.friendly_name = last_plate_event.delivered_plate 
                          AND e.occured_at = last_plate_event.occured_at  -- this forces last labwhere event row for a given plate

    WHERE e.lims_id='LABWHERE'
    AND labware_rt.key = 'labware'
    AND location_rt.key = 'location'
    AND md.key='location_info'
    -- this matches the location of all delivered things
    AND REPLACE(md.value," ","_") LIKE "%Delivered%"
) AS delivery
ON delivery.delivered_plate=cherry_pick.cherry_pick_plate

WHERE
sample.supplier_name IS NOT NULL
AND study.name = "Heron Project"
AND sample.supplier_name != "Positive Control"
AND sample.supplier_name != "Negative Control"
TWJW-SANGER commented 3 years ago

Written up query on https://ssg-confluence.internal.sanger.ac.uk/pages/viewpage.action?pageId=111057201

TWJW-SANGER commented 3 years ago

After PAM meeting.