[Treebase-guts] SF.net SVN: treebase:[521] trunk/treebase-core/src/main/java/org/cipres/ treebase/dao/taxon/TaxonLabelDAO.java

[<yo...@us...>]

Revision: 521
          http://treebase.svn.sourceforge.net/treebase/?rev=521&view=rev
Author:   youjun
Date:     2010-03-01 15:49:04 +0000 (Mon, 01 Mar 2010)

Log Message:
-----------
optimize taxonlabeldao queries   

Modified Paths:
--------------
    trunk/treebase-core/src/main/java/org/cipres/treebase/dao/taxon/TaxonLabelDAO.java

Modified: trunk/treebase-core/src/main/java/org/cipres/treebase/dao/taxon/TaxonLabelDAO.java
===================================================================
--- trunk/treebase-core/src/main/java/org/cipres/treebase/dao/taxon/TaxonLabelDAO.java	2010-02-26 16:02:32 UTC (rev 520)
+++ trunk/treebase-core/src/main/java/org/cipres/treebase/dao/taxon/TaxonLabelDAO.java	2010-03-01 15:49:04 UTC (rev 521)
@@ -409,12 +409,13 @@
 		return result;
 
 	}
-
+    // need refactoring later
+	//all the sql statement should go to same file or class 
 	public Collection<Matrix> findMatrices(Taxon t) {
 		Query q = getSession()
-		.createQuery("select distinct m from Matrix m, TaxonLabel tl where " +
-				"tl member of m.taxa.taxonLabelList and tl.taxonVariant.taxon = :t");
-		q.setParameter("t", t);
+		.createSQLQuery("select distinct m.* from matrix m join matrixrow using(matrix_id) join taxonlabel " +
+				"using (taxonlabel_id) join taxonvariant using (taxonvariant_id) where taxon_id = :id").addEntity(Matrix.class);
+		q.setParameter("id", t.getId());
 		Collection<Matrix> result = q.list();
 		return result;
 	}
@@ -436,8 +437,11 @@
 	 */
 	public Collection<PhyloTree> findTrees(Taxon t) {
 		Query q = getSession()
-		.createQuery("select pt from PhyloTree pt, TaxonLabel tl where " +
-				"tl member of pt.treeBlock.taxonLabelSet.taxonLabelList and tl.taxonVariant.taxon = :t");
+		.createQuery("select distinct pt from PhyloTree pt inner join fetch pt.treeNodes tn where " +
+		"tn.taxonLabel.taxonVariant.taxon = :t"); 
+		
+		//("select pt from PhyloTree pt, TaxonLabel tl where " +
+		//		"tl member of pt.treeBlock.taxonLabelSet.taxonLabelList and tl.taxonVariant.taxon = :t");
 		q.setParameter("t", t);
 		Collection<PhyloTree> result = new HashSet<PhyloTree>();
 		for (Object o: q.list()) {  // Can't select distinct over phylotrees today 20081204 mjd


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.