MySQL: counts of multiple sub-items of each parent

Sometimes it is useful to show in a list the numbers of children each object has. For instance how many Products are in each Category.

That’s simple enough to do when only one child type is used:

SELECT
 categories.title, categories.id,
 count(products.id) as productCount
FROM
 categories
   INNER JOIN products ON categories.id = products.categoryid

However, if you wanted to count two separate children types, the same method of querying will give you very odd results:

SELECT
 categories.title, categories.id,
 count(products.id) as productCount, count(images.id) as imagesCount
FROM
 categories
   INNER JOIN products ON categories.id = products.categoryid
   INNER JOIN images ON categories.id = images.categoryid

The lazy programmer would get around this by using the server language – looping through a simple query of the category, and with each row run another query to count the products (and another for the images). Yuck.

Instead, I found this method to work quite well:

SELECT
 cats.title AS title, cats.ID AS id,
 COALESCE(prods.howmany,0) AS productCount,
 COALESCE(imgs.howmany,0) AS imageCount
FROM (
 SELECT title, ID
 FROM categories
) AS cats
LEFT JOIN (
 SELECT
   count(products.id) as howmany,
   products.categoryid as categoryid
 FROM products
   LEFT JOIN categories ON products.categoryid = categories.id
 GROUP BY products.categoryid
) AS prods ON cats.id=prods.categoryid
LEFT JOIN (
 SELECT
   count(images.id) as howmany,
   images.categoryid as categoryid
 FROM images
   LEFT JOIN categories ON images.categoryid = categories.id
 GROUP BY images.categoryid
) AS imgs ON cats.id=imgs.categoryid

Note the use of the ‘COALESCE()’ function so that nulls are returned as a 0 (zero).
As you can see the database is still running three queries. But they are all within one connection, and the server language didn’t do any work.  Then MySQL joins those queries together into one result, easily parsed by your server language of choice. Here, ColdFusion:

<table>
 <tr>
   <td>Category</td>
   <td>Products</td>
   <td>Images</td>
 </tr>
 <cfoutput query="read">
 <tr>
   <td>#title#</td>
   <td>#productCount#</td>
   <td>#imageCount#</td>
 </tr>
 </cfoutput>
</table>

Making Apple’s DigitalColor Meter useful

DigitalColorMeter
I had a long time gripe about the DigitalColor Meter that ships with Mac OS X. It would be nice to use it for web development when you want to copy a color from an image to make text of the same color with CSS. But the way this utility works drove me nuts…

Setting the pulldown to “RGB as Hex Value, 8-bit” sounds right. Then put the aperture over the color you want, hit command+shift+c and you have the hex rgb in your clipboard. Nice! So go paste that into your css editor and you get:

Note the quotes
Note the quotes

So now I have to go in and remove the quotes manually each time. But then, it still does not work when you view the web page. Turning on hidden characters reveals…

What a mess
What a mess

“… just… like… WHY, Steve?” An otherwise nice utility is a real pain to do anything useful with.

Well, tonight I found a good workaround for this. In the preferences, turn on “Drag in swatch drags out the color”:
Picture 5 Then, Instead of command+shift+c, do a command+shift+h. That holds the color instead of copying it:

Picture 4 Then drag that chip in the middle to where you want to copy the hex…

Picture 8

And there you have it:

Picture 7

Now… as to why it is holding F39200 but dragged F88D00? I have no idea! Comments, suggestions, etc are welcome.

Watch a site being developed – the webveteran.com experiment

I had this crazy idea, and  I think I’m going to try it! I started to build a new website for myself… long overdo from almost three years ago. And I figured, ‘why not do so publicly?’ Sure it’s like dropping my drawers, but I’m confident although I know I am not the absolute best. But I think it would be interesting… at least, I think it would be to watch someone else do so.

The completed site will be/have:

  • web2.0 layout and functionality
  • coldFusion, ajax, scriptaculous, javascript sound
  • custom cms with plugin architecture (I might open this up as well)
  • seo + sef urls
  • one page load navigation (ajax)

So without further wait, please visit 2009.webveteran.com to take part in the experiment. Comment and criticisms are welcome and appreciated.

Keep in mind, this is a site entirely in development! What you will see occasionally:

  • broken links
  • broken content
  • dummy text
  • images as test text

Heck, you might even see a table or two (gasp). You’ll probably also see me change libraries every now and then (as was the case with the scroller). And of course there may be days without any progress whatsoever. Subscribe to this blog or my twitter account to keep updated!

[sigh] here we go…

CFup build 090624 – now on github

It’s been asked that I release the source of CFup. Initially I was not comfortable doing so as I threw it together as a test of concept. Certainly nt my best work! Feeling inspired I cleaned up some of the code – mostly just so the ajax routines don’t clash – and uploaded it to github. Now anyone can download it, try it out, and contribute. I’m excited to see what optimizations and cleanups can be done to it. So if you’re interested in kicking the tires or helping develop it, check it out on github: http://github.com/WebVeteran/CFup/

ColdFusion clips for Coda

I have made quite a few Clips in Coda to make development a little speedier. Hopefully you can get some use out of them too.

Coda Clips
Preview of Coda Clips - keep in mind the clips window only shows the first line of each clip.

Download the Clips here:
ColdFusion Coda Clips

CF TinyMCE 090612 – automatically handles multiple instance per page

I updated CF tinyMCE with a small nicety. It now automatically handles multiple instance per page. Previously, it would have to be invoked like so:

<cfoutput>
<tr>
	<td>
		#application.includes.tinymce(
			content=read.en,
			element="en",
			csswidth="444px",
			resizing=true,
			theme='advanced',
			theme_advanced_buttons1 = "bold,italic,underline,|,justifyleft,justifycenter,justifyright,justifyfull,|,formatselect,|,bullist,numlist,|,undo,redo,|,code,cleanup",
			theme_advanced_buttons2 = "",
			theme_advanced_buttons3 = ""

		)#
	</td>
	<td>
		#application.includes.tinymce(
			content=read.es,
			element="es",
			csswidth="444px",
			resizing=true,
			theme='advanced',
			loadbase='0',
			language='es',
			theme_advanced_buttons1 = "bold,italic,underline,|,justifyleft,justifycenter,justifyright,justifyfull,|,formatselect,|,bullist,numlist,|,undo,redo,|,code,cleanup",
			theme_advanced_buttons2 = "",
			theme_advanced_buttons3 = "",
			gecko_spellcheck = "false"
		)#
	</td>
</tr>
</cfoutput>

Notice the ‘loadbase=0’ argument on the second invocation. That told the previous version of CF TinyMCE to not all of TinyMCE again – just load the settings instead.

The new version automatically handles multiple instances and not loading TinyMCE all over again for subsequent invocations on the same page. Therefore, the new syntax example is:

<cfoutput>
<tr>
	<td>
		#application.includes.tinymce(
			VScope=Variables,
			content=read.en,
			element="en",
			csswidth="444px",
			resizing=true,
			theme='advanced',
			theme_advanced_buttons1 = "bold,italic,underline,|,justifyleft,justifycenter,justifyright,justifyfull,|,formatselect,|,bullist,numlist,|,undo,redo,|,code,cleanup",
			theme_advanced_buttons2 = "",
			theme_advanced_buttons3 = ""

		)#
	</td>
	<td>
		#application.includes.tinymce(
			VScope=Variables,
			content=read.es,
			element="es",
			csswidth="444px",
			resizing=true,
			theme='advanced',
			language='es',
			theme_advanced_buttons1 = "bold,italic,underline,|,justifyleft,justifycenter,justifyright,justifyfull,|,formatselect,|,bullist,numlist,|,undo,redo,|,code,cleanup",
			theme_advanced_buttons2 = "",
			theme_advanced_buttons3 = "",
			gecko_spellcheck = "false"
		)#
	</td>
</tr>
</cfoutput>

No more loadbase! The key is the argument “Vscope=Variables”. That lets CF pass the Variables scope back and forth between a page and a function. It is a required argument which I am not happy about. But since it’s value never changes it’s not so bad.

Because of this fundamental change, CF TinyMCE 090612 is not backwards compatible. If anyone knows how to pass the Variables scope back and forth between a page and a function, without an argument like I am here – please let me know!

You can download CF TinyMCE at github: http://github.com/WebVeteran/cf-tinymce/

A better Fix for IE6 and gzip compressed javascripts

In previous posts I found a fix for an intermittent problem with IE6 not loading compressed javascripts. IE was loading the first zero bytes of the gzipped javascripts. But if you copy-paste the url of the javascript it loads fine. And then the pages with those javascripts work just fine from there on. That fix was to exclude IE6 from receiving the compressed version.

Well, this fix is much better. Just add blank Pragma and Cache-control headers. No need to exclude IE6 from receiving the compressed js.

<cfif cgi.HTTP_ACCEPT_ENCODING contains "gzip">
	<cfheader name="Content-Encoding" value="gzip" >
	<cfheader name="Content-Disposition" value="inline; filename=""protoaculous182min.jgz""">
	<cfheader name="Content-Type" value="application/x-javascript; charset=ISO-8859-1">
	<cfif cgi.HTTP_USER_AGENT contains 'MSIE 6.0; Windows'>
		<cfheader name="Pragma" value="">
		<cfheader name="Cache-control" value="">
	</cfif>
	<cfcontent deletefile="no" file="#expandpath('./protoaculous182min.jgz')#" type="application/x-javascript; charset=ISO-8859-1">
<cfelse>
	<cfheader name="Content-Disposition" value="inline; filename=""protoaculous182min.js""">
	<cfheader name="Content-Type" value="application/x-javascript; charset=ISO-8859-1">
	<cfinclude template="protoaculous182min.js">
</cfif>