I have a view which works ok on user 1 but fails on all the other users. It should list and sum items just once, but if user is different than 1 rows are shown twice. I paste the SQL from user 1 and for another user with access to the nodes. It sould be bust a list of 3 rows.

If you compare the two queries you see the only differences are that the second one has a " INNER JOIN cc2_node_access na ON na.nid = node.nid" and a more restrictive WHERE.

Any hint of why the "INNER JOIN" makes the rows appear twice and how to solve this?

/* uid == 1 */

SELECT DISTINCT(node.nid) AS nid, node_cc_assentaments_linies6_comptecomptable.compte_comptable_nom AS node_cc_assentaments_linies6_comptecomptable_compte_comptable_nom, SUM(node_cc_assentaments_linies_pressupost_despeses.valor) AS node_cc_assentaments_linies_pressupost_despeses_valor, SUM(node_cc_assentaments_linies_pressupost_ingressos.valor) AS node_cc_assentaments_linies_pressupost_ingressos_valor, node_cc_assentaments_linies6.compte_comptable AS node_cc_assentaments_linies6_compte_comptable FROM cc2_node node LEFT JOIN cc2_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 cc2_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%' LEFT JOIN cc2_node_cc_assentaments_linies node_cc_assentaments_linies_pressupost_ingressos ON node.nid = node_cc_assentaments_linies_pressupost_ingressos.nid AND node_cc_assentaments_linies_pressupost_ingressos.compte_comptable LIKE '7%'                                                    WHERE                                                                                                                                                                                                                                                                                                          (((node.status <> 0) AND (node.type in ('cc_linia'))) AND (node_cc_assentaments_linies6.assentament_nid = 0) AND (node_cc_assentaments_linies6.projecte_nid = 1693)) AND (((node_cc_assentaments_linies6.compte_comptable) LIKE ('6%')) OR ((node_cc_assentaments_linies6.compte_comptable) LIKE ('7%')))  GROUP BY node_cc_assentaments_linies6_comptecomptable_compte_comptable_nom, node_cc_assentaments_linies6_compte_comptable ORDER BY node_cc_assentaments_linies6_compte_comptable ASC LIMIT 0, 10

/* uid <> 1 */
SELECT DISTINCT(node.nid) AS nid, node_cc_assentaments_linies6_comptecomptable.compte_comptable_nom AS node_cc_assentaments_linies6_comptecomptable_compte_comptable_nom, SUM(node_cc_assentaments_linies_pressupost_despeses.valor) AS node_cc_assentaments_linies_pressupost_despeses_valor, SUM(node_cc_assentaments_linies_pressupost_ingressos.valor) AS node_cc_assentaments_linies_pressupost_ingressos_valor, node_cc_assentaments_linies6.compte_comptable AS node_cc_assentaments_linies6_compte_comptable FROM cc2_node node LEFT JOIN cc2_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 cc2_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%' LEFT JOIN cc2_node_cc_assentaments_linies node_cc_assentaments_linies_pressupost_ingressos ON node.nid = node_cc_assentaments_linies_pressupost_ingressos.nid AND node_cc_assentaments_linies_pressupost_ingressos.compte_comptable LIKE '7%' INNER JOIN cc2_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.assentament_nid = 0) AND (node_cc_assentaments_linies6.projecte_nid = 1693)) AND (((node_cc_assentaments_linies6.compte_comptable) LIKE ('6%')) OR ((node_cc_assentaments_linies6.compte_comptable) LIKE ('7%'))) )GROUP BY node_cc_assentaments_linies6_comptecomptable_compte_comptable_nom, node_cc_assentaments_linies6_compte_comptable ORDER BY node_cc_assentaments_linies6_compte_comptable ASC LIMIT 0, 10

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

randallknutson’s picture

Status: Active » Needs review
FileSize
1.99 KB

Ran into this issue. Even though it is old, there was never a solution.

I've added an option on the field so that you can add a distinct inside the count. Edit the field options and check the Distinct option.

yang_yi_cn’s picture

yang_yi_cn’s picture

Status: Needs review » Patch (to be ported)

The patch solves my problem. can someone commit this?

mpavankumar’s picture

subscribing

ionmedia’s picture

Status: Patch (to be ported) » Active

i have same issue (duplicating results under user with uid !=1)

patch not working for me

for a right results i must do it now :

after SQL Aggregation: Group By Fields Group By Fields
add Customfield: PHP-code

global $user;

if ($user->uid==1) {
$sum=$data->node_data_field_value;
}
else {
$sum=($data->node_data_field_value)/2;
}

echo $sum;

i understand, what this is not a right answer, but i can't see what prevent right display