Bug: PostgreSQL Grouping Error

[aljawaid]

Forum Reported

https://kanboard.discourse.group/t/new-plugin-addressbook/2845/15?u=aljawaid

Internal Error: SQL Error[42803]: SQLSTATE[42803]: Grouping error: 7 ERROR: column “address_book_contacts_contact.item_id” must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT * FROM “address_book_contacts_contact” LEFT JOIN "ad… ^

[Teolone88]

I have fixed it by adding all the grouping of this world in these files /plugins/AddressBook/Model/...
DATABASE DRIVER postgres
DATABASE VERSION 15.6 (Debian 15.6-0+deb12u1)
OPERATING SYSTEM Linux 4.19.0 x86_64
PHP VERSION 8.3.6

    public function getAll()
    {
        $firstPosition = $this->db->table(ContactsItemsModel::TABLE)->columns('id')->eq('position', 1)->findOne();

        return $this->db
            ->table(self::TABLE)
            ->join(ContactsItemsModel::TABLE, 'id', 'item_id')
## Group everything possible
        	->groupBy('contacts_id', 'item_id', self::TABLE . '.contact_item_value', self::TABLE . '.updated_by_user_id', self::TABLE . '.last_updated', ContactsItemsModel::TABLE, 'id')
            ->asc(ContactsItemsModel::TABLE . '.position')
            ->asc(self::TABLE . '.contact_item_value')
            ->findAll();
    }

    public function getLinked($task_id)
    {
        $firstPosition = $this->db->table(ContactsItemsModel::TABLE)->columns('id')->eq('position', 1)->findOne();
        return $this->db
            ->table(self::TABLE)
            ->eq('task_id', $task_id)
            ->columns(
                self::TABLE . '.contacts_id',
                ContactsModel::TABLE . '.contact_item_value'
            )
            ->join(ContactsModel::TABLE, 'contacts_id', 'contacts_id')
            ->join(ContactsItemsModel::TABLE, 'id', 'item_id', ContactsModel::TABLE)
            ->asc(ContactsItemsModel::TABLE . '.position')
## Group everything possible
            ->groupBy(self::TABLE . '.contacts_id', ContactsModel::TABLE . '.contact_item_value', ContactsItemsModel::TABLE . '.position')
            ->asc(ContactsModel::TABLE . '.contact_item_value')
            ->findAll();
    }

    public function getByID($contacts_id)
    {
        $contact = $this->db
            ->table(self::TABLE)
            ->eq('contacts_id', $contacts_id)
            ->join(ContactsItemsModel::TABLE, 'id', 'item_id')
## Group everything possible
        	->groupBy(self::TABLE . '.contact_item_value', self::TABLE . '.contacts_id', self::TABLE . '.item_id', self::TABLE . '.updated_by_user_id', self::TABLE . '.last_updated', ContactsItemsModel::TABLE . '.id')
            ->asc(ContactsItemsModel::TABLE . '.position')
            ->findAll();

        $return = array();
        foreach ($contact as $key => $value) {
            $return[$value['id']] = $value;
        }

        return $return;
    }

    public function getAll()
    {
        $firstPosition = $this->db->table(ContactsItemsModel::TABLE)->columns('id')->eq('position', 1)->findOne();

        return $this->db
            ->table(self::TABLE)
            ->join(ContactsItemsModel::TABLE, 'id', 'item_id')
## Group everything possible
        	->groupBy('contacts_id', 'item_id', self::TABLE . '.contact_item_value', self::TABLE . '.updated_by_user_id', self::TABLE . '.last_updated', ContactsItemsModel::TABLE, 'id')
            ->asc(ContactsItemsModel::TABLE . '.position')
            ->asc(self::TABLE . '.contact_item_value')
            ->findAll();
    }

etc...