Home
August 28, 2008 - 9:12am — Anonymous
  1. MYTABLE:
  2. +-------+-----+-----+
  3. | TITLE | NID | FID |
  4. +-------+-----+-----+
  5. | Test  | 1   | 2   |
  6. +-------+-----+-----+
  7. | T2    | 3   | 3   |
  8. +-------+-----+-----+
  9.  
  10. NODE:
  11. +-----+------+---------+
  12. | NID | TNID | CREATED |
  13. +-----+------+---------+
  14. | 1   | 1    | <date>  | (en)
  15. +-----+------+---------+
  16. | 2   | 1    | <date>  | (nl)
  17. +-----+------+---------+
  18. | 3   | 4    | <date>  | (en)
  19. +-----+------+---------+
  20. | 4   | 4    | <date>  | (nl)
  21. +-----+------+---------+
  22.  
  23. In MYTABLE I save the English NID value, but the problem lies when other languages have to be chosen. The NID value does not match the NID of the NODE table (obviously) and I cannot simply use the TNID value because it is possible people started from another language (as seen where TNID = 4).
  24.  
  25. My current query:
  26. SELECT m.nid, m.fid, m.title FROM {MYTABLE} INNER JOIN {NODE} on n.nid = m.nid WHERE n.status = 1 ORDER BY n.created DESC;
  27. This works perfectly for the EN version of the website since those are the NID values I store in the MYTABLE table, but doesn't work for other languages. How can I easily fix that, while still beeing able to sort on the creation date of a node.