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.
Comments
Good tip. I’ve used GROUP_CONCAT before, but that method falls into the list of things I “know”, but rarely use. Most of the time, whenever I come across problems like this, I end up just solving the data munging problems in code rather than waste 15-20 minutes looking up the proper way to do things in SQL.
I’d say, on any given day, I only use the very tip of the SQL iceberg. Basic SELECTs, INSERTs, UPDATEs, and DELETEs. I love SQL and all of the power features, I just never get a chance to use them and those skills get rusty to the point of being a waste of my time to use. :/
Yeah, I normally would have done it in code too, but then I would have had to come up with some weird XML pipeline to feed Sphinx.
That’s the nice thing about most of my data structures, is that I don’t often need joins. It’s also one of the nice things about document stores that attracts me to them (esp. MongoDB).