Topic on Extension talk:CIForms

Im getting this error when trying to submit a CIForm:

Error 42P01: relation "CIForms_submissions" does not exist

I've successfully followed all the installation steps (I did skip step 2: "Run composer update inside the folder extensions/CIForms" because i dont need the email functionality).

We are using Postgres and it looks like CIForms.php is only making the the DB changes if mysql is being used?

Is it correct that only mysql is supported? if so, what would it take to get this working with Postgres?

Thanks in advance!

Update: I've been able to get Forms to submit successfully by

1. translating the two files in "CIForms/sql/" from mysql to postgres

2. making some minor changes to "CIForms/includes/specials/CIFormsSubmit.php" & "CIForms/includes/specials/CIFormsManage.php" (changing any references to the "CIForms..." tables to lower case i.e. "ciforms...")

But now i get a new database error when trying to view the results at Special:CIFormsManage:

Error 42803: ERROR: column "ciforms_submissions.id" must appear in the GROUP BY clause or be used in an aggregate function

LINE 1: SELECT /* CIFormsManage::reallyDoQuery */ ciforms_submissio...

Function: CIFormsManage::reallyDoQuery

Query: SELECT ciforms_submissions.*,

COUNT(*) as submissions,

(SELECT MAX(created_at)

FROM ciforms_submissions as b

WHERE b.title = ciforms_submissions.title

) as last_submission_date,

SUM(CASE WHEN shown IS NULL THEN 1 ELSE 0 END) as new

FROM ciforms_submissions

GROUP BY ciforms_submissions.page_id, ciforms_submissions.title

ORDER BY last_submission_date DESC LIMIT 51 OFFSET 0

It seems to originate from the database query that results from line 413 in "CIForms/includes/specials/CIFormsManage.php"

Im afraid i've reached the limits of my troubleshooting capabilities. Do you have any ideas to help me view the results in given our Postgres database?

Thanks again!

Update #2:

I changed line 457 in "CIForms/includes/specials/CIFormsManage.php" to:

GROUP BY ciforms_submissions.id, ciforms_submissions.page_id, ciforms_submissions.title

and now i can view the results! So it seems to be working now.

@YahboobayYahboobay thanks for reporting, so the issue is related to camelcase table names, right ?

@Thomas-topway-it, Sorry that my comments may have been hard to follow, I wasn't able to properly format the update comments. To summarize, in the end, there were four things to get it working with Postgres:

1. The two .sql files had to be translated from Mysql to Postgres. (CIForms_submissions.sql and CIForms_submissions_groups.sql)
2. Then there's the capitalization issue (postgres apparently defaults to lower case) which required changing all sql references to the database table "CIForms_..." to "ciforms_..." (in CIFormsManage.php and CIFormsSubmit.php)
3. Finally one small change to line 457 in CIFormsManage.php made an "ERROR: column must appear in the GROUP BY clause or be used in an aggregate function" error go away. This was changing GROUP BY ciforms_submissions.page_id, ciforms_submissions.title to GROUP BY ciforms_submissions.id, ciforms_submissions.page_id, ciforms_submissions.title
4. adjust the if statement on line 130 of CIForms.php which checks for mysql before proceeding

Hopefully that helps? Thank you for the great work on this extension!

@YahboobayYahboobay thanks for your notes, I've included them in the latest version. Can you check that everything work fine on your side ? You'll also find the full list of new features in the main page of the extension.