By jnorton on

Hello,

I am writing a review site (tutorial coming soon I hope) and have run into a problem using SQL in templates.

Here is the error code:

SELECT AVG(content_field_rating.field_rating_rating) FROM content_field_rating INNER JOIN node ON content_field_rating.nid = node.nid INNER JOIN node_access na ON na.nid = n.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 4 AND na.realm = 'content_access_author') OR (na.gid = 2 AND na.realm = 'content_access_rid'))) AND ( node.status =1 AND node.type = 'review')

What I am trying to acheive is to call review attributes such as average ratings from CCK field/database tables from within a theme template. It seems that me the adminstrator can access the custom queries but all other users can't.

Here is an example SQL query I am using:

SELECT content_field_content_id.field_content_id_value
FROM {content_field_content_id}
INNER JOIN {node} ON content_field_content_id.nid = node.nid
WHERE content_field_content_id.field_content_id_value = %d
AND node.status =1

%d = $node->nid in case you were wondering.

Is there an easy way around this problem as I am completely stuck?

Thanks in advance.

Comments

jnorton’s picture

jnorton commented

The actual error code for the SQL statements are:

user warning: Unknown table 'n' in on clause query: SELECT AVG(content_field_rating.field_rating_rating) FROM content_field_rating INNER JOIN node ON content_field_rating.nid = node.nid INNER JOIN node_access na ON na.nid = n.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 4 AND na.realm = 'content_access_author') OR (na.gid = 2 AND na.realm = 'content_access_rid'))) AND ( node.status =1 AND node.type = 'review') in /usr/local/psa/home/vhosts/mysite.com/httpdocs/includes/database.mysql.inc on line 172.

cburschka’s picture

cburschka
they
commented

You're trying to use n as an alias for {node} in the second join criterion, but aren't actually aliasing {node} as anything. You can either replace n with node, or place an n between "node" and "ON" earlier.

jnorton’s picture

jnorton commented

Hey,

Thanks for the quick reply.

To summarize, is this query correct?

SELECT AVG(content_field_rating.field_rating_rating)
FROM content_field_rating
INNER JOIN {node} n ON content_field_rating.nid = node.nid
WHERE node.status =1
AND node.type = 'review'

jnorton’s picture

jnorton commented

The correct code is:

SELECT AVG(content_field_rating.field_rating_rating)
FROM content_field_rating
INNER JOIN {node} n ON content_field_rating.nid = n.nid
WHERE n.status =1
AND n.type = 'review'

Thanks anyway.