"Distinct" is implicitly added to the query because there is a join on
to-many relationship. Without "distinct" there will be multiple rows in
the result set for each "Issue" that has more than one "CategoryIssue".
But the limitation of such approach that you've mentioned is valid. I
need to think how to address such cases. One solution would be to
replace joins with subquery; however subqueries are not universally
supported by all RDBMS... Another strategy would be to apply "distinct"
on the Cayenne side. I guess this might be a better solution.
Feel free to open a bug report on that.
Thanks
Andrus
On Mar 31, 2004, at 6:06 AM, Bill Fan wrote:
> Hi,
>
> I'm using the latest Cayenne 1.0.7
>
> I have the following simple table relationship, a category may have
> many issues and an issue may belongs to different categories,
>
> Category -->CategoryIssue<--Issue
>
> With the following code, the "SELECT DISTINT ..." sql statement was
> generated. It caused some problems as some of the columns in the issue
> table is type of text ... [Microsoft][SQLServer 2000 Driver for
> JDBC][SQLServer]The text, ntext, or image data type cannot be selected
> as DISTINCT.
>
>
> ============================================================
> Expression qualifier =
> ExpressionFactory.binaryPathExp(Expression.EQUAL_TO,
> "categoryissueArray.toCategory.categoryNumber",
> categoryNumber);
>
> // if the "Closed" voc's are not to be included...
> if (!isIncludingClosed)
> {
> qualifier = qualifier.andExp( ExpressionFactory.binaryPathExp(
> Expression.NOT_EQUAL_TO,
> "toIssuestatus.issueStatusDesc",
> "Closed") );
> }
>
> SelectQuery query = new SelectQuery(Issue.class, qualifier);
>
> Ordering ordering = new Ordering("issueNumber", Ordering.DESC);
>
> query.addOrdering(ordering);
>
> // set a relatively high logging level, to show the query execution
> progress
> query.setLoggingLevel(Level.WARN);
>
> List list = ctxt.performQuery(query);
> ============================================================
>
>
> The full sql statement generated is something like this,
>
> SELECT DISTINCT t0.DateModified, t0.DateSubmitted,
> t0.IssueDescription, t0.IssueName, t0.IssueNumber, t0.IssueId....FROM
> dbo.ISSUE t0, dbo.CATEGORYISSUE t1, dbo.CATEGORY t2, dbo.ISSUESTATUS
> t3 WHERE t0.IssueId = t1.IssueId AND t1.CategoryId = t2.CategoryId AND
> t0.IssueStatusId = t3.IssueStatusId AND ((t2.CategoryNumber = ?) AND
> (t3.IssueStatusDesc <> ?)) ORDER BY t0.IssueNumber DESC [bind: 3,
> 'Closed']
>
> I believe that the default sql statement generated should be with
> DISTINCT set to fales unless it is explicitly set via the statement of
>
> query.setDistinct(true);
>
> Any idea?
>
>
> Thanks for help!
>
>
> Bill
This archive was generated by hypermail 2.0.0 : Wed Mar 31 2004 - 10:15:43 EST