aboutsummaryrefslogtreecommitdiff
path: root/doc/database.org
diff options
context:
space:
mode:
authorBonfaceKilz2022-02-09 16:52:00 +0300
committerBonfaceKilz2022-02-09 16:52:00 +0300
commit2c22e593c59a9b4f9129a2e669443709d9c5154a (patch)
treea4c03d558c01c2f7e1ceb8c37ad3607b20c1c441 /doc/database.org
parent78a544ea268c28b34c2761e6d65465882578d235 (diff)
downloadgenenetwork2-2c22e593c59a9b4f9129a2e669443709d9c5154a.tar.gz
doc: Document how to fetch case-attribute data from a sample-list
Diffstat (limited to 'doc/database.org')
-rw-r--r--doc/database.org21
1 files changed, 21 insertions, 0 deletions
diff --git a/doc/database.org b/doc/database.org
index d5462d4e..99d2905a 100644
--- a/doc/database.org
+++ b/doc/database.org
@@ -1370,6 +1370,27 @@ Empty set (0.00 sec)
Hmmm. This is the test database. Then there are the plink files and VCF files.
+** How to fetch case attribute data
+
+To fetch case attribute data, you need the PublixshXRef Id and it's
+PhenotypeId. Run the following query (as an example):
+
+#+begin_src sql
+SELECT concat(st.Name, ',', ifnull(pd.value, 'x'), ',',
+ ifnull(ps.error, 'x'), ',', ifnull(ns.count, 'x')) as 'Data',
+ ifnull(ca.Name, 'x') as 'CaseAttr', ifnull(cxref.value, 'x') as 'Value'
+FROM PublishFreeze pf
+JOIN PublishXRef px ON px.InbredSetId = pf.InbredSetId
+JOIN PublishData pd ON pd.Id = px.DataId
+JOIN Strain st ON pd.StrainId = st.Id
+LEFT JOIN PublishSE ps ON ps.DataId = pd.Id AND ps.StrainId = pd.StrainId
+LEFT JOIN NStrain ns ON ns.DataId = pd.Id AND ns.StrainId = pd.StrainId
+LEFT JOIN CaseAttributeXRefNew cxref ON
+ cxref.InbredSetId = px.InbredSetId
+LEFT JOIN CaseAttribute ca ON ca.Id = cxref.CaseAttributeId
+WHERE px.Id = 10006 AND px.PhenotypeId = 28409 LIMIT 10;
+#+end_src
+
* Optimize SQL?
We were facing some issues with slow queries. A query