Data Retrieval: querying germplasms

From ICISWiki

Jump to: navigation, search

Germplasm Queries: (datatype id: GermplasmBaseConstants.DATATYPE_ID)

Getting by ID:

Searchable attribute id: org.generationcp.ontology.data.germplasm.GermplasmBaseConstants.UNIQUE_IDENTIFIER_DATATYPE_ATTRIBUTE_ID

SQL query:

SELECT g.gid, n2.nval, g.gdate FROM germplsm g LEFT JOIN names as n2 on g.gid = n2.gid and n2.nstat = 1 WHERE g.grplce = 0 AND g.gid = ?


Getting by preferred name:

Searchable attribute id: org.generationcp.ontology.data.germplasm.GermplasmBaseConstants.NAME_DATATYPE_ATTRIBUTE_ID

SQL query:

Using Operator.EQUAL

SELECT DISTINCT g.gid, n2.nval, g.gdate FROM germplsm g, names n2 WHERE n2.nval=? and n2.nstat = 1 AND g.grplce = 0 and g.gid = n2.gid

Using Operator.LIKE

SELECT DISTINCT g.gid, n2.nval, g.gdate FROM germplsm g, names n2 WHERE n2.nval like ? and n2.nstat = 1 AND g.grplce = 0 and g.gid = n2.gid



Getting by name of country of origin:

Searchable attribute id: org.generationcp.ontology.data.germplasm.GermplasmBaseConstants.LOCATIONS_DATATYPE_ATTRIBUTE_ID +
org.generationcp.core.ontology.LSID.ATTRIBUTE_DELIMITER +
org.generationcp.ontology.data.germplasm.GermplasmLocationBaseConstants.NAME_DATATYPE_ATTRIBUTE_ID

SQL query:

Using Operator.EQUAL

SELECT g.gid, n2.nval, g.gdate FROM ((germplsm g inner join names as n2 on g.gid = n2.gid and n2.nstat = 1) INNER JOIN (location loc inner join cntry ctry on loc.locid = ctry.cntryid and
ctry.isoabbr = ?) ON g.glocn = loc.locid) WHERE g.grplce = 0

Using Operator.LIKE

SELECT g.gid, n2.nval, g.gdate FROM ((germplsm g inner join names as n2 on g.gid = n2.gid and n2.nstat = 1) INNER JOIN (location loc inner join cntry ctry on loc.locid = ctry.cntryid and
ctry.isoabbr like ?) ON g.glocn = loc.locid) WHERE g.grplce = 0


Getting by alternate name:

Searchable attribute id: org.generationcp.ontology.data.germplasm.GermplasmBaseConstants.SYNONYMS_DATATYPE_ATTRIBUTE_ID +
org.generationcp.core.ontology.LSID.ATTRIBUTE_DELIMITER +
org.generationcp.ontology.core.identification.IdentifierSetBaseConstants.IDENTIFIERS_DATATYPE_ATTRIBUTE_ID +
org.generationcp.core.ontology.LSID.ATTRIBUTE_DELIMITER +
org.generationcp.ontology.core.identification.SimpleIdentifierBaseConstants.NAME_DATATYPE_ATTRIBUTE_ID

SQL query:

Using Operator.EQUAL

SELECT g.gid, n1.nval, g.gdate FROM germplsm g JOIN names n1 ON g.gid = n1.gid AND n1.nstat = 1 JOIN names n2 ON g.gid = n2.gid WHERE n2.nval = ? AND g.grplce = 0

Using Operator.LIKE

SELECT g.gid, n1.nval, g.gdate FROM germplsm g JOIN names n1 ON g.gid = n1.gid AND n1.nstat = 1 JOIN names n2 ON g.gid = n2.gid WHERE n2.nval like ? AND g.grplce = 0

Getting by preferred id:

Searchable attribute id: GermplasmExtConstants.PREFERRED_ID_DATATYPE_ATTRIBUTE_ID

SQL query:

Using Operator.EQUAL

SELECT g.gid, n1.nval, g.gdate FROM germplsm g JOIN names n1 ON g.gid = n1.gid AND n1.nstat = 1 JOIN names n2 ON g.gid = n2.gid AND n2.nstat = 8 WHERE n2.nval = ? AND g.grplce = 0

Using Operator.LIKE

SELECT g.gid, n1.nval, g.gdate FROM germplsm g JOIN names n1 ON g.gid = n1.gid AND n1.nstat = 1 JOIN names n2 ON g.gid = n2.gid AND n2.nstat = 8 WHERE n2.nval like ? AND g.grplce = 0


Getting attributes of Germplasm

Getting annotations (Germplasm.getAnnotations()):

SQL query:

SELECT DISTINCT atb.aid, g.gid, atb.atype, m.mname, m.mtype FROM germplsm g LEFT JOIN atributs atb ON g.gid = atb.gid LEFT JOIN methods m ON g.methn = m.mid WHERE g.gid = ?

Getting values of annotations (Germplasm.getAnnotations().getValues() – for annotations which are Feature objects):

SQL query:

SELECT u.fcode, u.fname, a.aval, a.adate FROM udflds u INNER JOIN atributs a ON u.fldno = a.atype WHERE a.gid = ? AND a.aid = ?

Getting relationships (Germplasm.getRelationships()):

SQL query:

SELECT DISTINCT g.gid, g.gnpgs FROM germplsm g WHERE g.gid=?


Getting relationships objects (Germplasm.getRelationships().getObjects()):

The query changes, depending on the type of EntityRelationship object and if the warehouse tables for germplasm pedigree are being used.

For type - OntologyTermCatalog.GERMPLASM_FEMALE_PARENT_TERM_ID or OntologyTermCatalog.GERMPLASM_PEDIGREE_GROUP_TERM_ID:

SELECT g.gpid1, n.nval FROM germplsm g LEFT JOIN names n ON g.gpid1 = n.gid AND n.nstat = 1 INNER JOIN germplsm p on p.gid = g.gpid1 WHERE p.grplce = 0 AND g.gid = ?

For type - OntologyTermCatalog.GERMPLASM_MALE_PARENT_TERM_ID or OntologyTermCatalog.GERMPLASM_PEDIGREE_SOURCE_TERM_ID:

SELECT g.gpid2, n.nval FROM germplsm g LEFT JOIN names n ON g.gpid2 = n.gid AND n.nstat = 1 INNER JOIN germplsm p on p.gid = g.gpid2 WHERE p.grplce = 0 AND g.gid = ?

For type - OntologyTermCatalog.GERMPLASM_PARENTS_TERM_ID

SELECT p.pid, n.nval FROM progntrs p LEFT JOIN names n on p.pid = n.gid AND n.nstat = 1 WHERE p.gid = ?

For type - OntologyTermCatalog.GERMPLASM_DERIVATIVE_CHILDREN_TERM_ID

Using warehouse table:

SELECT c.chgid, c.nval FROM wh_der_children c WHERE c.gid = ?

not using warehouse tables:

SELECT g.gid, n.nval, m.mtype FROM germplsm g LEFT JOIN names n on g.gid = n.gid and n.nstat = 1 LEFT JOIN methods m on g.methn = m.mid WHERE g.gnpgs < 0 AND (g.gpid2 = ? OR (g.gpid1 = ? AND g.gpid2 = 0))
AND g.grplce = 0


For type - OntologyTermCatalog.GERMPLASM_MAINTENANCE_CHILDREN_TERM_ID

Using warehouse table:

SELECT c.chgid, c.nval FROM wh_man_children c WHERE c.gid = ?

Not using warehouse table:

SELECT g.gid, n.nval FROM germplsm g LEFT JOIN names n on g.gid = n.gid and n.nstat = 1 LEFT JOIN methods m on g.methn = m.mid WHERE m.mtype = 'MAN' AND (g.gpid2 = ? OR (g.gpid1 = ? AND g.gpid2 = 0))
AND g.grplce = 0

Getting locations (Germplasm.getLocations()):

SQL query:

SELECT DISTINCT ctry.isotwo, ctry.isoabbr, loc.lname, loc.locid, ctry.cntryid, locd.dval FROM cntry ctry JOIN location loc ON loc.cntryid = ctry.cntryid JOIN locdes locd on loc.locid = locd.locid
JOIN germplsm g on g.glocn = loc.locid WHERE g.gid = ?

and

SELECT a.aval FROM atributs a JOIN udflds u ON a.atype = u.fldno AND u.fcode = 'ORI_COUN' WHERE a.gid = ?

Getting location details (Germplasm.getLocations().getValues()):

SQL query:

SELECT geo.lat, geo.lon, geo.alt FROM georef geo WHERE geo.locid = ?


Getting alternate names (Germplasm.getSynonyms().getIdentifiers()):

SQL query:

SELECT DISTINCT n.nid, n.nval, u.fname, n.ntype, n.nstat FROM names n, udflds u WHERE n.gid=?
and n.nstat <> 9 and u.fldno = n.ntype

Getting alternate names details (Germplasm.getSynonyms().getIdentifiers().getAnnotations()):

SQL query:

select n.nstat, ndate, l.lname, u.fcode, n.ntype from names n left join location l on n.nlocn = l.locid left join udflds u on u.fldno = n.ntype where n.nid = ?

Getting primary citation (Germplasm.getPrimaryCitation()):

SQL query:

SELECT bref.analyt, bref.refid, u.fcode, bref.authors, bref.publish, bref.pucntry, u.fname FROM germplsm g JOIN bibrefs bref ON g.gref = bref.refid JOIN udflds u ON bref.pubtype = u.fldno WHERE g.gid = ?



Retrieved from "http://www.cropinfo.org/icis/index.php/Data_Retrieval:_querying_germplasms"
Personal tools