Project

General

Profile

« Previous | Next » 

Revision 68d6d316

Added by Patrick Plitzner over 5 years ago

ref #5458 Fix and streamline SQL distribution query

View differences:

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