Micha Schopman wrote:
> In other words
>
> <cfquery name="showArticle" datasource="user020">
> SELECT A.*, Au.authorID, Au.authorFirstName, A,authorSurname
> FROM Articles A
> JOIN Authors Au ON (Au.AuthorID = A.AuthorID)
> WHERE A. articleID = '#url.updateID#
> </cfquery>
>
> Now you get authors * articles on records back. No problem with that.
>
> <cfset stArticles = structNew()>
> <cfloop query="showArticle">
> <!--- create first time structure for this specific article --->
> <cfif NOT structKeyExists(stArticles,"art_"&articleID)>
> <cfset stArticles["art_"&articleID] = arrayNew(1)>
> </cfif>
> <!--- add this author to the structure for this article --->
> <cfset arrayAppend(stArticles["art_"&articleID],authorID)>
> </cfloop>
>
> something like that .. now you can output all data with cfloop
> collection for example :) You make the calls.
There's a few problems with that:
1. You're only getting back the authors with published articles,
something that isn't guaranteed to be true.
2. You're doing something that the DBMS can do better and faster
than you can when you do that loop. Seriously, querying the DB
and caching the authors recordset is a better alternative to doing
that if they're used a lot. And even if they're not, it makes sense
to do the article and authors queries seperately. They're logically
two different datasets.
3. Joins are more expensive than you think.
4. You're going to end up doing two loops over the dataset needlessly.
If you're doing a join, an left outer join is the only kind that makes
any sense in this case. You need all the articles and all the authors.
At best, you'll end up with m records (the number of authors being less
than the number of articles, and each author having written at least one
article), and at worst (m + n - 1) (all articles written by the one
author, with a bunch of other authors with no articles assigned to
them). And then you have to loop over the whole thing to tear the
record sets apart again! And resort the authors list!
No, what makes sense is two seperate queries:
----
<cfquery name="articles" datasource="dsn">
SELECT author_id, article_title, date_posted, article_body
FROM articles
WHERE article_id =
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#URL.authorId#">
</cfquery>
<cfquery name="authors" datasource="dsn"
cachedwithin="#CreateTimeSpan(0, 0, 0, 5)#">
SELECT author_id, CONCAT(author_fname, ' ', author_lname) AS name
FROM authors
ORDER BY author_lname, author_fname
</cfquery>
<cfoutput query="articles">
...output article details...
<!---
Build a dropdown list of all the authors, with the article's
author marked by default.
--->
<select name="authorId">
<cfloop query="authors">
<option value="#author_id#"
<cfif articles.author_id EQ
article_id>selected="selected"</cfif>
>#name#</option>
</cfloop>
</cfoutput>
----
But if there's any doubt as to whether this is the best way of doing it,
then all I can say is *profile* *it*, but keep the code as simple as
possible and only complicate it if you *really* need to.
--
Keith Gaughan, Developer
Digital Crew Ltd., Pembroke House, Pembroke Street, Cork, Ireland
http://digital-crew.com/
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183849
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
