[development] problem with db_rewrite_sql

[Lluís]

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 at 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.