Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.
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
Comment | File | Size | Author |
---|---|---|---|
#1 | 637402-views_groupby_db_rewritesql.patch | 1.99 KB | randallknutson |
Comments
Comment #1
randallknutson CreditAttribution: randallknutson commentedRan 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.
Comment #2
yang_yi_cn CreditAttribution: yang_yi_cn commentedmark #800482: duplicate count under a user role a duplicate of this
Comment #3
yang_yi_cn CreditAttribution: yang_yi_cn commentedThe patch solves my problem. can someone commit this?
Comment #4
mpavankumar CreditAttribution: mpavankumar commentedsubscribing
Comment #5
ionmedia CreditAttribution: ionmedia commentedi 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
i understand, what this is not a right answer, but i can't see what prevent right display