starred/SuiteCRM-SuiteCRM

Fork 0

mirror of https://github.com/SuiteCRM/SuiteCRM.git synced 2026-03-02 19:16:58 -05:00

AOS_Products detail view does not diplay subpanels #1578

New issue

Closed

opened 2026-02-20 15:11:46 -05:00 by deekerman · 7 comments

deekerman commented

2026-02-20 15:11:46 -05:00

Owner

Originally created by @boombata on GitHub (Jan 6, 2017).

Issue

added a one to many relationship to AOS_Products but the subpanel is not present in the product detail view.

Expected Behavior

Products must be able to display custom relation ship subpanel

Actual Behavior

no subpanel present

Possible Fix

edit modules/AOS_Products\metadata\subpaneldefs.php

current
line53 //'get_subpanel_data' => 'aos_products_aos_quotes_1',
line54 'get_subpanel_data' => 'function:getCustomersPurchasedProductsQuery',

modified
line53 'get_subpanel_data' => 'aos_products_aos_quotes_1',
line54 //'get_subpanel_data' => 'function:getCustomersPurchasedProductsQuery',

after this modification my custom relationships are visible, this point to be an issue with function:getCustomersPurchasedProductsQuery

Steps to Reproduce

add a one to many relationship to products
quickrepair
3.no subpanel present.

Context

Your Environment

SuiteCRM Version used: 7.7.8
Browser name and version (e.g. Chrome Version 51.0.2704.63 (64-bit)): firefox 50.1.0
Environment name and version (e.g. MySQL, PHP 7):PHP 7
Operating System and version (e.g Ubuntu 16.04):Ubuntu 16.04

Originally created by @boombata on GitHub (Jan 6, 2017).   #### Issue  added a one to many relationship to AOS_Products but the subpanel is not present in the product detail view. #### Expected Behavior Products must be able to display custom relation ship subpanel #### Actual Behavior no subpanel present #### Possible Fix  edit modules/AOS_Products\metadata\subpaneldefs.php current line53 //'get_subpanel_data' => 'aos_products_aos_quotes_1', line54 'get_subpanel_data' => 'function:getCustomersPurchasedProductsQuery', modified line53 'get_subpanel_data' => 'aos_products_aos_quotes_1', line54 //'get_subpanel_data' => 'function:getCustomersPurchasedProductsQuery', after this modification my custom relationships are visible, this point to be an issue with function:getCustomersPurchasedProductsQuery #### Steps to Reproduce   1. add a one to many relationship to products 2. quickrepair 3.no subpanel present. 4. #### Context   #### Your Environment  * SuiteCRM Version used: 7.7.8 * Browser name and version (e.g. Chrome Version 51.0.2704.63 (64-bit)): firefox 50.1.0 * Environment name and version (e.g. MySQL, PHP 7):PHP 7 * Operating System and version (e.g Ubuntu 16.04):Ubuntu 16.04

deekerman

2026-02-20 15:11:46 -05:00

closed this issue
added the
Type: Bug

Priority:Moderate
labels

deekerman commented

2026-02-20 15:11:48 -05:00

Author

Owner

@boombata commented on GitHub (Jan 6, 2017):

located root cause of the issue:
function:getCustomersPurchasedProductsQuery in modules/AOS_Products/AOS_Products.php
failed with error:
MySQL error 1248: Every derived table must have its own alias

removing the first "SELECT * FROM(" and ")AS aos_quotes" is solving the problem and all subsections are displayed, the problem is in the sql query structure but cannot determine where.

@boombata commented on GitHub (Jan 6, 2017): located root cause of the issue: function:getCustomersPurchasedProductsQuery in modules/AOS_Products/AOS_Products.php failed with error: MySQL error 1248: Every derived table must have its own alias removing the first "SELECT * FROM(" and ")AS aos_quotes" is solving the problem and all subsections are displayed, the problem is in the sql query structure but cannot determine where.

deekerman commented

2026-02-20 15:11:48 -05:00

Author

Owner

@boombata commented on GitHub (Jan 6, 2017):

did more test, it is not a bug of suitecrm but mysql config that needed to be adjusted

mysql> SELECT aos_quotes.*, accounts.id AS account_id, accounts.name AS billing_account, opportunity_id AS opportunity, billing_contact_id AS billing_contact, '' AS created_by_name, '' AS modified_by_name, '' AS assigned_user_name FROM aos_products JOIN aos_products_quotes ON aos_products_quotes.product_id = aos_products.id AND aos_products.id = 'a2445c16-ab5b-8ee0-8b7f-586d10b81c2c' AND aos_products_quotes.deleted = 0 AND aos_products.deleted = 0 JOIN aos_quotes ON aos_quotes.id = aos_products_quotes.parent_id AND aos_quotes.stage = 'Closed Accepted' AND aos_quotes.deleted = 0 JOIN accounts ON accounts.id = aos_quotes.billing_account_id GROUP BY accounts.id;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'suitecrm.aos_quotes.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql> SELECT @@GLOBAL.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

so i removed it from mysqld
edited my.cnf
[mysqld]
sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

restarted mysql and now subpanels appear.

do you have recommendations on mysql settings ?

@boombata commented on GitHub (Jan 6, 2017): did more test, it is not a bug of suitecrm but mysql config that needed to be adjusted > mysql> SELECT aos_quotes.*, accounts.id AS account_id, accounts.name AS billing_account, opportunity_id AS opportunity, billing_contact_id AS billing_contact, '' AS created_by_name, '' AS modified_by_name, '' AS assigned_user_name FROM aos_products JOIN aos_products_quotes ON aos_products_quotes.product_id = aos_products.id AND aos_products.id = 'a2445c16-ab5b-8ee0-8b7f-586d10b81c2c' AND aos_products_quotes.deleted = 0 AND aos_products.deleted = 0 JOIN aos_quotes ON aos_quotes.id = aos_products_quotes.parent_id AND aos_quotes.stage = 'Closed Accepted' AND aos_quotes.deleted = 0 JOIN accounts ON accounts.id = aos_quotes.billing_account_id GROUP BY accounts.id; ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'suitecrm.aos_quotes.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by mysql> SELECT @@GLOBAL.sql_mode; +-------------------------------------------------------------------------------------------------------------------------------------------+ | @@GLOBAL.sql_mode | +-------------------------------------------------------------------------------------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +-------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) so i removed it from mysqld edited my.cnf [mysqld] sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" restarted mysql and now subpanels appear. do you have recommendations on mysql settings ?

deekerman commented

2026-02-20 15:11:48 -05:00

Author

Owner

@valcosoftj commented on GitHub (Feb 8, 2018):

Can the group clause just be removed ?, so its works properly on all databases

@valcosoftj commented on GitHub (Feb 8, 2018): Can the group clause just be removed ?, so its works properly on all databases

deekerman commented

2026-02-20 15:11:48 -05:00

Author

Owner

@gerdb42 commented on GitHub (Apr 15, 2020):

Had the same issue with 7.11.12 running against MSSQL 2014. Worked around it by a) removing the outer SELECT and b) removing the GROUP BY and doing a SELECT DISTINCT. Maybe someone can find a DB independent solution.

@gerdb42 commented on GitHub (Apr 15, 2020): Had the same issue with 7.11.12 running against MSSQL 2014. Worked around it by a) removing the outer SELECT and b) removing the GROUP BY and doing a SELECT DISTINCT. Maybe someone can find a DB independent solution.

deekerman commented

2026-02-20 15:11:48 -05:00

Author

Owner

@pgorod commented on GitHub (Apr 15, 2020):

Can you please post the specific code you used?

@pgorod commented on GitHub (Apr 15, 2020): Can you please post the specific code you used?

deekerman commented

2026-02-20 15:11:48 -05:00

Author

Owner

@gerdb42 commented on GitHub (Apr 15, 2020):

Here are my changes to modules\AOS_Products\AOS_Products.php:

99,100c98,99
<  			-- SELECT * FROM (
<  				SELECT DISTINCT
---
>  			SELECT * FROM (
>  				SELECT
117,118c116,117
< 				-- GROUP BY aos_quotes.id
< 			-- ) AS aos_quotes
---
> 				GROUP BY aos_quotes.id
> 			) AS aos_quotes

@gerdb42 commented on GitHub (Apr 15, 2020): Here are my changes to modules\AOS_Products\AOS_Products.php: ``` 99,100c98,99 < -- SELECT * FROM ( < SELECT DISTINCT --- > SELECT * FROM ( > SELECT 117,118c116,117 < -- GROUP BY aos_quotes.id < -- ) AS aos_quotes --- > GROUP BY aos_quotes.id > ) AS aos_quotes ```

deekerman commented

2026-02-20 15:11:48 -05:00

Author

Owner

@Kevconry commented on GitHub (Feb 7, 2023):

Can't replicate in 7.13.1, please re-raise or get in touch if you are still having this issue

@Kevconry commented on GitHub (Feb 7, 2023): Can't replicate in 7.13.1, please re-raise or get in touch if you are still having this issue

deekerman referenced this issue

2026-02-20 16:05:02 -05:00

Multiple bounce handling problems #3408