Error when exporting reports

[azamat028]

Hi, there is an error when downloading any reports SQLSTATE[42712]: Duplicate alias: 7 ERROR: table name "customers" specified more than once (SQL: select "conversations"."id", "conversations"."number", "conversations"."type", "conversations"."user_id", "conversations"."status", "conversations"."state", "conversations"."mailbox_id", "conversations"."customer_id" as "customer_name", "conversations"."customer_email", "conversations"."threads_count", "conversations"."subject", "conversations"."cc", "conversations"."bcc", "conversations"."has_attachments", "conversations"."channel", "conversations"."created_at", "conversations"."last_reply_at", "conversations"."last_reply_from", "conversations"."closed_at", "conversations"."closed_by_user_id", "customers"."first_name" as "customer_first_name", "customers"."last_name" as "customer_last_name" from "conversations" left join "customers" on "customers"."id" = "conversations"."customer_id" inner join "threads" on "conversations"."id" = "threads"."conversation_id" left join "customers" on "conversations"."customer_id" = "customers"."id" where "conversations"."mailbox_id" in (3, 2, 1) group by "conversations"."id" order by "last_reply_at" desc) {"userId":17,"email":"smana@kacd.kz","exception":"[object] (Illuminate\Database\QueryException(code: 42712): SQLSTATE[42712]: Duplicate alias: 7 ERROR: table name \"customers\" specified more than once (SQL: select \"conversations\".\"id\", \"conversations\".\"number\", \"conversations\".\"type\", \"conversations\".\"user_id\", \"conversations\".\"status\", \"conversations\".\"state\", \"conversations\".\"mailbox_id\", \"conversations\".\"customer_id\" as \"customer_name\", \"conversations\".\"customer_email\", \"conversations\".\"threads_count\", \"conversations\".\"subject\", \"conversations\".\"cc\", \"conversations\".\"bcc\", \"conversations\".\"has_attachments\", \"conversations\".\"channel\", \"conversations\".\"created_at\", \"conversations\".\"last_reply_at\", \"conversations\".\"last_reply_from\", \"conversations\".\"closed_at\", \"conversations\".\"closed_by_user_id\", \"customers\".\"first_name\" as \"customer_first_name\", \"customers\".\"last_name\" as \"customer_last_name\" from \"conversations\" left join \"customers\" on \"customers\".\"id\" = \"conversations\".\"customer_id\" inner join \"threads\" on \"conversations\".\"id\" = \"threads\".\"conversation_id\" left join \"customers\" on \"conversations\".\"customer_id\" = \"customers\".\"id\" where \"conversations\".\"mailbox_id\" in (3, 2, 1) group by \"conversations\".\"id\" order by \"last_reply_at\" desc) at /opt/helpdesk/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664, Doctrine\DBAL\Driver\PDO\Exception(code: 42712): SQLSTATE[42712]: Duplicate alias: 7 ERROR: table name \"customers\" specified more than once at /opt/helpdesk/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php:18, PDOException(code: 42712): SQLSTATE[42712]: Duplicate alias: 7 ERROR: table name \"customers\" specified more than once at /opt/helpdesk/overrides/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:115) laravel-2024-08-05.log

PHP version: PHP 8.1.2-1ubuntu2.15 FreeScout version: 1.8.146 Database: Pgsql (13.8 (Ubuntu 13.8-1.pgdg20.04+1)) Web-server: nginx/1.18.0 Are you using CloudFlare: No Are you using non-official modules: No Export Conversations: 1.0.10 Reports: 1.0.42

I deleted and reinstalled the export module did not help And as I noticed, disabling the check mark from Customer Name and you can download the report, but I need exactly Customer Name

[freescout-help]

Try to add the following code to the line 94 in /Modules/ExportConversations/Http/Controllers/ExportConversationsController.php:

        echo "<pre>";
        print_r($query->toSql());
        exit();

After that try to export conversations and send the output you'll get.

[azamat028]

select * from "conversations" left join "customers" on "customers"."id" = "conversations"."customer_id" inner join "threads" on "conversations"."id" = "threads"."conversation_id" left join "customers" on "conversations"."customer_id" = "customers"."id" where "conversations"."mailbox_id" in (?, ?, ?) and "conversations"."status" in (?, ?, ?) and "conversations"."state" = ? and "conversations"."created_at" >= ? and "conversations"."created_at" <= ? group by "conversations"."id" order by "last_reply_at" desc

[freescout-help]

Now remove previously added code and replace line 2366 in /app/Conversation.php with the following code:

if (!preg_match('#[`"]customers[`"]\.[`"]id[`"]#', $query_sql)) {

And try to export conversations. If there will be some error - send us its text.

[azamat028]

Call to a member function select() on null {"userId":17,"email":"smana@kacd.kz","exception":"[object] (Symfony\Component\Debug\Exception\FatalThrowableError(code: 0): Call to a member function select() on null at /opt/helpdesk/Modules/ExportConversations/Http/Controllers/ExportConversationsController.php:165)

[freescout-help]

You'll need to find someone who can check it on your server or email at support@freescout.net

[azamat028]

Did I add it correctly? Now I get this error SQLSTATE[42803]: Grouping error: 7 ERROR: column "customers.first_name" must appear in the GROUP BY clause or be used in an aggregate function

[azamat028]

I solved the problem by adding a string for grouping $query_conversations->groupBy('conversations.id', 'conversations.number', 'conversations.type', 'conversations.user_id', 'conversations.status', 'conversations.state', 'conversations.mailbox_id','conversations.customer_id', 'conversations.customer_email', 'conversations.threads_count', 'conversations.subject', 'conversations.cc', 'conversations.bcc', 'conversations.has_attachments', 'conversations.channel', 'conversations.created_at', 'conversations.last_reply_at', 'conversations.last_reply_from', 'conversations.closed_at', 'conversations.closed_by_user_id', 'customers.first_name', 'customers.last_name');

[azamat028]

Now I have another mistake, too, about duplicate aliases SQLSTATE[42712]: Duplicate alias: 7 ERROR: table name "threads" specified more than once (SQL: select "conversations"."id", "conversations"."number", "conversations"."type", "conversations"."user_id", "conversations"."status", "conversations"."state", "conversations"."mailbox_id", "conversations"."customer_id" as "customer_name", "conversations"."customer_email", "conversations"."threads_count", "conversations"."subject", "conversations"."cc", "conversations"."bcc", "conversations"."has_attachments", "conversations"."channel", "conversations"."created_at", "conversations"."last_reply_at", "conversations"."last_reply_from", "conversations"."closed_at", "conversations"."closed_by_user_id", "customers"."first_name" as "customer_first_name", "customers"."last_name" as "customer_last_name", string_agg(threads.rating, ', ') as sat_ratings from "conversations" left join "customers" on "customers"."id" = "conversations"."customer_id" inner join "threads" on "conversations"."id" = "threads"."conversation_id" inner join "threads" on "conversations"."id" = "threads"."conversation_id" where "conversations"."mailbox_id" in (3, 2, 1) and "conversations"."status" in (1, 2, 3) and "conversations"."state" = 2 and "conversations"."created_at" >= 2024-07-30 00:00:00 and "conversations"."created_at" <= 2024-08-06 23:59:59 group by "conversations"."id", "conversations"."id", "conversations"."number", "conversations"."type", "conversations"."user_id", "conversations"."status", "conversations"."state", "conversations"."mailbox_id", "conversations"."customer_id", "conversations"."customer_email", "conversations"."threads_count", "conversations"."subject", "conversations"."cc", "conversations"."bcc", "conversations"."has_attachments", "conversations"."channel", "conversations"."created_at", "conversations"."last_reply_at", "conversations"."last_reply_from", "conversations"."closed_at", "conversations"."closed_by_user_id", "customers"."first_name", "customers"."last_name" order by "last_reply_at" desc) {"userId":17,"email":"smana@kacd.kz","exception":"[object] (Illuminate\Database\QueryException(code: 42712): SQLSTATE[42712]: Duplicate alias: 7 ERROR: table name \"threads\" specified more than once (SQL: select \"conversations\".\"id\", \"conversations\".\"number\", \"conversations\".\"type\", \"conversations\".\"user_id\", \"conversations\".\"status\", \"conversations\".\"state\", \"conversations\".\"mailbox_id\", \"conversations\".\"customer_id\" as \"customer_name\", \"conversations\".\"customer_email\", \"conversations\".\"threads_count\", \"conversations\".\"subject\", \"conversations\".\"cc\", \"conversations\".\"bcc\", \"conversations\".\"has_attachments\", \"conversations\".\"channel\", \"conversations\".\"created_at\", \"conversations\".\"last_reply_at\", \"conversations\".\"last_reply_from\", \"conversations\".\"closed_at\", \"conversations\".\"closed_by_user_id\", \"customers\".\"first_name\" as \"customer_first_name\", \"customers\".\"last_name\" as \"customer_last_name\", string_agg(threads.rating, ', ') as sat_ratings from \"conversations\" left join \"customers\" on \"customers\".\"id\" = \"conversations\".\"customer_id\" inner join \"threads\" on \"conversations\".\"id\" = \"threads\".\"conversation_id\" inner join \"threads\" on \"conversations\".\"id\" = \"threads\".\"conversation_id\" where \"conversations\".\"mailbox_id\" in (3, 2, 1) and \"conversations\".\"status\" in (1, 2, 3) and \"conversations\".\"state\" = 2 and \"conversations\".\"created_at\" >= 2024-07-30 00:00:00 and \"conversations\".\"created_at\" <= 2024-08-06 23:59:59 group by \"conversations\".\"id\", \"conversations\".\"id\", \"conversations\".\"number\", \"conversations\".\"type\", \"conversations\".\"user_id\", \"conversations\".\"status\", \"conversations\".\"state\", \"conversations\".\"mailbox_id\", \"conversations\".\"customer_id\", \"conversations\".\"customer_email\", \"conversations\".\"threads_count\", \"conversations\".\"subject\", \"conversations\".\"cc\", \"conversations\".\"bcc\", \"conversations\".\"has_attachments\", \"conversations\".\"channel\", \"conversations\".\"created_at\", \"conversations\".\"last_reply_at\", \"conversations\".\"last_reply_from\", \"conversations\".\"closed_at\", \"conversations\".\"closed_by_user_id\", \"customers\".\"first_name\", \"customers\".\"last_name\" order by \"last_reply_at\" desc) at /opt/helpdesk/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664, Doctrine\DBAL\Driver\PDO\Exception(code: 42712): SQLSTATE[42712]: Duplicate alias: 7 ERROR: table name \"threads\" specified more than once at /opt/helpdesk/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php:18, PDOException(code: 42712): SQLSTATE[42712]: Duplicate alias: 7 ERROR: table name \"threads\" specified more than once at /opt/helpdesk/overrides/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:115)

[freescout-help]

https://github.com/freescout-help-desk/freescout/issues/4167#issuecomment-2275599428