Sort by payment type in the table dolibarr/compta/payment/list.php

[MechMachOth]

Bug

When I modify the file dolibarr/compta/payment/list.php by adding 'c.libelle' to the query:

// DISTINCT is to avoid duplicate when there is a link to sales representatives
$sql = "SELECT DISTINCT p.rowid, p.ref, p.datep, p.fk_bank, p.amount, p.statut, p.num_paiement, c.libelle";
$sql .= ", c.code as paiement_code";
$sql .= ", ba.rowid as bid, ba.ref as bref, ba.label as blabel, ba.number, ba.account_number as account_number, ba.fk_accountancy_journal as accountancy_journal";
$sql .= ", s.rowid as socid, s.nom as name, s.email";

The sorting is done correctly, and I no longer encounter any errors.

Environment Version

18.0.1

Environment OS

No response

Environment Web server

No response

Environment PHP

No response

Environment Database

No response

Environment URL(s)

No response

Expected and actual behavior

When I click on 'type' in the payments table to sort it in ascending or descending order, I expect the data to be sorted correctly. However, instead, I receive the following error:

Requested URL: /dolibarr/compta/payment/list.php?sortfield=c.libelle&sortorder=asc&begin=&contextpage=paymentlist
Referer: http://localhost/dolibarr/compta/payment/list.php?sortfield=c.libelle&sortorder=desc&begin=&contextpage=paymentlist
Menu Handler: eldy_menu.php
Modules/Applications: syslog, user, api, contract, ficheinter, supplier_proposal, incoterm, tax, loan, propal, productbatch, stock, product, expedition, project, mailing, workflow, import, export, bookmark, fckeditor, gravatar, paypal, notification, clicktodial, blockedlog, agenda, bank, barcode, cron, order, donation, ecm, expensereport, supplier, holiday, margin, opensurvey, paybox, printing, resource, salaries, service, company, modulebuilder, webhook, emailcollector, debugbar, knowledgemanagement, externalids, ticket, invoice, direct_debit, recovery
Database Manager Type: mysqli
Error in the last database access: SELECT DISTINCT p.rowid, p.ref, p.datep, p.fk_bank, p.amount, p.statut, p.num_paiement, c.code as payment_code, ba.rowid as bid, ba.ref as bref, ba.label as blabel, ba.number, ba.account_number as account_number, ba.fk_accountancy_journal as accountancy_journal, s.rowid as socid, s.nom as name, s.email FROM llx_paiement as p LEFT JOIN llx_c_paiement as c ON p.fk_paiement = c.id LEFT JOIN llx_bank as b ON p.fk_bank = b.rowid LEFT JOIN llx_bank_account as ba ON b.fk_account = ba.rowid LEFT JOIN llx_payment_invoice as pf ON p.rowid = pf.fk_payment LEFT JOIN llx_invoice as f ON pf.fk_invoice = f.rowid LEFT JOIN llx_company as s ON f.fk_soc = s.rowid WHERE p.entity IN (1) ORDER BY c.libelle ASC LIMIT 26
Last database access return code in error: DB_ERROR_3065
Information on the last database access error: Expression #1 of ORDER BY clause is not in SELECT list, references column 'dolibarr18.0.1.c.libelle' which is not in SELECT list; this is incompatible with DISTINCT."

Steps to reproduce the behavior

"The error occurs when I click on the 'type' column in the payment table dolibarr/compta/payment/list.php.

Attached files

No response

[atm-maxime]

Hi ! Thanks for the feedback.
Can you tell us how you get on this page "/compta/payment/list.php" because it doesn't exist in Dolibarr project. The one I found most approaching is "/compta/paiement/list.php"

[MechMachOth]

Ah, oui, je n'ai pas fait attention à cela. J'ai utilisé un traducteur et je pense qu'il a même traduit le lien. Je parlais de la même page '/compta/paiement/list.php'.

Voici l'erreur que j'ai rencontrée :
Dolibarr a détecté une erreur technique.
Ces informations peuvent être utiles à des fins de diagnostic (vous pouvez définir l'option $dolibarr_main_prod sur '1' pour masquer les informations sensibles) :
Date: 20240117203154
Dolibarr: 18.0.1 - https://www.dolibarr.org
Niveau de fonctionnalités: 0
PHP: 7.4.3-4ubuntu2.19
Server: Apache/2.4.41 (Ubuntu)
OS: Linux LinuxMint 5.4.0-169-generic #187-Ubuntu SMP Thu Nov 23 14:52:28 UTC 2023 x86_64
User Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36

Url sollicitée: /dolibarr/compta/paiement/list.php?sortfield=c.libelle&sortorder=desc&begin=&contextpage=paymentlist
Referer: http://localhost/dolibarr/compta/paiement/list.php?leftmenu=customers_bills_payment
Gestionnaire de menu: eldy_menu.php

Modules/Applications: syslog, user, api, contrat, ficheinter, supplier_proposal, incoterm, tax, loan, propal, productbatch, stock, product, expedition, projet, mailing, workflow, import, export, bookmark, fckeditor, gravatar, paypal, notification, clicktodial, blockedlog, agenda, banque, barcode, cron, commande, don, ecm, expensereport, fournisseur, holiday, margin, opensurvey, paybox, printing, resource, salaries, service, societe, modulebuilder, webhook, emailcollector, debugbar, knowledgemanagement, externalids, ticket, facture, prelevement, paymentbybanktransfer, recouvrement
Type gestionnaire de base de données: mysqli
Requête dernier accès en base en erreur: SELECT DISTINCT p.rowid, p.ref, p.datep, p.fk_bank, p.amount, p.statut, p.num_paiement, c.code as paiement_code, ba.rowid as bid, ba.ref as bref, ba.label as blabel, ba.number, ba.account_number as account_number, ba.fk_accountancy_journal as accountancy_journal, s.rowid as socid, s.nom as name, s.email FROM llx_paiement as p LEFT JOIN llx_c_paiement as c ON p.fk_paiement = c.id LEFT JOIN llx_bank as b ON p.fk_bank = b.rowid LEFT JOIN llx_bank_account as ba ON b.fk_account = ba.rowid LEFT JOIN llx_paiement_facture as pf ON p.rowid = pf.fk_paiement LEFT JOIN llx_facture as f ON pf.fk_facture = f.rowid LEFT JOIN llx_societe as s ON f.fk_soc = s.rowid WHERE p.entity IN (1) ORDER BY c.libelle DESC LIMIT 26
Code retour dernier accès en base en erreur: DB_ERROR_3065
Information sur le dernier accès en base en erreur: Expression #1 of ORDER BY clause is not in SELECT list, references column 'dolibarr18.0.1.c.libelle' which is not in SELECT list; this is incompatible with DISTINCT."

[hregis]

@MechMachOth @atm-maxime fixed wtih this PR #27643
thanks