Revision 68d6d316
Added by Patrick Plitzner over 7 years ago
src/main/java/eu/etaxonomy/cdm/vaadin/util/CdmQueryFactory.java | ||
---|---|---|
13 | 13 |
import java.util.Iterator; |
14 | 14 |
import java.util.List; |
15 | 15 |
|
16 |
import org.apache.commons.lang.StringUtils; |
|
17 |
|
|
16 | 18 |
import com.vaadin.data.util.sqlcontainer.query.FreeformQuery; |
17 | 19 |
import com.vaadin.data.util.sqlcontainer.query.QueryDelegate; |
18 | 20 |
|
... | ... | |
56 | 58 |
} |
57 | 59 |
|
58 | 60 |
public static QueryDelegate generateTaxonDistributionQuery(List<String> termList, List<Integer> taxonNodeIds) throws SQLException { |
61 |
|
|
59 | 62 |
String idString = ""; |
60 | 63 |
Iterator<Integer> nodeIterator = taxonNodeIds.iterator(); |
61 | 64 |
while (nodeIterator.hasNext()) { |
... | ... | |
66 | 69 |
} |
67 | 70 |
} |
68 | 71 |
String FROM_QUERY = |
69 |
" FROM TaxonNode tn " + |
|
70 |
"INNER JOIN TaxonBase tb on tn.taxon_id = tb.id " + |
|
71 |
"INNER JOIN Classification cl ON tn.classification_id = cl.id " + |
|
72 |
"LEFT OUTER JOIN TaxonNameBase tnb ON tnb.id=tb.id " + |
|
73 |
"LEFT OUTER JOIN DescriptionBase db ON db.taxon_id=tb.id " + |
|
74 |
"LEFT OUTER JOIN (SELECT indescription_id, area_id, status_id, DTYPE, id FROM DescriptionElementBase deb WHERE deb.DTYPE LIKE 'Distribution') AS deb ON deb.indescription_id=db.id " + |
|
75 |
"LEFT OUTER JOIN DefinedTermBase dtb on deb.status_id=dtb.id " + |
|
76 |
"LEFT OUTER JOIN DefinedTermBase dtb1 on deb.area_id=dtb1.id " + |
|
77 |
"LEFT OUTER JOIN DefinedTermBase dtb2 on tnb.rank_id = dtb2.id " + |
|
78 |
"WHERE tn.id IN ("+ idString +") AND tb.DTYPE = 'Taxon'" ; |
|
79 |
|
|
80 |
String GROUP_BY = " GROUP BY tn.id "; |
|
72 |
"FROM TaxonNameBase tnb " |
|
73 |
+ "INNER JOIN TaxonBase tb on tnb.id = tb.name_id and tb.DTYPE='Taxon'" + // # name<->taxon |
|
74 |
"INNER JOIN TaxonNode tn on tn.taxon_id = tb.id "+ |
|
75 |
"INNER JOIN DefinedTermBase rank on tnb.rank_id = rank.id "+// # rank <-> name |
|
76 |
"LEFT OUTER JOIN DescriptionBase descr on descr.taxon_id = tb.id "+// # taxon <-> taxon description (not every taxon has a description) |
|
77 |
"LEFT OUTER JOIN DescriptionElementBase descrEl on descrEl.indescription_id = descr.id and descrEl.DTYPE = 'Distribution' "+// # distribution <-> description |
|
78 |
"LEFT OUTER JOIN DefinedTermBase statusTerm on statusTerm.id = descrEl.status_id "+ |
|
79 |
"LEFT OUTER JOIN DefinedTermBase area on area.id = descrEl.area_id "+ |
|
80 |
"WHERE tn.id IN ("+ idString +") "; |
|
81 |
|
|
82 |
String GROUP_BY = " GROUP BY tb.uuid, tn.id "; |
|
81 | 83 |
|
82 | 84 |
String ORDER_BY = " ORDER BY tb.titleCache "; |
83 | 85 |
|
84 |
String SELECT_QUERY= |
|
85 |
"SELECT tb.DTYPE," +
|
|
86 |
"tb.id, " +
|
|
87 |
"tb.uuid, " +
|
|
88 |
"tn.classification_id, " +
|
|
86 |
String SELECT_QUERY= "SELECT "
|
|
87 |
+ "tb.DTYPE, "
|
|
88 |
+ "tb.id, "
|
|
89 |
+ "tb.uuid, "
|
|
90 |
+ "tn.classification_id, "+
|
|
89 | 91 |
"tb.titleCache AS "+TAXON_COLUMN+", " + |
90 |
"dtb2.titleCache AS "+RANK_COLUMN+", ";
|
|
92 |
"rank.titleCache AS "+RANK_COLUMN+", ";
|
|
91 | 93 |
|
92 |
int count = termList.size(); |
|
93 | 94 |
for(String term : termList){ |
94 |
if(count == 1){ |
|
95 |
SELECT_QUERY= SELECT_QUERY + |
|
96 |
"MAX( IF(dtb1.titleCache = '"+ term +"', dtb.titleCache, NULL) ) as '"+ term +"' " ; |
|
97 |
}else{ |
|
98 |
SELECT_QUERY= SELECT_QUERY + |
|
99 |
"MAX( IF(dtb1.titleCache = '"+ term +"', dtb.titleCache, NULL) ) as '"+ term +"'," ; |
|
100 |
} |
|
101 |
count--; |
|
95 |
SELECT_QUERY += "MAX( IF(area.titleCache = '"+ term +"', statusTerm.titleCache, NULL) ) as '"+ term +"'," ; |
|
102 | 96 |
} |
97 |
SELECT_QUERY = StringUtils.stripEnd(SELECT_QUERY, ","); |
|
103 | 98 |
SELECT_QUERY= SELECT_QUERY + FROM_QUERY + GROUP_BY + ORDER_BY; |
104 | 99 |
String COUNT_QUERY = "SELECT count(DISTINCT tb.id)" + FROM_QUERY; |
105 |
// String CONTAINS_QUERY = "SELECT * FROM TaxonNode tn WHERE tn.id = ?"; |
|
106 | 100 |
String CONTAINS_QUERY = "SELECT * FROM TaxonBase tb WHERE tb.uuid = ?"; |
107 | 101 |
|
108 | 102 |
return generateQueryDelegate(SELECT_QUERY, COUNT_QUERY, CONTAINS_QUERY); |
Also available in: Unified diff
ref #5458 Fix and streamline SQL distribution query