Source code for allensdk.internal.api.queries.compound_lims_queries

from typing import List
import pandas as pd
from allensdk.internal.api import PostgresQueryMixin
from allensdk.internal.api.queries.ecephys_lims_queries import (
    donor_id_list_from_ecephys_session_ids)
from allensdk.internal.api.queries.utils import build_in_list_selector_query


def behavior_sessions_from_ecephys_session_ids(
        lims_connection: PostgresQueryMixin,
        ecephys_session_id_list: List[int]
) -> pd.DataFrame:
    """
    Get a DataFrame listing all of the behavior sessions that
    mice from a specified list of ecephys sessions went through

    Parameters
    ----------
    lims_connection: PostgresQueryMixin

    ecephys_session_id_list: List[int]
        The ecephys sessions used to find the mice used to find
        the behavior sessions

    Returns
    -------
    mouse_to_behavior: pd.DataFrame
        Dataframe with columns
            mouse_id
            behavior_session_id
            session_type
            date_of_acquisition
            date_of_birth
            ecephys_session_id
            genotype
            sex
            equipment_name
        listing every behavior session the mice in question went through
    """
    donor_id_list = donor_id_list_from_ecephys_session_ids(
                        lims_connection=lims_connection,
                        session_id_list=ecephys_session_id_list)

    query = f"""
    SELECT
      donors.external_donor_name as mouse_id
      ,behavior.id as behavior_session_id
      ,behavior.stimulus_name as session_type
      ,behavior.date_of_acquisition as date_of_acquisition
      ,behavior.ecephys_session_id as ecephys_session_id
      ,donors.date_of_birth as date_of_birth
      ,donors.full_genotype as genotype
      ,genders.name as sex
      ,equipment.name as equipment_name
    FROM donors
    JOIN behavior_sessions AS behavior
      ON behavior.donor_id = donors.id
    JOIN genders
      ON genders.id = donors.gender_id
    JOIN equipment
      ON equipment.id = behavior.equipment_id
    {build_in_list_selector_query(
        col='donors.id',
        valid_list=donor_id_list
    )}
    """

    mouse_to_behavior = lims_connection.select(query)
    return mouse_to_behavior