Search and Customers search not working on PostgreSQL

[BullDoggen]

Using PostgreSQL and when I click on a some tag on TAGS list in Conversations Report I get following error

LINE 1: select "customers".*, "emails"."email" from "customers" left... ^ (SQL: select "customers".*, "emails"."email" from "customers" left join "emails" on "customers"."id" = "emails"."customer_id" where ("customers"."first_name" like %% or "customers"."last_name" like %% or "customers"."company" like %% or "customers"."job_title" like %% or "customers"."phones" like %% or "customers"."websites" like %% or "customers"."social_profiles" like %% or "customers"."address" like %% or "customers"."city" like %% or "customers"."state" like %% or "customers"."zip" like %% or "customers"."zip" like %% or "emails"."email" like %%) group by "customers"."id" limit 50 offset 0) {"userId":4,"email":"[email protected]","exception":"[object] (Illuminate\\Database\\QueryException(code: 42803): SQLSTATE[42803]: Grouping error: 7 ERROR: column \"emails.email\" must appear in the GROUP BY clause or be used in an aggregate function

[BullDoggen]

Now when I created new Kanban board, I get this error

LINE 1: select "kn_cards"."kn_column_id", count(*) as closed_count f... ^ (SQL: select "kn_cards"."kn_column_id", count(*) as closed_count from "conversations" inner join "kn_cards" on "kn_cards"."conversation_id" = "conversations"."id" where "state" = 2 and "kn_cards"."kn_board_id" = 1 and "kn_cards"."kn_swimlane_id" = 1 and "status" in (3) and "kn_cards"."kn_column_id" = 1) {"userId":4,"email":"[email protected]","exception":"[object] (Illuminate\\Database\\QueryException(code: 42803): SQLSTATE[42803]: Grouping error: 7 ERROR: column \"kn_cards.kn_column_id\" must appear in the GROUP BY clause or be used in an aggregate function

[freescout-helpdesk]

We'll check it.

[freescout-helpdesk]

Using PostgreSQL and when I click on a some tag on TAGS list in Conversations Report I get following error

LINE 1: select "customers".*, "emails"."email" from "customers" left... ^ (SQL: select "customers".*, "emails"."email" from "customers" left join "emails" on "customers"."id" = "emails"."customer_id" where ("customers"."first_name" like %% or "customers"."last_name" like %% or "customers"."company" like %% or "customers"."job_title" like %% or "customers"."phones" like %% or "customers"."websites" like %% or "customers"."social_profiles" like %% or "customers"."address" like %% or "customers"."city" like %% or "customers"."state" like %% or "customers"."zip" like %% or "customers"."zip" like %% or "emails"."email" like %%) group by "customers"."id" limit 50 offset 0) {"userId":4,"email":"[email protected]","exception":"[object] (Illuminate\\Database\\QueryException(code: 42803): SQLSTATE[42803]: Grouping error: 7 ERROR: column \"emails.email\" must appear in the GROUP BY clause or be used in an aggregate function

Does search work for you? Especially Customers search?

[megaerki]

Seeing the same thing on MySQL.

Turned on logging and found this query:
mysql> select customers.*,
-> emails.email
-> from customers
-> left join emails on customers.id = emails.customer_id
-> where (
-> customers.first_name like '%test%'
-> or customers.last_name like '%test%'
-> or customers.company like '%test%'
-> or customers.job_title like '%test%'
-> or customers.phones like '%test%'
-> or customers.websites like '%test%'
-> or customers.social_profiles like '%test%'
-> or customers.address like '%test%'
-> or customers.city like '%test%'
-> or customers.state like '%test%'
-> or customers.zip like '%test%'
-> or customers.zip like '%test%'
-> or emails.email like '%test%'
-> )
-> group by customers.id
-> limit 50 offset 0;

Getting this error:
ERROR 1055 (42000): Expression #22 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'freescout.emails.email' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

mysql version: 5.7.36-0ubuntu0.18.04.1 (Ubuntu)
freescout version: 1.7.28 or 1.7.29.

[BullDoggen]

Does search work for you? Especially Customers search?

No, seems that all searches log the same error

[freescout-helpdesk]

Try this /app/Http/Controllers/ConversationsController.php and let us know the result.

ConversationsController.zip

[BullDoggen]

Testing with given file gives Undefined variable: sort_by (View: /var/www/html/resources/views/conversations/conversations_table.blade.php) (View: /var/www/html/resources/views/conversations/conversations_table.blade.php)

[freescout-helpdesk]

Try this /app/Http/Controllers/ConversationsController.php and let us know the result.

ConversationsController.zip

Update FreeScout to the latest version before testing the patch.

[BullDoggen]

Original problem works now correctly with that php in zip file. But if I try to navigate to Manage -> Kanban I get this

LINE 1: select "kn_cards"."kn_column_id", count(*) as closed_count f... ^ (SQL: select "kn_cards"."kn_column_id", count(*) as closed_count from "conversations" inner join "kn_cards" on "kn_cards"."conversation_id" = "conversations"."id" where "state" = 2 and "kn_cards"."kn_board_id" = 1 and "kn_cards"."kn_swimlane_id" = 1 and "status" in (3) and "kn_cards"."kn_column_id" = 1) {"userId":4,"email":"[email protected]","exception":"[object] (Illuminate\\Database\\QueryException(code: 42803): SQLSTATE[42803]: Grouping error: 7 ERROR: column \"kn_cards.kn_column_id\" must appear in the GROUP BY clause or be used in an aggregate function

[freescout-helpdesk]

Original problem works now correctly with that php in zip file. But if I try to navigate to Manage -> Kanban I get this

LINE 1: select "kn_cards"."kn_column_id", count(*) as closed_count f... ^ (SQL: select "kn_cards"."kn_column_id", count(*) as closed_count from "conversations" inner join "kn_cards" on "kn_cards"."conversation_id" = "conversations"."id" where "state" = 2 and "kn_cards"."kn_board_id" = 1 and "kn_cards"."kn_swimlane_id" = 1 and "status" in (3) and "kn_cards"."kn_column_id" = 1) {"userId":4,"email":"[email protected]","exception":"[object] (Illuminate\\Database\\QueryException(code: 42803): SQLSTATE[42803]: Grouping error: 7 ERROR: column \"kn_cards.kn_column_id\" must appear in the GROUP BY clause or be used in an aggregate function

This has been fixed in the latest version of Kanban Module.