Improve query.
ao2 - April 17, 2009 - 16:45
| Project: | Select translation |
| Version: | 6.x-0.x-dev |
| Component: | Code |
| Category: | task |
| Priority: | minor |
| Assigned: | Unassigned |
| Status: | needs work |
Description
Hi,
I see that the current query executes two sub-selects, can't this be improved with a query like the one I propose in #434308: Filter duplicate translations? It uses the neat tecnique of ORDER BY CASE which I find very appropriate for the purpose.
Thanks for the module btw, I hope to see an improved and stable version released soon.
Regards,
Antonio.
#1
Looks nice, I did not know of this syntax.
Does it work with both MySql and PostGre ?
#2
Hi,
I tested it with MySql only, but I read on the web that it should work with PostGre too.
Anyway it would be better to test that :)
In the proposed query the original translation case is not handled but it shouldn't be too difficult to add it.
Regards,
Antonio
#3
Thanks, this is very usefull. My queries are very ugly, so this will be very good :)
There's another couple of patches I need to look at for this module ; I'll try and find time to do this this week and I'll make a beta release.
#4
Hi Anselm,
if you haven't looked at this yet I could try to do it myself and send a patch.
Regards,
Antonio
#5
ao2 : I haven't had time to do it - a patch would be great thanks !!!
#6
Ok,
if I understood the drupal scheme right and the original version comes always first we could handle the original language case implicitly in the
ELSEpart of theCASE, something like this (in MySQL):set @current_language='it';
set @default_language='en';
set @curr_lang_priority = 1;
set @def_lang_priority = 2;
set @orig_lang_priority = 3;
SELECT nid,tnid, language, title
FROM node AS n1
WHERE (n1.promote <> 0) AND (n1.status <> 0) AND (
tnid IS NULL OR tnid=0 OR nid=(
SELECT nid FROM node AS n2
WHERE n2.tnid=n1.tnid
ORDER BY
CASE n2.language
WHEN @current_language THEN @curr_lang_priority
WHEN @default_language THEN @def_lang_priority
ELSE @orig_lang_priority
END
LIMIT 1
)
)
ORDER BY n1.sticky DESC, n1.created DESC;
Patch attached which implements this idea in Select Translation.
Needs work and testing :)
Regards,
Antonio
#7
The patch exposes this bug #369629: db_rewrite_sql breaks on sql having "order by" clause so it is not fully usable for now.