Restricting hook_db_rewrite_sql
I'm having problems getting hook_db_rewrite_sql to restrict its actions to queries where it has business rewriting, and am having difficulty figuring how what to look at in order to do this. Here's the use case. I have two node types (call them "civinode_contact" and "civinode_access") that I want viewable according to a couple of tables that test the current user. This now works (i.e., it makes the right join and does the test I want), but it also tries to rewrite the query that og.module creates to do its own listing in www.example.com/og, at least for the anonymous user: Unknown column 'og.type' in 'where clause' query: SELECT og.nid, n.title, r.body, n.uid, u.name, og.description FROM og og INNER JOIN node n ON og.nid = n.nid INNER JOIN node_revisions r ON r.vid = n.vid INNER JOIN users u ON n.uid = u.uid LEFT JOIN node_access cna ON cna.nid = og.nid WHERE ((og.type != 'civinode_access' AND og.type != 'civinode_contact') ) AND og.directory=1 AND n.status=1 ORDER BY n.nid DESC LIMIT 0, 50 in ......./includes/database.mysql.inc on line 120. The code looks something like this: function civinode_db_rewrite_sql($query, $primary_table, $primary_field, $args){ global $user; if ($primary_field == 'nid') { //check for access to contacts if (user_access('view all contacts') or user_access('administer CiviCRM')) return; //no need to screen $result = array(); $result['join'] = "LEFT JOIN {node_access} cna ON cna.nid = $primary_table.nid"; $contact_limit = ""; $accesses = civinode_get_accesses_for_user($user->uid); if ($accesses) { $contact_limit = " OR cna.gid IN (" . implode(',', $accesses) . ")"; } $result['where'] = "($primary_table.type != 'civinode_access' AND $primary_table.type != 'civinode_contact') $contact_limit"; //$result['where'] = $contact_limit; return $result; } I've found that need to user $primary_table, since at the very least, not every query aliases the node table to "n" (some alias it to "node", or just aren't aliasing it AFAIK). But I don't want to be picking up this particular og query. How do I restrict this hook so that it does not interfere with arbitrary queries, but picks up cases where it would restrict viewing my nodes (as I need it to do)? Thanks, Rob Rob Thorne Torenware Networks
Rob Thorne wrote:
I'm having problems getting hook_db_rewrite_sql to restrict its actions to queries where it has business rewriting, and am having difficulty figuring how what to look at in order to do this.
Here's the use case. I have two node types (call them "civinode_contact" and "civinode_access") that I want viewable according to a couple of tables that test the current user. This now works (i.e., it makes the right join and does the test I want), but it also tries to rewrite the query that og.module creates to do its own listing in www.example.com/og, at least for the anonymous user:
That's primarily because most node queries are type agnostic. That's why there is a node_access table.
Earl Miles wrote:
That's primarily because most node queries are type agnostic. That's why there is a node_access table.
Well, yeah. Which I'm using: I've defined a realm field for node_access for what I'm doing, and among other things, I'm using hook_db_rewrite_sql to use it; civinode_get_accesses_for_user($user->uid) identifies which of these node_access fields is relevant to the current user. The documentation does not make it clear what it means to be a $primary_table, although it would help if it did. I only use the field because in most of the cases I actually want to affect, I assume that $primary_table is either "node" or an alias to it. This particular query is failing because Moshe is doing his rewrite with "og" as the table. I don't need or want to modify his query, but only discovered the problem via the error logs. Rob
Do you control the query that needs rewriting? If so, put some hints ikn the $args parameter to db_rewrite_sql(). If not, you have to find some unique way of identifying the queries you want to rewrite. On 4/16/06 2:11 PM, "Rob Thorne" <rob@torenware.com> wrote:
I'm having problems getting hook_db_rewrite_sql to restrict its actions to queries where it has business rewriting, and am having difficulty figuring how what to look at in order to do this.
Here's the use case. I have two node types (call them "civinode_contact" and "civinode_access") that I want viewable according to a couple of tables that test the current user. This now works (i.e., it makes the right join and does the test I want), but it also tries to rewrite the query that og.module creates to do its own listing in www.example.com/og, at least for the anonymous user:
Unknown column 'og.type' in 'where clause' query: SELECT og.nid, n.title, r.body, n.uid, u.name, og.description FROM og og INNER JOIN node n ON og.nid = n.nid INNER JOIN node_revisions r ON r.vid = n.vid INNER JOIN users u ON n.uid = u.uid LEFT JOIN node_access cna ON cna.nid = og.nid WHERE ((og.type != 'civinode_access' AND og.type != 'civinode_contact') ) AND og.directory=1 AND n.status=1 ORDER BY n.nid DESC LIMIT 0, 50 in ......./includes/database.mysql.inc on line 120.
The code looks something like this:
function civinode_db_rewrite_sql($query, $primary_table, $primary_field, $args){ global $user;
if ($primary_field == 'nid') { //check for access to contacts if (user_access('view all contacts') or user_access('administer CiviCRM')) return; //no need to screen
$result = array(); $result['join'] = "LEFT JOIN {node_access} cna ON cna.nid = $primary_table.nid"; $contact_limit = ""; $accesses = civinode_get_accesses_for_user($user->uid); if ($accesses) { $contact_limit = " OR cna.gid IN (" . implode(',', $accesses) . ")"; } $result['where'] = "($primary_table.type != 'civinode_access' AND $primary_table.type != 'civinode_contact') $contact_limit"; //$result['where'] = $contact_limit; return $result; }
I've found that need to user $primary_table, since at the very least, not every query aliases the node table to "n" (some alias it to "node", or just aren't aliasing it AFAIK). But I don't want to be picking up this particular og query.
How do I restrict this hook so that it does not interfere with arbitrary queries, but picks up cases where it would restrict viewing my nodes (as I need it to do)?
Thanks, Rob
Rob Thorne Torenware Networks
Moshe, In general, I don't know who writes these queries, nor can I control them: I am only using hook_db_rewrite_sql so that queries that list my nodes will omit them. A good example is the query that generates the default front page (*/node). There does not appear to be anything specific in the calling conventions to hook_db_rewrite_sql for this purpose. If there is, tell me and I will use it. Thanks, Rob Moshe Weitzman wrote:
Do you control the query that needs rewriting? If so, put some hints ikn the $args parameter to db_rewrite_sql(). If not, you have to find some unique way of identifying the queries you want to rewrite.
On 4/16/06 2:11 PM, "Rob Thorne" <rob@torenware.com> wrote:
I'm having problems getting hook_db_rewrite_sql to restrict its actions to queries where it has business rewriting, and am having difficulty figuring how what to look at in order to do this.
Here's the use case. I have two node types (call them "civinode_contact" and "civinode_access") that I want viewable according to a couple of tables that test the current user. This now works (i.e., it makes the right join and does the test I want), but it also tries to rewrite the query that og.module creates to do its own listing in www.example.com/og, at least for the anonymous user:
Unknown column 'og.type' in 'where clause' query: SELECT og.nid, n.title, r.body, n.uid, u.name, og.description FROM og og INNER JOIN node n ON og.nid = n.nid INNER JOIN node_revisions r ON r.vid = n.vid INNER JOIN users u ON n.uid = u.uid LEFT JOIN node_access cna ON cna.nid = og.nid WHERE ((og.type != 'civinode_access' AND og.type != 'civinode_contact') ) AND og.directory=1 AND n.status=1 ORDER BY n.nid DESC LIMIT 0, 50 in ......./includes/database.mysql.inc on line 120.
The code looks something like this:
function civinode_db_rewrite_sql($query, $primary_table, $primary_field, $args){ global $user;
if ($primary_field == 'nid') { //check for access to contacts if (user_access('view all contacts') or user_access('administer CiviCRM')) return; //no need to screen
$result = array(); $result['join'] = "LEFT JOIN {node_access} cna ON cna.nid = $primary_table.nid"; $contact_limit = ""; $accesses = civinode_get_accesses_for_user($user->uid); if ($accesses) { $contact_limit = " OR cna.gid IN (" . implode(',', $accesses) . ")"; } $result['where'] = "($primary_table.type != 'civinode_access' AND $primary_table.type != 'civinode_contact') $contact_limit"; //$result['where'] = $contact_limit; return $result; }
I've found that need to user $primary_table, since at the very least, not every query aliases the node table to "n" (some alias it to "node", or just aren't aliasing it AFAIK). But I don't want to be picking up this particular og query.
How do I restrict this hook so that it does not interfere with arbitrary queries, but picks up cases where it would restrict viewing my nodes (as I need it to do)?
Thanks, Rob
Rob Thorne Torenware Networks
participants (3)
-
Earl Miles -
Moshe Weitzman -
Rob Thorne