Writing a news ticker module. It works, but it's sloppy as hell. Is there a better way to do this query? I've got three pre-existing websites that use the story module, the path module, and CCK to produce customized news stories in Drupal 6. I wanted to create a news ticker module, to consolidate the news from all three sites, and replace the per site views based approach we're using now.

Studied the entire schema that the sites use, and dissected Drupal to see if there was a relatively straight forward way to do this. Turns out, there isn't. Architecturally, Drupal is all over the place. 

So what I came up with was a situation where I do a crazy join between the nodes table and the the url_alias table, then union the whole thing together because the tables are identical between schemas. 

Like this:

            SELECT 
                   'site1' as disposition,
                   url_alias.dst as destination_url,
                   node.sticky,
                   node.promote,
                   node.created
              FROM    site1.node node
                   INNER JOIN
                      site1.url_alias url_alias

                   ON (node.nid = substring(url_alias.src,6,15) )

              WHERE substring(url_alias.dst,1,5) = 'news/'

              UNION


              SELECT 
              'site2'  as disposition,
              url_alias.dst as destination_url,
                   node.sticky,
                   node.promote,
                   node.created
              FROM    site2.node node
                   INNER JOIN
                      site2.url_alias url_alias

                   ON (node.nid = substring(url_alias.src,6,15) )

              WHERE substring(url_alias.dst,1,5) = 'news/'

              UNION

              SELECT 

              'site3'  as disposition,
              url_alias.dst as destination_url,
                   node.sticky,
                   node.promote,
                   node.created
              FROM    site3.node node
                   INNER JOIN
                      site3.url_alias url_alias

                   ON (node.nid = substring(url_alias.src,6,15) )

              WHERE substring(url_alias.dst,1,5) = 'news/'

              ORDER BY 4 DESC


But it seems to me that this is incredibly sloppy, and that there has to be a better way to do basically the same thing by depending on correctly formed relationships and keys, rather than using SUBSTRING to parse url aliases. 

What do you think?
Two things jump out at me, both related to the use of {url_alias}:


I don't see why you need to do the string shenanigans in the WHERE.  You should be able to to use node.type to limit yourself to the subset of nodes you want, especially if you are primarily using Pathauto settings.
I don't see why you explicitly need to return url_alias.dst in the result set. If you have the nid, then you can just use $path = url('node/' . $nid); to build up paths (or use l()).


I think this means that you should be able to eliminate the JOIN on the {url_alias} altogether, which should make the query much faster.