problem with db_rewrite_sql
I am using group by module, and db_rewrite_sql is giving me problems because it cause rows to be selected twice and SUM reports double their value. After doing some research I have found that if I change: function node_db_rewrite_sql($query, $primary_table, $primary_field) { if ($primary_field == 'nid' && !node_access_view_all_nodes()) { $return['join'] = _node_access_join_sql($primary_table); $return['where'] = _node_access_where_sql(); $return['distinct'] = 1; return $return; } } to: function node_db_rewrite_sql($query, $primary_table, $primary_field) { if ($primary_field == 'nid' && !node_access_view_all_nodes()) { if (($where=_node_access_where_sql('view','new_na'))!='') { $return['where'] = "".$primary_table.".".$primary_field." IN (SELECT new_na.nid FROM {node_access} new_na WHERE ".$where.")"; return $return; } } } everything works as expected. Is there any reason why using "INNER JOIN" is better than using " WHERE nid IN (SELECT ... FROM {node_access} ... )"? -- *Les normes hi són perquè hi pensis abans de saltar-te-les *La vida és com una taronja, què esperes a exprimir-la? *Si creus que l'educació és cara, prova la ignorància. *La vida és com una moneda, la pots gastar en el que vulguis però només una vegada. *Abans d'imprimir aquest missatge, pensa en el medi ambient.
Lluis wrote:
Is there any reason why using "INNER JOIN" is better than using "WHERE nid IN (SELECT ... FROM {node_access} ... )"?
I think it was Larry Garfield who answered this yesterday or the day before. The SELECT clause is going to be executed for each node that otherwise matches the conditions. In the case of a larger site, that could result in a LOT of overhead. You might be better off doing a separate query to get the IN list and use db_placeholders to make the query. Nancy E. Wichmann, PMP Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr.
ok, I have now read "Creative querying". Otherwise, I have a problem using "views" and "group by" modules, the query is basically a "SELECT a, SUM(b) FROM table JOIN... WHERE ... GROUP BY a" It works perfectly before db_rewrite_sql (show more rows, but the SUM is ok); but after de db_rewrite_sql it show the correct rows but SUM(b) is doubled. I paste the "wrong" query here; if I remove just INNER JOIN and WHERE reference to {node_access} everything works fine SELECT DISTINCT(node.nid) AS nid, node_cc_assentaments_linies6.compte_comptable AS node_cc_assentaments_linies6_compte_comptable, node_cc_assentaments_linies6_comptecomptable.compte_comptable_nom AS node_cc_assentaments_linies6_comptecomptable_compte_comptable_nom, SUM(node_cc_assentaments_linies_balans_despeses_pressupostades.valor) AS node_cc_assentaments_linies_balans_despeses_pressupostades_valor, SUM(node_cc_assentaments_linies_balans_despeses_executades.valor) AS node_cc_assentaments_linies_balans_despeses_executades_valor, SUM(node_cc_assentaments_linies_pressupost_despeses.valor) AS node_cc_assentaments_linies_pressupost_despeses_valor FROM cc3_node node LEFT JOIN cc3_node_cc_assentaments_linies node_cc_assentaments_linies6 ON node.vid = node_cc_assentaments_linies6.vid LEFT JOIN cc2_cc_comptes_comptables node_cc_assentaments_linies6_comptecomptable ON node_cc_assentaments_linies6.compte_comptable = node_cc_assentaments_linies6_comptecomptable.compte_comptable_codi LEFT JOIN cc3_node_cc_assentaments_linies node_cc_assentaments_linies_balans_despeses_pressupostades ON node.nid = node_cc_assentaments_linies_balans_despeses_pressupostades.nid AND (node_cc_assentaments_linies_balans_despeses_pressupostades.compte_comptable LIKE '6%' AND node_cc_assentaments_linies_balans_despeses_pressupostades.assentament_nid = '0') LEFT JOIN cc3_node_cc_assentaments_linies node_cc_assentaments_linies_balans_despeses_executades ON node.nid = node_cc_assentaments_linies_balans_despeses_executades.nid AND (node_cc_assentaments_linies_balans_despeses_executades.compte_comptable LIKE '6%' AND node_cc_assentaments_linies_balans_despeses_executades.assentament_nid
'0') LEFT JOIN cc3_node_cc_assentaments_linies node_cc_assentaments_linies_pressupost_despeses ON node.nid = node_cc_assentaments_linies_pressupost_despeses.nid AND node_cc_assentaments_linies_pressupost_despeses.compte_comptable LIKE '6%' INNER JOIN cc3_node_access na ON na.nid = node.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 805 AND na.realm = 'wu_comptacau') OR (na.gid = 805 AND na.realm = 'wu_comptacau <###> 2010') OR (na.gid = 805 AND na.realm = 'wu_visitor_comptacau') OR (na.gid = 805 AND na.realm = 'wu_visitor_comptacau <###> 2010'))) AND ( (node.status <> 0) AND (node.type in ('cc_linia')) AND ((node_cc_assentaments_linies6.compte_comptable) LIKE ('6%')) AND (node_cc_assentaments_linies6.projecte_nid = 3989) )GROUP BY node_cc_assentaments_linies6_compte_comptable, node_cc_assentaments_linies6_comptecomptable_compte_comptable_nom ORDER BY node_cc_assentaments_linies6_compte_comptable ASC
On Tue, Nov 24, 2009 at 3:07 PM, Nancy Wichmann <nan_wich@bellsouth.net> wrote:
Lluis wrote:
Is there any reason why using "INNER JOIN" is better than using "WHERE nid IN (SELECT ... FROM {node_access} ... )"?
I think it was Larry Garfield who answered this yesterday or the day before. The SELECT clause is going to be executed for each node that otherwise matches the conditions. In the case of a larger site, that could result in a LOT of overhead. You might be better off doing a separate query to get the IN list and use db_placeholders to make the query.
Nancy E. Wichmann, PMP Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr.
-- *Les normes hi són perquè hi pensis abans de saltar-te-les *La vida és com una taronja, què esperes a exprimir-la? *Si creus que l'educació és cara, prova la ignorància. *La vida és com una moneda, la pots gastar en el que vulguis però només una vegada. *Abans d'imprimir aquest missatge, pensa en el medi ambient.
Lluís wrote:
ok, I have now read "Creative querying".
Otherwise, I have a problem using "views" and "group by" modules, the query is basically a
"SELECT a, SUM(b) FROM table JOIN... WHERE ... GROUP BY a"
It works perfectly before db_rewrite_sql (show more rows, but the SUM is ok); but after de db_rewrite_sql it show the correct rows but SUM(b) is doubled.
Yes, probably because the user is a member of 2 different roles that have access, and therefore the GROUP BY ends up counting both. There's a problem similar to this in core in counting forum posts, for example. I don't know of any solutions to this, either. This is a really unfortunate effect of the query we use. :/
I tunned by module so now: - just one $grant is assigned to user (before "visitor" and "editor" was assigned to "editors") - two records are saved for the node, one read-only and one read&write The problem persists, any other hint? On Tue, Nov 24, 2009 at 5:17 PM, Earl Miles <merlin@logrus.com> wrote:
Lluís wrote:
ok, I have now read "Creative querying".
Otherwise, I have a problem using "views" and "group by" modules, the query is basically a
"SELECT a, SUM(b) FROM table JOIN... WHERE ... GROUP BY a"
It works perfectly before db_rewrite_sql (show more rows, but the SUM is ok); but after de db_rewrite_sql it show the correct rows but SUM(b) is doubled.
Yes, probably because the user is a member of 2 different roles that have access, and therefore the GROUP BY ends up counting both. There's a problem similar to this in core in counting forum posts, for example. I don't know of any solutions to this, either. This is a really unfortunate effect of the query we use. :/
-- *Les normes hi són perquè hi pensis abans de saltar-te-les *La vida és com una taronja, què esperes a exprimir-la? *Si creus que l'educació és cara, prova la ignorància. *La vida és com una moneda, la pots gastar en el que vulguis però només una vegada. *Abans d'imprimir aquest missatge, pensa en el medi ambient.
If you're working with Drupal 6.14, you'll want to be aware of http://drupal.org/node/284392 (db_rewrite_sql causing issues with DISTINCT<http://drupal.org/node/284392> ), a significant regression. On Tue, Nov 24, 2009 at 4:03 AM, Lluís <enboig@gmail.com> wrote:
I am using group by module, and db_rewrite_sql is giving me problems because it cause rows to be selected twice and SUM reports double their value.
After doing some research I have found that if I change: function node_db_rewrite_sql($query, $primary_table, $primary_field) { if ($primary_field == 'nid' && !node_access_view_all_nodes()) { $return['join'] = _node_access_join_sql($primary_table); $return['where'] = _node_access_where_sql(); $return['distinct'] = 1; return $return; } }
to: function node_db_rewrite_sql($query, $primary_table, $primary_field) { if ($primary_field == 'nid' && !node_access_view_all_nodes()) { if (($where=_node_access_where_sql('view','new_na'))!='') { $return['where'] = "".$primary_table.".".$primary_field." IN (SELECT new_na.nid FROM {node_access} new_na WHERE ".$where.")"; return $return; } } }
everything works as expected.
Is there any reason why using "INNER JOIN" is better than using " WHERE nid IN (SELECT ... FROM {node_access} ... )"?
-- *Les normes hi són perquè hi pensis abans de saltar-te-les *La vida és com una taronja, què esperes a exprimir-la? *Si creus que l'educació és cara, prova la ignorància. *La vida és com una moneda, la pots gastar en el que vulguis però només una vegada. *Abans d'imprimir aquest missatge, pensa en el medi ambient.
-- Randy Fay Drupal Development, troubleshooting, and debugging randy@randyfay.com +1 970.462.7450
participants (4)
-
Earl Miles -
Lluís -
Nancy Wichmann -
Randy Fay