Month: August 2006

SQL – Inner joins and limit…

Posted on by chris

I’ve been hard at work at the rockface of the new Wiblog system recently, and it seems that the more I get done the more there is to do. I met a bit of a problem tonight regarding tags (those lovely bits of folksonomy) which I think I’ve fixed. This one is quite hard to explain, and is only suitable for people who would wear a “geek” badge with pride, but I’ll do my best.

I have a MySQL database containing table with blog entries in it. That table is called ‘entries’. And I have another table with tags in it called ‘tags’. Each entry in the entries table has a unique ID, and each tag in the tag table is linked to the entry it belongs to using a field called ‘entryid’. So far so good.

However, I want to paginate (that is, split into multiple pages) my blog entries, showing 10 on a page then a link to the next page of 10. That’s great as long as there are zero or one tags linked to each entry. The code would look something like this:

select e.id, e.title, e.body, t.tag
from entries e
left outer join tags t on t.entryid = e.id
order by e.id desc
limit 0, 10

Those of you with some SQL knowledge will see my problem. When any particular entry has more than one tag linked to it, the ‘limit’ clause will not work correctly. Rather than bring back the top 10 entries, it will bring back the top 9, with one of thm (the one with two tags) twice. Once for each tag. Let me illustrate:

Output for the above code with one tag per entry:

id title body tag
100 100 title 100 body 100 tag
99 99 title 99 body 99 tag
98 98 title 98 body 98 tag
97 97 title 97 body 97 tag
96 96 title 96 body 96 tag
95 95 title 95 body 95 tag
94 94 title 94 body 94 tag
93 93 title 93 body 93 tag
92 92 title 92 body 92 tag
91 91 title 91 body 91 tag

Output for the above code where entry ID 100 has two tags:

id title body tag
100 100 title 100 body 100 tag
100 100 title 100 body 100 another tag
99 99 title 99 body 99 tag
98 98 title 98 body 98 tag
97 97 title 97 body 97 tag
96 96 title 96 body 96 tag
95 95 title 95 body 95 tag
94 94 title 94 body 94 tag
93 93 title 93 body 93 tag
92 92 title 92 body 92 tag

I’ve made the second tag bold to make it obvious. So, where has ID 91 gone? Dropped off the end because of the second tag linked to ID 10, that’s where. No good, in fact it’s downright bad.

I’ve had an idea for how to fix this for a while, but tonight has been my first chance to get it working. I’m sure this isn’t unique, but I had a quick search and couldn’t find anything similar. So, here we go.

The important thing we need to know is what the highest and lowest IDs of entries are that we want to bring back. Once we know that it doesn’t matter how many tags we have linked to any entries, as we always know our list will be bound by the upper and lower limits.

Here’s the code, with (hopefully) clear instructions:

# select the fields we want
select e.id, e.title, e.body, t.tag
# fom the entries table
from entries e
# join the tags table
left outer join tags t on t.entryid = e.id
# here's where it gets interesting
# firstly, get the minimum entry ID and check we only get ID equal or greater
where (e.id>=(select min(l.id) from (select id
from entries
# make sure we order correctly
order by id desc
# set our limit (used for pagination)
limit 0, 10) as l))
# then get the maximum entry ID and check we only get ID equal or less
and (e.id
from entries
# make sure we order correctly
order by id desc
# set our limit (used for pagination)
limit 0, 10) as l))
# make sure we order correctly
order by e.id desc

So, the above code should give you:

id title body tag
100 100 title 100 body 100 tag
100 100 title 100 body 100 another tag
99 99 title 99 body 99 tag
98 98 title 98 body 98 tag
97 97 title 97 body 97 tag
96 96 title 96 body 96 tag
95 95 title 95 body 95 tag
94 94 title 94 body 94 tag
93 93 title 93 body 93 tag
92 92 title 92 body 92 tag
91 91 title 91 body 91 tag

ID 91 is back! And while we actually have 11 rows returned by the query, it’s pretty easy to parse it using PHP to make sure we don’t show the same entry details twice. The only thing to watch out for is that you get the ordering and limits the same in both the min() and max() statements. I hope that’s useful to you.

Wearing different hats…

Posted on by chris

It’s one of the main things I enjoy about my job that I have to wear different hats. Not real hats, of course, I’m talking about metaphorical hats signifying different roles. Some of these hats I enjoy wearing more than others, and some I absolutely resent even going near as they are way outside my responsibility. It seems like web guys are lumped alongside general IT people when they clearly aren’t the same breed. But I digress.

Here’s a quick rundown of the hats I wear, how much time I wear them (in percent), and how well-fitting they are to me.

Developer Hat: Worn 40% of the time

My main hat is the Developer Hat. It’s plain black with a very small purple PHP logo on it. It fits pretty well, although sometimes I see people wearing the same hat and it’s quite obvious that they were born wearing the Developer Hat and I only got my one relatively late in life.

What most people can’t see is the intricate and complex mesh of technologies that make up the inside of the hat, a mesh that I concieved and constructed with my own two fingers (hmm, note to self, learn to type properly). A mesh of objects, functions, expressions and tricks that I’ve picked up over the last decade and now carry around with me. This is my Developer Hat, and I wear it with pride. It’s a pity that other people see it as a…

Technical Support Person Hat: Worn 25% of the time

That’s right, many people seem to see me as a general helpdesk dogsbody. Just because I work on the internet they ask me things like “Are my credit card details safe on this site?”, or “My screen has frozen, what do I do?” (reboot, muppet) or even “My mouse is going a bit funny, can you help?”.

The Technical Support Person hat is very distinctive. It is bright yellow with a big badge on it that says “Happy to help!”. I hate wearing this hat, it just Isn’t Me.

Let me say this once, in the hope I don’t have to say it again: there are people who are paid to answer these questions, they are called Technical Support. Ask them, not me. I am too busy to figure out how you can print every other page of your document.

Secretarial Dogsbody Hat: worn 10% of the time

Quite a lot of the time I find myself doing menial tasks that could be easily done by a much more junior person than me. The Secretarial Dogsbody Hat, therefore, covers a whole range of scenarios; from checking spelling in web pages to resizing images. While this brain-light work is sometimes a welcome change from the heavy development brain-ache stuff, it’s more often than not an annoying distraction from more important work.

Still, at the moment, there isn’t anyone else available to wear this hat, so I’m stuck with it.

Designer Hat: Worn 10% of the time

The Designer Hat is also quite distinctive. It has a set of tools down the left hand side containing things like ‘Gradiated Fill’ and ‘Bezier Curve’ and sucks up memory like nothing else on earth. It’s not a natural fit for me, but very occasionally I get it a a good angle and someone says “That looks nice”. I’d like to fit inside the Designer Hat better, but most of the time it’s a case of just jamming it on and hoping it doesn’t fall off.

Marketer Hat: worn 5% of the time

The Marketer Hat is slightly wierd. On the ouside it is all smiles and flashy graphics and low, low prices. But on the inside it is hollow, built with bits of string and devoid of soul. The kind of Marketing Hat I’d like to wear is one that is big enough for a whole community to get inside, but those hats are few and far between in big business.

Business Development Person Hat: worn 5% of the time

I like the Business Development Person Hat. It’s a no-nonsense sort of hat, plain and strong, with several useful compartments. The problem is I don’t get to wear it very often because people think that I am only capable of wearing the Technical Support Person Hat. And the few times I do get to wear the Business Development Person Hat most people tend to think I dont know what I’m on about because I don’t have an MBA or wear expensive suits.

Community Facilitator Hat: worn 5% if the time

The Community Facilitator Hat is a fun hat to wear, even if it does mean hard work. It comes with a mop for cleaning up messes, and a megaphone for making announcements. Wearing this hat means you have some degree of power, but it definitely doesn’t mean you have some degree of respect. The Community Facilitator Hat is the rarest of hats to find a perfect fit of, but once you find the right hat it’s there for life.

What most business people don’t realise is that the Community Facilitator Hat, Business Development Person Hat and Marketer Hat could (and should be one and the same.

projectGenie – the website…

Posted on by chris

Just like “Cheese – the film”. I’m being a bit premature with this, but I’ve waited so long to get this live I’m going to tell you all about it before I’ve had chance to finish it. You know my project management software? Well, it’s now got it’s own website.

Obviously it’s not finished yet, and I’m frankly not sure when it will be, but I’m happy with it all the same.

Open letter to Oxfam…

Posted on by chris

Dear Oxfam,

I was happy when I heard that you are selling music online. However I wan’t so happy to learn that you only allow Windows IE use only.

Is that because you think Macintosh and Linux users don’t want to give to charity? Is it because you are unaware of the other, more secure browsers available? Or maybe you’ve signed a deal with Microsoft to use their audio file format rather than one of the many other formats available.

Also, your main music page is also built using broken and invalid code. I’ve done a new version, built using web standards which works in all browsers.

The advantages of web standards are clear to see; for example consider that the HTML code of my version of your page weighs in at a fraction the size of your HTML code. That may save you a considerable amount in bandwidth charges when applied across your whole site.

Whatever the reason for the decision to not build your websites using international standards, I’m disappointed and it’s made me have difficulties in using your site. Surely it’s in your interest to make your website as open as possible, so as many people as possible can use it and help to fund your excellent work. I firmly believe that web standards are the way forward to ensure a more open, accessible and scalable future for the web.

Yours sincerely

Chris Taylor