I am having a problem with setDistinctMode() in Transfer and am trying
to figure out the correct way to using distinct with joins.
I am running a query with TQL:
<cfset var qList = "" />
<cfset var tQuery = "" />
<cfsavecontent variable="qList">
<cfoutput>
FROM tags.tag
join bookmarks.bookmark as bookmark
join bookmarklinks.bookmarklink as bookmarklink ON
bookmark.bookmarkLink
join links.link as link ON bookmarklink.link
WHERE link.userID = :userID
</cfoutput>
</cfsavecontent>
<cfset tQuery = variables.transfer.createQuery(qList) />
<cfset tQuery.setDistinctMode(true) />
<cfset tQuery.setParam("userID",arguments.userID) />
<cfreturn variables.transfer.listByQuery(tQuery) />
This creates SQL of:
select distinct tags.tagID, tags.tagTitle, bookmark.title,
bookmark.sortOrder, bookmark.status, bookmark.bookmarkID,
bookmarklink.linkNote, bookmarklink.linkTitle, bookmarklink.sortOrder,
bookmarklink.bookmarkLinkID, link.linkURL, link.linkTitle,
link.userID, link.linkID FROM tags inner join taglinks on
taglinks.tagID = tags.tagID inner join bookmarks bookmark ON
taglinks.bookmarkID = bookmark.bookmarkID inner join bookmarklinks
bookmarklink ON bookmark.bookmarkLinkID = bookmarklink.bookmarkLinkID
inner join links link ON bookmarklink.linkID = link.linkID WHERE
link.userID = '1'
Since I have the setDistinctMode(true) parameter, I was expecting to
get distinct tags. Instead, my query contains multiple references to
the same tagID
results:
id title
1 sports
3 funny
1 sports
1 sports
2 news
Sports should only be in the query once.
When I change the SQL to:
select distinct tags.tagID, tags.tagTitle FROM tags inner join
taglinks on taglinks.tagID = tags.tagID inner join bookmarks bookmark
ON taglinks.bookmarkID = bookmark.bookmarkID inner join bookmarklinks
bookmarklink ON bookmark.bookmarkLinkID = bookmarklink.bookmarkLinkID
inner join links link ON bookmarklink.linkID = link.linkID WHERE
link.userID = '1'
I run the query manually, without Transfer and then the results are:
id title
1 sports
3 funny
2 news
So, it works when I remove all the extra stuff that came before the
FROM.
Basically I'm asking... how can I do a distinctMode select while using
joins? When I use joins the extra:
bookmark.title, bookmark.sortOrder, bookmark.status,
bookmark.bookmarkID, bookmarklink.linkNote, bookmarklink.linkTitle,
bookmarklink.sortOrder, bookmarklink.bookmarkLinkID, link.linkURL,
link.linkTitle, link.userID, link.linkID
is added before the FROM, which causes the distinct not to work.
Simply removing these lines and using a regular SQL query works, but
surely there is a way to do this in Transfer?
All I want before FROM is:
select distinct tags.tagID, tags.tagTitle FROM
The other stuff causes the distinct not to work, but the joins are
required for getting the proper data. I just can't figure out how to
have Transfer let me still use the join without adding all of the
extra columns before FROM.
Thanks!
--~--~---------~--~----~------------~-------~--~----~
Before posting questions to the group please read:
http://groups.google.com/group/transfer-dev/web/how-to-ask-support-questions-on-transfer
You received this message because you are subscribed to the Google Groups
"transfer-dev" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/transfer-dev?hl=en
-~----------~----~----~----~------~----~------~--~---
