Cascading a MySQL SELECT

November 12, 2007 9:55 AM

I'm determined to do this in one query. I sure I can, I KNOW you can. Writing a tooltip retrieval system for a site with multiple versions of the same module. I want to supply a generic tip for each version level that can be overridden for a specific module. Diagrams = word x 10^3

So the Knowledgebase table looks a bit like this
PathItemTooltipArticlesaddGeneric tip textArticles-1.0addFall back for all 1.0Articles-1.0.2addSpecific for 1.0.2Articles-1.0.3addSpecific for 1.0.3
So for I've tried SELECTING with GROUP BY, with DISTINCT, with ORDER BY and results have been inconsistent. When I get a sucess I'm never confident its not a fluke

I simply want to make the call with the module's path and for it to nicely degrade back to the root if nothing else exists. So that

getTip(Articles-1.0.2) returns "Specific for 1.0.2"
getTip(Articles-1.0.4) returns "Fallback for all 1.0"
getTip(Articles-2.0.0) returns "Generic tip text"

Hmm - committing it here makes me think its not a single request after all, but this is how translations engines work isn't it (if theres no translation have a fallback language?)

Hope that makes sense

posted by handybitesize to Computers & Internet (9 answers total)

Response by poster: Hmm HTML was on preview - a link will follow
posted by handybitesize at 10:00 AM on November 12, 2007

Response by poster: Table layout here http://codersoffortune.com/mysql.html

Sorry about that
posted by handybitesize at 10:06 AM on November 12, 2007

At a glance, it looks like you need to normalize the tables a little bit more. I'm having a bit of trouble understanding the layout now, so apologizes if I have some of the terminology wrong. Here are the tables as I see them:

Items
====
ItemId
Name
VersionId

TooltipTypes
=========
TooltipTypeId
Name

Tooltips
=====
TooltipId
ItemId
VersionId (NULL)

I'm not sure that's exactly it, but I'm not all that clear on your setup either. This would at least let you do outer joins to get specific tips if they exist and fall back to a non-specific tip if it's not there. The only drawback is it might require you to dump in some redundant data given the structure of what you currently have.
posted by yerfatma at 10:21 AM on November 12, 2007

Best answer: What version of MySQL are you using? If you're using V.5, you could write a stored procedure, and then it'd be easy.

You'd do something like

--------------------
CREATE PROCEDURE spGetTip (@Article nvarchar(50))
AS

DECLARE @Result nvarchar(50);

//query for specific tooltip
SELECT @Result=Tooltip FROM Knowledgebase WHERE Path=@Article;

IF (@Result is NULL)
BEGIN
//generic tooltip
SELECT @Result=Tooltip FROM Knowledgebase WHERE Path='Articles'
// If 'Articles' varies, you could do some sort of string split to take the part before the '-' and query using that.

END

RETURN @Result

GO
------------------

So then you'd just call that procedure, and you'd be golden. Unless I misunderstood you...
posted by jeffxl at 10:24 AM on November 12, 2007 [1 favorite]

Yes, generally for localization there is a fallback system. Language codes have a definite structure: [language[_territory][.codeset][@modifier]]. For example:

• en_US (english, US)
• en_US.UTF-8 (english, US, in UTF-8)
• en_UK.UTF-16 (english, UK, in UTF-16)
• fr_FR.UTF-8@EU (french, france, in UTF-8, with the EU symbol)

Etc, etc. If a localization doesn't exist for a specifier, you start stripping off the parts, finally substituting a default (dependent on the software):

• ru_RU.KOI8-R
• ru_RU
• ru
• en_US

But most systems I've seen and used for doing this do it all in software. Java property files, gettext (C, PHP), Perl's libintl, so on and so forth. That is, the localizations aren't stored in the database. They're well suited for file based storage since (a) they're accessed frequently in the app, and (b) they aren't changed frequently.

To answer your original question, probably a stored procedure would be best for this. But you're going to have to write code for it; it won't fall out automatically. Also, you'll probably want to give your data a definite structure so that it's always easy to peal off the parts. You don't really have that right now, at least with the data we're given.
posted by sbutler at 11:02 AM on November 12, 2007

Best answer: um, if you could provide a sample export, it'd probably be easier to assist -- it's pretty difficult to discern the table structure from what you've posted. As others have mentioned, you may have some issues with your database design that, whether or not they are contributing to the success of this query, should be fixed.

if that's a single table, the easiest thing to do is get the entire row and get the non-NULL value in your programming logic.

Of course, you can use SQL to do this too by 'cascading' an IF statement, IE 'SELECT IF(my_field1 IS NOT NULL, my_field2, IF(myfield_2 IS NOT NULL,myfield_2,my_field3)) AS my_cascaded_field FROM table' ... but I understand that conditionals aren't necessarily the speediest things to have a relational database do.

the problem you're likely having with ORDER/GROUP BY is (as you may have discovered), an ORDER BY happens AFTER a GROUP BY. as far as I know, you can't specify an order to group by without using a sub-select.

if you're trying to do a conditional where, either use a stored procedure as people have suggested, or create a 'flag' in your selected columns that you can use to filter, IE 'SELECT IF(my_condition,1,0) AS my_flag FROM table WHERE my_flag=1;'
posted by fishfucker at 11:42 AM on November 12, 2007 [1 favorite]

oh, and while it's good to use as few queries as possible, don't get too hung up on doing everything in one query, especially if it makes your code more difficult to maintain/understand. Most well-written queries occur so quickly that if you have to do in quick succession, it won't kill you.

In fact, you might be surprised to find (i was) that many popular CMSes end up doing HUNDREDS of queries per page without any noticeable negative results until you start getting 'real' traffic.
posted by fishfucker at 11:44 AM on November 12, 2007

Best answer: Your table structure is jacked up (technical term), but if you just want to be able to do this in one query and move along:

SELECT Tooltip FROM tbl
WHERE Path in ('Articles-1.0.4', 'Articles-1.0', 'Articles')
ORDER BY
CASE WHEN Path = 'Articles-1.0.4' THEN 0
ELSE
CASE WHEN PATH = 'Articles-1.0' THEN 1 ELSE 2 END
END
LIMIT 1;

Check what explain says about that though, because it might be slower than using three queries if you have a unique index on Path, something like:

SELECT Tooltip FROM tbl WHERE Path = 'Articles-1.0.4'
UNION
SELECT Tooltip FROM tbl WHERE Path = 'Articles-1.0'
UNION
SELECT Tooltip FROM tbl WHERE Path = 'Articles'
LIMIT 1;

Which has the added benefit of being much much more transparent in its intended functionality. Of course both methods leave you to decompose 'Articles-1.0.4' to 'Articles-1.0' and 'Articles' either in client code or in a stored proc, but there's no escaping that unless you normalize your table.
posted by moift at 12:00 PM on November 12, 2007 [2 favorites]

Response by poster: Thanks everyone for your responses - I see that I'll have to add some logic somewhere - either in a stored proc or in the application code.

Thats not the real table structure btw, I just wanted to test the theory.

My gut instinct is to keep it in the application layer, but I'll perform some testing and post the results if this threads still open.

Thanks again
posted by handybitesize at 1:15 PM on November 12, 2007