Bug: `Amibiguous Column Name` in SQLite When Viewing Tasks

[aljawaid]

• When opening a task Internal Error: SQL Error: SQLSTATE[HY000]: General error: 1 ambiguous column name: contacts_id

Originally posted by @PopovIG in https://github.com/aljawaid/AddressBook/issues/6#issuecomment-1618129357

[aljawaid]

@PopovIG could you please try the below?

In line 35-36: https://github.com/aljawaid/AddressBook/blob/09d9f314f903f05380cf3d9b4a0853aa290a0499/Schema/Sqlite.php#L35-L36

Change it to

contact_id INT NOT NULL,
FOREIGN KEY(contact_id) REFERENCES address_book_contacts_contact(contacts_id) ON DELETE CASCADE,

[PopovIG]

Just changing these lines didn't help:

_Task priority P0 Internal Error: SQL Error[HY000]: SQLSTATE[HY000]: General error: 1 no such column: address_book_contacts_task_has_contact.contactsid

I decided to experiment a little and change the files:

• Model\ContactsTaskModel.php;
• Template\task\link-contacts.php;
• Template\task\description.php.

Replaced in the necessary places contacts_id to contact_id. Now it works. But I don't think this is the right solution - i got the solution for my sqlite, but probably broke everything for other types of databases.

I also made another change to the sqlite schema - added "id" column to table "address_book_contacts_contact" and added "PRIMARY".

$pdo->exec('CREATE TABLE IF NOT EXISTS address_book_contacts_contact (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    contacts_id INTEGER NOT NULL,
    item_id INT NOT NULL,
    contact_item_value TEXT NOT NULL,
    updated_by_user_id INT NOT NULL,
    last_updated INTEGER NOT NULL,
    FOREIGN KEY(item_id) REFERENCES address_book_contacts_items(id) ON DELETE CASCADE
)');

/* CREATE LINKS TO TASKS WITH CONTACTS */
$pdo->exec('CREATE TABLE IF NOT EXISTS address_book_contacts_task_has_contact (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    task_id INT NOT NULL,
    contact_id INT NOT NULL,
    FOREIGN KEY(contact_id) REFERENCES address_book_contacts_contact(id) ON DELETE CASCADE,
    FOREIGN KEY(task_id) REFERENCES tasks(id) ON DELETE CASCADE
)');

in the case of "contacts_id INTEGER PRIMARY KEY NOT NULL," there was still an error with saving the contact in some cases (I could not understand in which ones) ((

In my case, everything works now)) Hope my comments are helpful. I will continue to follow the work on the plugin - I really liked it.

[aljawaid]

@PopovIG Thanks for the explanation. Unless you fork my repo and push your changes (to your repo or mine), I can't specifically look at the edits you made.

I will leave this issue open in case anybody else may have a solution, as I dont have SQLite to test it properly and find a solution.

[aljawaid]

I think I may have solved it, for sqlite