Tag: Learning

Thursday Quote: William Gibson

December 9, 2010 » Geek, Life

“My problem is that all things are increasingly interesting to me.”

– William Gibson
During A Talk On “Zero History”

You Don’t Know What You Don’t Know – GROUP_CONCAT

September 4, 2010 » Geek

I’ve been trying to get Sphinx search running at work and I’ve been stymied by needing to get data out of a many-to-many table. This has to be a possibility, why can’t I find a reference to it, and I’m not much of a DBA to begin with.

Turns out it’s not that hard, it just takes an SQL construct that I’d never seen before, GROUP_CONCAT. Once you have that building block, it becomes pretty easy, but without it, it seemed insurmountable.

SELECT
    posts.id, 
    posts.title, 
    posts.body, 
    GROUP_CONCAT( tag.name SEPARATOR ', ' ) AS tags
FROM posts
LEFT OUTER JOIN posts.id = post_tags.post_id
LEFT OUTER JOIN post_tags.tag_id = tags.id
GROUP BY posts.id;

This highlights a flaw in my autodidact education. If I just glom together from the internet, and something isn’t mentioned (or is only mentioned tangentially) then I don’t even know I don’t know something. Interesting problem to have to work around.