Come together with the global Drupal community in Rotterdam, 28 Sept – 1 Oct 2026. Sessions, contribution, connection, and Early Bird savings until 8 June.
By mardy78 on
Hello,
I'd like to find a solution for this: http://drupal.org/node/78973
Suppose I have a table like this:
nid trid lang
2 0 ia
1 1 ia
3 1 en
4 2 ia
5 2 en
6 2 it
7 0 ia
8 4 ia
9 4 en
10 4 it
11 0 ia
The "nid" is the node number, "lang" is the language the node is written in, and "trid" is used to group together the translation equivalents: that is, nodes 4,5,6 describe the same content in three different languages. If "trid" is 0, then the node has not been translated into other languages.
What I'm looking for is a query that filters out records in this table like this ("lang_user" and "lang_default" are two PHP variables which are known at the time the query is written; think of them as "en" and "ia"):
1) if "trid" is 0, then keep the record
2) if lang = lang_user, keep this record and filter out all other records having the same "trid"
3) if lang = lang_default, keep this record and filter out all other records having the same "trid"
4) filter out this record
My question is, can all this be achived within a single SQL statement (I guess no, but I'm not a SQL expert)?
If not, I guess I'll have to code a patch to drupal core, if I want to solve the issue http://drupal.org/node/78973, I am right?
Comments
It can be done
Hi, I'm not at my workstation right now to test this for you, but you should be able to get what you want with a statement such as the following. Let's say your table is called "translations". Then:
select * from translations A where a.trid=0 or a.lang=$lang_user or (a.lang=$lang_default and NOT EXISTS (select * from translations B where b.nid = a.nid and a.lang=$lang_user)
Of course you'll have to handle the variables properly in the code, I can't remember the syntax right now for the variable substitution.
Hope this helps!
Steve
Steve
Revista Y AHORA QUÉ | Selectividad - Universidades - Foro Blog Chat Estudiantes
Thank you very much!
Thank you Steve, your solution works perfectly! I also came to a similar one, but instead of "NOT EXISTS (select * ...)" I used "(select COUNT(*) ...) = 0".
I like yours more, but I'm not sure if there is any difference in execution speed.