Build current domain in sql query

Something like this should work for nodes on current domain (published to current or all domains):

$result = db_query("SELECT n.nid, n.title 
FROM {node} n
LEFT JOIN {domain_access} da ON n.nid = da.nid
  INNER JOIN {links_node} wn ON n.nid = wn.nid
  INNER JOIN {links} w ON wn.lid = w.lid
WHERE n.type = 'weblink'
  AND n.status = 1
  AND n.moderate = 0
AND ((da.realm = 'domain_id' AND da.gid = %d) OR (da.realm = 'domain_site' AND da.gid = 0))
ORDER BY n.created DESC
LIMIT %d", $_domain['domain_id'], $nlimit));

You should remember to escape db_query() and use db_rewrite_sql() for security reasons.

See http://api.drupal.org/api/function/db_query/6 and http://api.drupal.org/api/function/db_rewrite_sql/6

Looking at the query, I do not understand why you have the extra INNER JOINs there -- since you are not selecting any data from those tables, it seems like wasted effort.

The query you have written will (in effect) be created automatically if you pass:

$nlimit = 15;
$result = pager_query(db_rewrite_sql("SELECT n.nid, n.title 
FROM {node} n
WHERE n.type = 'weblink'
  AND n.status = 1
  AND n.moderate = 0
ORDER BY n.created DESC", $nlimit));

db_rewrite_sql() will magically add the node_access logic; pager_query() adds database-safe LIMIT clauses. Here's a sample query from the blog module:

  $result = pager_query(db_rewrite_sql(
"SELECT n.nid, n.created 
   FROM {node} n 
   WHERE n.type = 'blog' AND n.status = 1 
   ORDER BY n.sticky DESC, n.created DESC"), 
variable_get('default_nodes_main', 10));

Here's how that query is actually run after the query alter functions run:

SELECT DISTINCT(n.nid), n.sticky, n.created FROM node n INNER JOIN node_access na ON na.nid = n.nid WHERE (na.grant_view >= 1 AND ((na.realm = 'all' AND na.gid = 0) OR (na.realm = 'domain_site' AND na.gid = 0) OR (na.realm = 'domain_id' AND na.gid = 0)) ) AND ( n.promote = 1 AND n.status = 1 ) ORDER BY n.sticky DESC, n.created DESC LIMIT 0, 10

If you need to filter out 'all affiliates,' you may need to tweak the query a bit.

You may, in this case, need to specify your primary table to db_rewrite_sql() -- it's hard to say. If you use db_rewrite_sql(), the node access system will filter your query for you, but it will return items that match either the active domain or 'all affiliates.'

I think for that you have to add a JOIN to {domain_access}, try:

global $_domain;
$nlimit = 15;
$result = pager_query(db_rewrite_sql("SELECT n.nid, n.title
FROM {node} n
  INNER JOIN {domain_access} da
  ON da.nid = n.nid
WHERE n.type = 'weblink'
  AND n.status = 1
  AND n.moderate = 0
  AND da.domain_id = %d
ORDER BY n.created DESC", $_domain['domain_id'], $nlimit));

This should be ok.

Use the Devel module to debug the query that is actually being written to the db.

The global $_domain loads the currently active domain, which is used in the query argument as $_domain['domain_id']

It is also possible that you are returning an empty result set for other reasons, such as no nodes that match the {node} table part of the query, or the use of Domain Strict.

And Domain Views build these for you, if you are using Views 1.