Page 1 of 2
Too many tables when fetching order items
Posted: Wed Feb 06, 2013 5:56 am
by denis
Hi,
With BLC 2.2.1-SNASPHOT when i select an order, an exception is showing up :
//EX[2,1,["org.broadleafcommerce.common.exception.ServiceException/1744595097","Unable to fetch results for org.broadleafcommerce.core.order.domain.OrderItem"],0,7]
Code: Select all
[ERROR] 11:53:29 BasicPersistenceModule - Problem fetching results for org.broadleafcommerce.core.order.domain.OrderItem
org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:140)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:128)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2545)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
at org.hibernate.loader.Loader.list(Loader.java:2271)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:119)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1716)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:347)
Code: Select all
Caused by: java.sql.SQLException: Too many tables; MySQL can only use 61 tables in a join
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4074)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4006)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2468)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2629)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2719)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2318)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1953)
at org.hibernate.loader.Loader.doQuery(Loader.java:802)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
at org.hibernate.loader.Loader.doList(Loader.java:2542)
I haven't this problem with BLC 2.1.3-SNAPSHOT. Is there a way to avoid this problem?
Re: Too many tables when fetching order items
Posted: Wed Feb 06, 2013 9:59 am
by phillipuniverse
Haven't seen this one before. Which BLC entities have you extended that might be apart of this query? (OrderItem, Sku, Product, etc)
Re: Too many tables when fetching order items
Posted: Wed Feb 06, 2013 10:34 am
by denis
Hi Phillip,
yep we have extended several entities :
- MyDiscreteOrderItem extends DiscreteOrderItemImpl
- MyProduct extends ProductImpl
- MyCustomer extends CustomerImpl
- MySku extends SkuImpl
- MyOrder extends OrderImpl
I don't know how to make it works
Re: Too many tables when fetching order items
Posted: Wed Feb 06, 2013 11:02 am
by phillipuniverse
Hm odd that you never saw this in 2.1. This shouldn't really be specific to the BLC version since it's theoretically doing the same joins.
Could you put debug logging on Hibernate to get the actual query it's attempting to execute?
Re: Too many tables when fetching order items
Posted: Wed Feb 06, 2013 11:05 am
by phillipuniverse
And this only happens on the admin, right? Seems like it would also effect the frontend when attempting to get a list of OrderItems for an Order. Although, accessing the list directly from the List<OrderItem> on Order maybe executes a slightly different query that excludes some joins (although I'm not sure why).
Re: Too many tables when fetching order items
Posted: Wed Feb 06, 2013 11:10 am
by denis
Yes this only happens when i click on an order on the admin. Below the query executed by hibernate to retrieve the orderItem :
Code: Select all
Hibernate:
select this_.ORDER_ITEM_ID as ORDER1_58_20_,
this_.CATEGORY_ID as CATEGORY10_58_20_,
this_.GIFT_WRAP_ITEM_ID as GIFT11_58_20_,
this_.ITEM_TAXABLE_FLAG as ITEM2_58_20_,
this_.NAME as NAME58_20_,
this_.ORDER_ID as ORDER12_58_20_,
this_.ORDER_ITEM_TYPE as ORDER4_58_20_,
this_.PERSONAL_MESSAGE_ID as PERSONAL13_58_20_,
this_.PRICE as PRICE58_20_,
this_.QUANTITY as QUANTITY58_20_,
this_.RETAIL_PRICE as RETAIL7_58_20_,
this_.SALE_PRICE as SALE8_58_20_,
this_.SPLIT_PARENT_ITEM_ID as SPLIT9_58_20_,
this_1_.BASE_RETAIL_PRICE as BASE1_59_20_,
this_1_.BASE_SALE_PRICE as BASE2_59_20_,
this_1_.PRODUCT_BUNDLE_ID as PRODUCT4_59_20_,
this_1_.SKU_ID as SKU5_59_20_,
this_2_.BASE_RETAIL_PRICE as BASE1_60_20_,
this_2_.BASE_SALE_PRICE as BASE2_60_20_,
this_2_.BUNDLE_ORDER_ITEM_ID as BUNDLE4_60_20_,
this_2_.PRODUCT_ID as PRODUCT5_60_20_,
this_2_.SKU_ID as SKU6_60_20_,
this_2_.SKU_BUNDLE_ITEM_ID as SKU7_60_20_,
this_5_.ITEM_STATUT as ITEM1_161_20_,
this_5_.SUBSCRIPTION_ID as SUBSCRIP3_161_20_,
case when this_3_.ORDER_ITEM_ID is not null
then 3 when this_4_.ORDER_ITEM_ID is not null
then 4 when this_5_.ORDER_ITEM_ID is not null
then 5 when this_1_.ORDER_ITEM_ID is not null
then 1 when this_2_.ORDER_ITEM_ID is not null
then 2 when this_.ORDER_ITEM_ID is not null
then 0 end as clazz_20_,
categoryim3_.CATEGORY_ID as CATEGORY1_25_0_,
categoryim3_.ACTIVE_END_DATE as ACTIVE2_25_0_,
categoryim3_.ACTIVE_START_DATE as ACTIVE3_25_0_,
categoryim3_.ARCHIVED as ARCHIVED25_0_,
categoryim3_.DEFAULT_PARENT_CATEGORY_ID as DEFAULT13_25_0_,
categoryim3_.DESCRIPTION as DESCRIPT5_25_0_,
categoryim3_.DISPLAY_TEMPLATE as DISPLAY6_25_0_,
categoryim3_.FULFILLMENT_TYPE as FULFILLM7_25_0_,
categoryim3_.INVENTORY_TYPE as INVENTORY8_25_0_,
categoryim3_.LONG_DESCRIPTION as LONG9_25_0_,
categoryim3_.NAME as NAME25_0_,
categoryim3_.URL as URL25_0_,
categoryim3_.URL_KEY as URL12_25_0_,
categoryim3_1_.SITE_ID as SITE2_148_0_,
case when categoryim3_1_.CATEGORY_ID is not null
then 1 when categoryim3_.CATEGORY_ID is not null
then 0 end as clazz_0_,
categoryim4_.CATEGORY_ID as CATEGORY1_25_1_,
categoryim4_.ACTIVE_END_DATE as ACTIVE2_25_1_,
categoryim4_.ACTIVE_START_DATE as ACTIVE3_25_1_,
categoryim4_.ARCHIVED as ARCHIVED25_1_,
categoryim4_.DEFAULT_PARENT_CATEGORY_ID as DEFAULT13_25_1_,
categoryim4_.DESCRIPTION as DESCRIPT5_25_1_,
categoryim4_.DISPLAY_TEMPLATE as DISPLAY6_25_1_,
categoryim4_.FULFILLMENT_TYPE as FULFILLM7_25_1_,
categoryim4_.INVENTORY_TYPE as INVENTORY8_25_1_,
categoryim4_.LONG_DESCRIPTION as LONG9_25_1_,
categoryim4_.NAME as NAME25_1_,
categoryim4_.URL as URL25_1_,
categoryim4_.URL_KEY as URL12_25_1_,
categoryim4_1_.SITE_ID as SITE2_148_1_,
case when categoryim4_1_.CATEGORY_ID is not null
then 1 when categoryim4_.CATEGORY_ID is not null
then 0 end as clazz_1_,
siteimpl5_.SITE_ID as SITE1_7_2_,
siteimpl5_.NAME as NAME7_2_,
siteimpl5_.PRODUCTION_SANDBOX_ID as PRODUCTION5_7_2_,
siteimpl5_.SITE_IDENTIFIER_TYPE as SITE3_7_2_,
siteimpl5_.SITE_IDENTIFIER_VALUE as SITE4_7_2_,
giftwrapor6_.ORDER_ITEM_ID as ORDER1_58_3_,
giftwrapor6_2_.CATEGORY_ID as CATEGORY10_58_3_,
giftwrapor6_2_.GIFT_WRAP_ITEM_ID as GIFT11_58_3_,
giftwrapor6_2_.ITEM_TAXABLE_FLAG as ITEM2_58_3_,
giftwrapor6_2_.NAME as NAME58_3_,
giftwrapor6_2_.ORDER_ID as ORDER12_58_3_,
giftwrapor6_2_.ORDER_ITEM_TYPE as ORDER4_58_3_,
giftwrapor6_2_.PERSONAL_MESSAGE_ID as PERSONAL13_58_3_,
giftwrapor6_2_.PRICE as PRICE58_3_,
giftwrapor6_2_.QUANTITY as QUANTITY58_3_,
giftwrapor6_2_.RETAIL_PRICE as RETAIL7_58_3_,
giftwrapor6_2_.SALE_PRICE as SALE8_58_3_,
giftwrapor6_2_.SPLIT_PARENT_ITEM_ID as SPLIT9_58_3_,
giftwrapor6_1_.BASE_RETAIL_PRICE as BASE1_60_3_,
giftwrapor6_1_.BASE_SALE_PRICE as BASE2_60_3_,
giftwrapor6_1_.BUNDLE_ORDER_ITEM_ID as BUNDLE4_60_3_,
giftwrapor6_1_.PRODUCT_ID as PRODUCT5_60_3_,
giftwrapor6_1_.SKU_ID as SKU6_60_3_,
giftwrapor6_1_.SKU_BUNDLE_ITEM_ID as SKU7_60_3_,
orderimpl7_.ORDER_ID as ORDER1_72_4_,
orderimpl7_.CREATED_BY as CREATED2_72_4_,
orderimpl7_.DATE_CREATED as DATE3_72_4_,
orderimpl7_.DATE_UPDATED as DATE4_72_4_,
orderimpl7_.UPDATED_BY as UPDATED5_72_4_,
orderimpl7_.CURRENCY_CODE as CURRENCY15_72_4_,
orderimpl7_.CUSTOMER_ID as CUSTOMER16_72_4_,
orderimpl7_.EMAIL_ADDRESS as EMAIL6_72_4_,
orderimpl7_.LOCALE_CODE as LOCALE17_72_4_,
orderimpl7_.NAME as NAME72_4_,
orderimpl7_.ORDER_NUMBER as ORDER8_72_4_,
orderimpl7_.ORDER_STATUS as ORDER9_72_4_,
orderimpl7_.ORDER_SUBTOTAL as ORDER10_72_4_,
orderimpl7_.SUBMIT_DATE as SUBMIT11_72_4_,
orderimpl7_.ORDER_TOTAL as ORDER12_72_4_,
orderimpl7_.TOTAL_SHIPPING as TOTAL13_72_4_,
orderimpl7_.TOTAL_TAX as TOTAL14_72_4_,
orderimpl7_1_.ARCHIVED as ARCHIVED160_4_,
case when orderimpl7_1_.ORDER_ID is not null
then 1 when orderimpl7_.ORDER_ID is not null
then 0 end as clazz_4_,
personalme8_.PERSONAL_MESSAGE_ID as PERSONAL1_78_5_,
personalme8_.MESSAGE as MESSAGE78_5_,
personalme8_.MESSAGE_FROM as MESSAGE3_78_5_,
personalme8_.MESSAGE_TO as MESSAGE4_78_5_,
personalme8_.OCCASION as OCCASION78_5_,
bundleorde9_.ORDER_ITEM_ID as ORDER1_58_6_,
bundleorde9_1_.CATEGORY_ID as CATEGORY10_58_6_,
bundleorde9_1_.GIFT_WRAP_ITEM_ID as GIFT11_58_6_,
bundleorde9_1_.ITEM_TAXABLE_FLAG as ITEM2_58_6_,
bundleorde9_1_.NAME as NAME58_6_,
bundleorde9_1_.ORDER_ID as ORDER12_58_6_,
bundleorde9_1_.ORDER_ITEM_TYPE as ORDER4_58_6_,
bundleorde9_1_.PERSONAL_MESSAGE_ID as PERSONAL13_58_6_,
bundleorde9_1_.PRICE as PRICE58_6_,
bundleorde9_1_.QUANTITY as QUANTITY58_6_,
bundleorde9_1_.RETAIL_PRICE as RETAIL7_58_6_,
bundleorde9_1_.SALE_PRICE as SALE8_58_6_,
bundleorde9_1_.SPLIT_PARENT_ITEM_ID as SPLIT9_58_6_,
bundleorde9_.BASE_RETAIL_PRICE as BASE1_59_6_,
bundleorde9_.BASE_SALE_PRICE as BASE2_59_6_,
bundleorde9_.PRODUCT_BUNDLE_ID as PRODUCT4_59_6_,
bundleorde9_.SKU_ID as SKU5_59_6_,
productimp10_.PRODUCT_ID as PRODUCT1_33_7_,
productimp10_.ARCHIVED as ARCHIVED33_7_,
productimp10_.CAN_SELL_WITHOUT_OPTIONS as CAN3_33_7_,
productimp10_.DEFAULT_CATEGORY_ID as DEFAULT10_33_7_,
productimp10_.DISPLAY_TEMPLATE as DISPLAY4_33_7_,
productimp10_.IS_FEATURED_PRODUCT as IS5_33_7_,
productimp10_.MANUFACTURE as MANUFACT6_33_7_,
productimp10_.MODEL as MODEL33_7_,
productimp10_.URL as URL33_7_,
productimp10_.URL_KEY as URL9_33_7_,
productimp10_1_.AUTO_BUNDLE as AUTO1_34_7_,
productimp10_1_.BUNDLE_PROMOTABLE as BUNDLE2_34_7_,
productimp10_1_.ITEMS_PROMOTABLE as ITEMS3_34_7_,
productimp10_1_.PRICING_MODEL as PRICING4_34_7_,
productimp10_1_.BUNDLE_PRIORITY as BUNDLE5_34_7_,
productimp10_2_.TYPE as TYPE150_7_,
productimp10_3_.EMAIL_DELIVERY as EMAIL1_151_7_,
productimp10_4_.VIRTUAL_PRODUCT_TYPE as VIRTUAL1_152_7_,
productimp10_6_.BUNDLE_TYPE as BUNDLE1_157_7_,
productimp10_6_.IS_BUNDLE as IS2_157_7_,
case when productimp10_6_.PRODUCT_ID is not null
then 6 when productimp10_3_.PRODUCT_ID is not null
then 3 when productimp10_4_.PRODUCT_ID is not null
then 4 when productimp10_5_.PRODUCT_ID is not null
then 5 when productimp10_1_.PRODUCT_ID is not null
then 1 when productimp10_2_.PRODUCT_ID is not null
then 2 when productimp10_.PRODUCT_ID is not null
then 0 end as clazz_7_,
skuimpl11_.SKU_ID as SKU1_39_8_,
skuimpl11_.ACTIVE_END_DATE as ACTIVE2_39_8_,
skuimpl11_.ACTIVE_START_DATE as ACTIVE3_39_8_,
skuimpl11_.AVAILABLE_FLAG as AVAILABLE4_39_8_,
skuimpl11_.DEFAULT_PRODUCT_ID as DEFAULT24_39_8_,
skuimpl11_.DESCRIPTION as DESCRIPT5_39_8_,
skuimpl11_.CONTAINER_SHAPE as CONTAINER6_39_8_,
skuimpl11_.DEPTH as DEPTH39_8_,
skuimpl11_.DIMENSION_UNIT_OF_MEASURE as DIMENSION8_39_8_,
skuimpl11_.GIRTH as GIRTH39_8_,
skuimpl11_.HEIGHT as HEIGHT39_8_,
skuimpl11_.CONTAINER_SIZE as CONTAINER11_39_8_,
skuimpl11_.WIDTH as WIDTH39_8_,
skuimpl11_.DISCOUNTABLE_FLAG as DISCOUN13_39_8_,
skuimpl11_.FULFILLMENT_TYPE as FULFILL14_39_8_,
skuimpl11_.INVENTORY_TYPE as INVENTORY15_39_8_,
skuimpl11_.IS_MACHINE_SORTABLE as IS16_39_8_,
skuimpl11_.LONG_DESCRIPTION as LONG17_39_8_,
skuimpl11_.NAME as NAME39_8_,
skuimpl11_.RETAIL_PRICE as RETAIL19_39_8_,
skuimpl11_.SALE_PRICE as SALE20_39_8_,
skuimpl11_.TAXABLE_FLAG as TAXABLE21_39_8_,
skuimpl11_.WEIGHT as WEIGHT39_8_,
skuimpl11_.WEIGHT_UNIT_OF_MEASURE as WEIGHT23_39_8_,
skuimpl11_2_.PRODUCT_ID as PRODUCT1_40_8_,
skuimpl11_1_.BUSINESS_RETAIL_PRICE as BUSINESS1_149_8_,
skuimpl11_1_.BUSINESS_SALE_PRICE as BUSINESS2_149_8_,
case when skuimpl11_1_.SKU_ID is not null
then 1 when skuimpl11_.SKU_ID is not null
then 0 end as clazz_8_, skubundlei12_.SKU_BUNDLE_ITEM_ID as SKU1_37_9_,
skubundlei12_.PRODUCT_BUNDLE_ID as PRODUCT4_37_9_,
skubundlei12_.ITEM_SALE_PRICE as ITEM2_37_9_,
skubundlei12_.QUANTITY as QUANTITY37_9_,
skubundlei12_.SKU_ID as SKU5_37_9_,
order1_.ORDER_ID as ORDER1_72_10_,
order1_.CREATED_BY as CREATED2_72_10_,
order1_.DATE_CREATED as DATE3_72_10_,
order1_.DATE_UPDATED as DATE4_72_10_,
order1_.UPDATED_BY as UPDATED5_72_10_,
order1_.CURRENCY_CODE as CURRENCY15_72_10_,
order1_.CUSTOMER_ID as CUSTOMER16_72_10_,
order1_.EMAIL_ADDRESS as EMAIL6_72_10_,
order1_.LOCALE_CODE as LOCALE17_72_10_,
order1_.NAME as NAME72_10_,
order1_.ORDER_NUMBER as ORDER8_72_10_,
order1_.ORDER_STATUS as ORDER9_72_10_,
order1_.ORDER_SUBTOTAL as ORDER10_72_10_,
order1_.SUBMIT_DATE as SUBMIT11_72_10_,
order1_.ORDER_TOTAL as ORDER12_72_10_,
order1_.TOTAL_SHIPPING as TOTAL13_72_10_,
order1_.TOTAL_TAX as TOTAL14_72_10_,
order1_1_.ARCHIVED as ARCHIVED160_10_,
case when order1_1_.ORDER_ID is not null
then 1 when order1_.ORDER_ID is not null
then 0 end as clazz_10_,
broadleafc14_.CURRENCY_CODE as CURRENCY1_0_11_,
broadleafc14_.DEFAULT_FLAG as DEFAULT2_0_11_,
broadleafc14_.FRIENDLY_NAME as FRIENDLY3_0_11_,
customerim15_.CUSTOMER_ID as CUSTOMER1_17_12_,
customerim15_.CREATED_BY as CREATED2_17_12_,
customerim15_.DATE_CREATED as DATE3_17_12_,
customerim15_.DATE_UPDATED as DATE4_17_12_,
customerim15_.UPDATED_BY as UPDATED5_17_12_,
customerim15_.CHALLENGE_ANSWER as CHALLENGE6_17_12_,
customerim15_.CHALLENGE_QUESTION_ID as CHALLENGE16_17_12_,
customerim15_.LOCALE_CODE as LOCALE17_17_12_,
customerim15_.DEACTIVATED as DEACTIVA7_17_12_,
customerim15_.EMAIL_ADDRESS as EMAIL8_17_12_,
customerim15_.FIRST_NAME as FIRST9_17_12_,
customerim15_.LAST_NAME as LAST10_17_12_,
customerim15_.PASSWORD as PASSWORD17_12_,
customerim15_.PASSWORD_CHANGE_REQUIRED as PASSWORD12_17_12_,
customerim15_.RECEIVE_EMAIL as RECEIVE13_17_12_,
customerim15_.IS_REGISTERED as IS14_17_12_,
customerim15_.USER_NAME as USER15_17_12_,
customerim15_1_.DEACTIVATION_DATE as DEACTIVA1_140_12_,
customerim15_1_.END_ACTIVE_DATE as END2_140_12_,
customerim15_1_.POINT_ACCOUNT_ID as POINT6_140_12_,
customerim15_1_.RECEIVE_PARTNER_EMAIL as RECEIVE3_140_12_,
customerim15_1_.TYPE as TYPE140_12_,
customerim15_2_.CONSUMER_GROUP_ID as CONSUMER2_141_12_,
customerim15_3_.COMPANY_ACTIVITY_ID as COMPANY5_142_12_,
customerim15_3_.COMPANY_NAFCODE as COMPANY1_142_12_,
customerim15_3_.COMPANY_NAME as COMPANY2_142_12_,
customerim15_3_.COMPANY_SIRET as COMPANY3_142_12_,
customerim15_3_.BUSINESS_GROUP_ID as BUSINESS6_142_12_,
case when customerim15_2_.CUSTOMER_ID is not null
then 2 when customerim15_3_.CUSTOMER_ID is not null
then 3 when customerim15_1_.CUSTOMER_ID is not null
then 1 when customerim15_.CUSTOMER_ID is not null
then 0 end as clazz_12_,
localeimpl16_.LOCALE_CODE as LOCALE1_4_13_,
localeimpl16_.CURRENCY_CODE as CURRENCY4_4_13_,
localeimpl16_.DEFAULT_FLAG as DEFAULT2_4_13_,
localeimpl16_.FRIENDLY_NAME as FRIENDLY3_4_13_,
productbun17_.PRODUCT_ID as PRODUCT1_33_14_,
productbun17_1_.ARCHIVED as ARCHIVED33_14_,
productbun17_1_.CAN_SELL_WITHOUT_OPTIONS as CAN3_33_14_,
productbun17_1_.DEFAULT_CATEGORY_ID as DEFAULT10_33_14_,
productbun17_1_.DISPLAY_TEMPLATE as DISPLAY4_33_14_,
productbun17_1_.IS_FEATURED_PRODUCT as IS5_33_14_,
productbun17_1_.MANUFACTURE as MANUFACT6_33_14_,
productbun17_1_.MODEL as MODEL33_14_,
productbun17_1_.URL as URL33_14_,
productbun17_1_.URL_KEY as URL9_33_14_,
productbun17_.AUTO_BUNDLE as AUTO1_34_14_,
productbun17_.BUNDLE_PROMOTABLE as BUNDLE2_34_14_,
productbun17_.ITEMS_PROMOTABLE as ITEMS3_34_14_,
productbun17_.PRICING_MODEL as PRICING4_34_14_,
productbun17_.BUNDLE_PRIORITY as BUNDLE5_34_14_,
productbun17_2_.BUNDLE_TYPE as BUNDLE1_157_14_,
productbun17_2_.IS_BUNDLE as IS2_157_14_,
case when productbun17_2_.PRODUCT_ID is not null
then 6 when productbun17_.PRODUCT_ID is not null
then 1 end as clazz_14_,
categoryim18_.CATEGORY_ID as CATEGORY1_25_15_,
categoryim18_.ACTIVE_END_DATE as ACTIVE2_25_15_,
categoryim18_.ACTIVE_START_DATE as ACTIVE3_25_15_,
categoryim18_.ARCHIVED as ARCHIVED25_15_,
categoryim18_.DEFAULT_PARENT_CATEGORY_ID as DEFAULT13_25_15_,
categoryim18_.DESCRIPTION as DESCRIPT5_25_15_,
categoryim18_.DISPLAY_TEMPLATE as DISPLAY6_25_15_,
categoryim18_.FULFILLMENT_TYPE as FULFILLM7_25_15_,
categoryim18_.INVENTORY_TYPE as INVENTORY8_25_15_,
categoryim18_.LONG_DESCRIPTION as LONG9_25_15_,
categoryim18_.NAME as NAME25_15_,
categoryim18_.URL as URL25_15_,
categoryim18_.URL_KEY as URL12_25_15_,
categoryim18_1_.SITE_ID as SITE2_148_15_,
case when categoryim18_1_.CATEGORY_ID is not null
then 1 when categoryim18_.CATEGORY_ID is not null
then 0 end as clazz_15_, skuimpl19_.SKU_ID as SKU1_39_16_,
skuimpl19_.ACTIVE_END_DATE as ACTIVE2_39_16_,
skuimpl19_.ACTIVE_START_DATE as ACTIVE3_39_16_,
skuimpl19_.AVAILABLE_FLAG as AVAILABLE4_39_16_,
skuimpl19_.DEFAULT_PRODUCT_ID as DEFAULT24_39_16_,
skuimpl19_.DESCRIPTION as DESCRIPT5_39_16_,
skuimpl19_.CONTAINER_SHAPE as CONTAINER6_39_16_,
skuimpl19_.DEPTH as DEPTH39_16_,
skuimpl19_.DIMENSION_UNIT_OF_MEASURE as DIMENSION8_39_16_,
skuimpl19_.GIRTH as GIRTH39_16_,
skuimpl19_.HEIGHT as HEIGHT39_16_,
skuimpl19_.CONTAINER_SIZE as CONTAINER11_39_16_,
skuimpl19_.WIDTH as WIDTH39_16_,
skuimpl19_.DISCOUNTABLE_FLAG as DISCOUN13_39_16_,
skuimpl19_.FULFILLMENT_TYPE as FULFILL14_39_16_,
skuimpl19_.INVENTORY_TYPE as INVENTORY15_39_16_,
skuimpl19_.IS_MACHINE_SORTABLE as IS16_39_16_,
skuimpl19_.LONG_DESCRIPTION as LONG17_39_16_,
skuimpl19_.NAME as NAME39_16_,
skuimpl19_.RETAIL_PRICE as RETAIL19_39_16_,
skuimpl19_.SALE_PRICE as SALE20_39_16_,
skuimpl19_.TAXABLE_FLAG as TAXABLE21_39_16_,
skuimpl19_.WEIGHT as WEIGHT39_16_,
skuimpl19_.WEIGHT_UNIT_OF_MEASURE as WEIGHT23_39_16_,
skuimpl19_2_.PRODUCT_ID as PRODUCT1_40_16_,
skuimpl19_1_.BUSINESS_RETAIL_PRICE as BUSINESS1_149_16_,
skuimpl19_1_.BUSINESS_SALE_PRICE as BUSINESS2_149_16_,
case when skuimpl19_1_.SKU_ID is not null
then 1 when skuimpl19_.SKU_ID is not null
then 0 end as clazz_16_,
skuimpl20_.SKU_ID as SKU1_39_17_,
skuimpl20_.ACTIVE_END_DATE as ACTIVE2_39_17_,
skuimpl20_.ACTIVE_START_DATE as ACTIVE3_39_17_
skuimpl20_.DEFAULT_PRODUCT_ID as DEFAULT24_39_17_,
skuimpl20_.DESCRIPTION as DESCRIPT5_39_17_,
skuimpl20_.CONTAINER_SHAPE as CONTAINER6_39_17_,
skuimpl20_.DEPTH as DEPTH39_17_,
skuimpl20_.DIMENSION_UNIT_OF_MEASURE as DIMENSION8_39_17_,
skuimpl20_.GIRTH as GIRTH39_17_,
skuimpl20_.HEIGHT as HEIGHT39_17_,
skuimpl20_.CONTAINER_SIZE as CONTAINER11_39_17_,
skuimpl20_.WIDTH as WIDTH39_17_,
skuimpl20_.DISCOUNTABLE_FLAG as DISCOUN13_39_17_,
skuimpl20_.FULFILLMENT_TYPE as FULFILL14_39_17_,
skuimpl20_.INVENTORY_TYPE as INVENTORY15_39_17_,
skuimpl20_.IS_MACHINE_SORTABLE as IS16_39_17_,
skuimpl20_.LONG_DESCRIPTION as LONG17_39_17_,
skuimpl20_.NAME as NAME39_17_,
skuimpl20_.RETAIL_PRICE as RETAIL19_39_17_,
skuimpl20_.SALE_PRICE as SALE20_39_17_,
skuimpl20_.TAXABLE_FLAG as TAXABLE21_39_17_,
skuimpl20_.WEIGHT as WEIGHT39_17_,
skuimpl20_.WEIGHT_UNIT_OF_MEASURE as WEIGHT23_39_17_,
skuimpl20_2_.PRODUCT_ID as PRODUCT1_40_17_,
skuimpl20_1_.BUSINESS_RETAIL_PRICE as BUSINESS1_149_17_,
skuimpl20_1_.BUSINESS_SALE_PRICE as BUSINESS2_149_17_,
case when skuimpl20_1_.SKU_ID is not null
then 1 when skuimpl20_.SKU_ID is not null
then 0 end as clazz_17_,
productimp21_.PRODUCT_ID as PRODUCT1_33_18_,
productimp21_.ARCHIVED as ARCHIVED33_18_,
productimp21_.CAN_SELL_WITHOUT_OPTIONS as CAN3_33_18_,
productimp21_.DEFAULT_CATEGORY_ID as DEFAULT10_33_18_,
productimp21_.DISPLAY_TEMPLATE as DISPLAY4_33_18_,
productimp21_.IS_FEATURED_PRODUCT as IS5_33_18_,
productimp21_.MANUFACTURE as MANUFACT6_33_18_,
productimp21_.MODEL as MODEL33_18_,
productimp21_.URL as URL33_18_,
productimp21_.URL_KEY as URL9_33_18_,
productimp21_1_.AUTO_BUNDLE as AUTO1_34_18_,
productimp21_1_.BUNDLE_PROMOTABLE as BUNDLE2_34_18_,
productimp21_1_.ITEMS_PROMOTABLE as ITEMS3_34_18_,
productimp21_1_.PRICING_MODEL as PRICING4_34_18_,
productimp21_1_.BUNDLE_PRIORITY as BUNDLE5_34_18_,
productimp21_2_.TYPE as TYPE150_18_,
productimp21_3_.EMAIL_DELIVERY as EMAIL1_151_18_,
productimp21_4_.VIRTUAL_PRODUCT_TYPE as VIRTUAL1_152_18_,
productimp21_6_.BUNDLE_TYPE as BUNDLE1_157_18_,
productimp21_6_.IS_BUNDLE as IS2_157_18_,
case when productimp21_6_.PRODUCT_ID is not null
then 6 when productimp21_3_.PRODUCT_ID is not null
then 3 when productimp21_4_.PRODUCT_ID is not null
then 4 when productimp21_5_.PRODUCT_ID is not
then 1 when productimp21_2_.PRODUCT_ID is not null
then 2 when productimp21_.PRODUCT_ID is not null
then 0 end as clazz_18_,
productimp22_.PRODUCT_ID as PRODUCT1_33_19_,
productimp22_.ARCHIVED as ARCHIVED33_19_,
productimp22_.CAN_SELL_WITHOUT_OPTIONS as CAN3_33_19_,
productimp22_.DEFAULT_CATEGORY_ID as DEFAULT10_33_19_,
productimp22_.DISPLAY_TEMPLATE as DISPLAY4_33_19_,
productimp22_.IS_FEATURED_PRODUCT as IS5_33_19_,
productimp22_.MANUFACTURE as MANUFACT6_33_19_,
productimp22_.MODEL as MODEL33_19_,
productimp22_.URL as URL33_19_,
productimp22_.URL_KEY as URL9_33_19_,
productimp22_1_.AUTO_BUNDLE as AUTO1_34_19_,
productimp22_1_.BUNDLE_PROMOTABLE as BUNDLE2_34_19_,
productimp22_1_.ITEMS_PROMOTABLE as ITEMS3_34_19_,
productimp22_1_.PRICING_MODEL as PRICING4_34_19_,
productimp22_1_.BUNDLE_PRIORITY as BUNDLE5_34_19_,
productimp22_2_.TYPE as TYPE150_19_,
productimp22_3_.EMAIL_DELIVERY as EMAIL1_151_19_,
productimp22_4_.VIRTUAL_PRODUCT_TYPE as VIRTUAL1_152_19_,
productimp22_6_.BUNDLE_TYPE as BUNDLE1_157_19_,
productimp22_6_.IS_BUNDLE as IS2_157_19_,
case when productimp22_6_.PRODUCT_ID is not null
then 6 when productimp22_3_.PRODUCT_ID is not null
then 3 when productimp22_4_.PRODUCT_ID is not null
then 4 when productimp22_5_.PRODUCT_ID is not null
then 5 when productimp22_1_.PRODUCT_ID is not null
then 1 when productimp22_2_.PRODUCT_ID is not null
then 2 when productimp22_.PRODUCT_ID is not null
then 0 end as clazz_19_ from BLC_ORDER_ITEM this_ left outer join
BLC_BUNDLE_ORDER_ITEM this_1_ on this_.ORDER_ITEM_ID=this_1_.ORDER_ITEM_ID left outer join
BLC_DISCRETE_ORDER_ITEM this_2_ on this_.ORDER_ITEM_ID=this_2_.ORDER_ITEM_ID left outer join
BLC_DYN_DISCRETE_ORDER_ITEM this_3_ on this_.ORDER_ITEM_ID=this_3_.ORDER_ITEM_ID left outer join
BLC_GIFTWRAP_ORDER_ITEM this_4_ on this_.ORDER_ITEM_ID=this_4_.ORDER_ITEM_ID left outer join
MY_DISCRETE_ORDER_ITEM this_5_ on this_.ORDER_ITEM_ID=this_5_.ORDER_ITEM_ID left outer join
BLC_CATEGORY categoryim3_ on this_.CATEGORY_ID=categoryim3_.CATEGORY_ID left outer join
MY_CATEGORY categoryim3_1_ on categoryim3_.CATEGORY_ID=categoryim3_1_.CATEGORY_ID left outer join
BLC_CATEGORY categoryim4_ on categoryim3_.DEFAULT_PARENT_CATEGORY_ID=categoryim4_.CATEGORY_ID left outer join
MY_CATEGORY categoryim4_1_ on categoryim4_.CATEGORY_ID=categoryim4_1_.CATEGORY_ID left outer join
BLC_SITE siteimpl5_ on categoryim3_1_.SITE_ID=siteimpl5_.SITE_ID left outer join
BLC_GIFTWRAP_ORDER_ITEM giftwrapor6_ on this_.GIFT_WRAP_ITEM_ID=giftwrapor6_.ORDER_ITEM_ID left outer join
BLC_DISCRETE_ORDER_ITEM giftwrapor6_1_ on giftwrapor6_.ORDER_ITEM_ID=giftwrapor6_1_.ORDER_ITEM_ID left outer join
BLC_ORDER_ITEM giftwrapor6_2_ on giftwrapor6_.ORDER_ITEM_ID=giftwrapor6_2_.ORDER_ITEM_ID left outer join
BLC_ORDER orderimpl7_ on giftwrapor6_2_.ORDER_ID=orderimpl7_.ORDER_ID left outer join
MY_ORDER orderimpl7_1_ on orderimpl7_.ORDER_ID=orderimpl7_1_.ORDER_ID left outer join
BLC_PERSONAL_MESSAGE personalme8_ on giftwrapor6_2_.PERSONAL_MESSAGE_ID=personalme8_.PERSONAL_MESSAGE_ID left outer join
BLC_BUNDLE_ORDER_ITEM bundleorde9_ on giftwrapor6_1_.BUNDLE_ORDER_ITEM_ID=bundleorde9_.ORDER_ITEM_ID left outer join
BLC_ORDER_ITEM bundleorde9_1_ on bundleorde9_.ORDER_ITEM_ID=bundleorde9_1_.ORDER_ITEM_ID left outer join
BLC_PRODUCT productimp10_ on giftwrapor6_1_.PRODUCT_ID=productimp10_.PRODUCT_ID left outer join
BLC_PRODUCT_BUNDLE productimp10_1_ on productimp10_.PRODUCT_ID=productimp10_1_.PRODUCT_ID left outer join
MY_PRODUCT productimp10_2_ on productimp10_.PRODUCT_ID=productimp10_2_.PRODUCT_ID left outer join
MY_PHYSICAL_PRODUCT productimp10_3_ on productimp10_.PRODUCT_ID=productimp10_3_.PRODUCT_ID left outer join
MY_VIRTUAL_PRODUCT productimp10_4_ on productimp10_.PRODUCT_ID=productimp10_4_.PRODUCT_ID left outer join
MY_POINT_PRODUCT productimp10_5_ on productimp10_.PRODUCT_ID=productimp10_5_.PRODUCT_ID left outer join
MY_PRODUCT_BUNDLE productimp10_6_ on productimp10_.PRODUCT_ID=productimp10_6_.PRODUCT_ID left outer join
BLC_SKU skuimpl11_ on giftwrapor6_1_.SKU_ID=skuimpl11_.SKU_ID left outer join
MY_SKU skuimpl11_1_ on skuimpl11_.SKU_ID=skuimpl11_1_.SKU_ID left outer join
BLC_PRODUCT_SKU_XREF skuimpl11_2_ on skuimpl11_.SKU_ID=skuimpl11_2_.SKU_ID left outer join
BLC_SKU_BUNDLE_ITEM skubundlei12_ on giftwrapor6_1_.SKU_BUNDLE_ITEM_ID=skubundlei12_.SKU_BUNDLE_ITEM_ID inner join
BLC_ORDER order1_ on this_.ORDER_ID=order1_.ORDER_ID left outer join
MY_ORDER order1_1_ on order1_.ORDER_ID=order1_1_.ORDER_ID left outer join
BLC_CURRENCY broadleafc14_ on order1_.CURRENCY_CODE=broadleafc14_.CURRENCY_CODE left outer join
BLC_CUSTOMER customerim15_ on order1_.CUSTOMER_ID=customerim15_.CUSTOMER_ID left outer join
MY_CUSTOMER customerim15_1_ on customerim15_.CUSTOMER_ID=customerim15_1_.CUSTOMER_ID left outer join
MY_CONSUMER_CUSTOMER customerim15_2_ on customerim15_.CUSTOMER_ID=customerim15_2_.CUSTOMER_ID left outer join
MY_BUSINESS_CUSTOMER customerim15_3_ on customerim15_.CUSTOMER_ID=customerim15_3_.CUSTOMER_ID left outer join
BLC_LOCALE localeimpl16_ on order1_.LOCALE_CODE=localeimpl16_.LOCALE_CODE left outer join
BLC_PRODUCT_BUNDLE productbun17_ on this_1_.PRODUCT_BUNDLE_ID=productbun17_.PRODUCT_ID left outer join
BLC_PRODUCT productbun17_1_ on productbun17_.PRODUCT_ID=productbun17_1_.PRODUCT_ID left outer join
MY_PRODUCT_BUNDLE productbun17_2_ on productbun17_.PRODUCT_ID=productbun17_2_.PRODUCT_ID left outer join
BLC_CATEGORY categoryim18_ on productbun17_1_.DEFAULT_CATEGORY_ID=categoryim18_.CATEGORY_ID left outer join
MY_CATEGORY categoryim18_1_ on categoryim18_.CATEGORY_ID=categoryim18_1_.CATEGORY_ID left outer join
BLC_SKU skuimpl19_ on productbun17_.PRODUCT_ID=skuimpl19_.DEFAULT_PRODUCT_ID left outer join
MY_SKU skuimpl19_1_ on skuimpl19_.SKU_ID=skuimpl19_1_.SKU_ID left outer join
BLC_PRODUCT_SKU_XREF skuimpl19_2_ on skuimpl19_.SKU_ID=skuimpl19_2_.SKU_ID left outer join
BLC_SKU skuimpl20_ on this_1_.SKU_ID=skuimpl20_.SKU_ID left outer join
MY_SKU skuimpl20_1_ on skuimpl20_.SKU_ID=skuimpl20_1_.SKU_ID left outer join
BLC_PRODUCT_SKU_XREF skuimpl20_2_ on skuimpl20_.SKU_ID=skuimpl20_2_.SKU_ID left outer join
BLC_PRODUCT productimp21_ on skuimpl20_.DEFAULT_PRODUCT_ID=productimp21_.PRODUCT_ID left outer join
BLC_PRODUCT_BUNDLE productimp21_1_ on productimp21_.PRODUCT_ID=productimp21_1_.PRODUCT_ID left outer join
MY_PRODUCT productimp21_2_ on productimp21_.PRODUCT_ID=productimp21_2_.PRODUCT_ID left outer join
MY_PHYSICAL_PRODUCT productimp21_3_ on productimp21_.PRODUCT_ID=productimp21_3_.PRODUCT_ID left outer join
MY_VIRTUAL_PRODUCT productimp21_4_ on productimp21_.PRODUCT_ID=productimp21_4_.PRODUCT_ID left outer join
MY_POINT_PRODUCT productimp21_5_ on productimp21_.PRODUCT_ID=productimp21_5_.PRODUCT_ID left outer join
MY_PRODUCT_BUNDLE productimp21_6_ on productimp21_.PRODUCT_ID=productimp21_6_.PRODUCT_ID left outer join
BLC_PRODUCT productimp22_ on skuimpl20_2_.PRODUCT_ID=productimp22_.PRODUCT_ID left outer join
BLC_PRODUCT_BUNDLE productimp22_1_ on productimp22_.PRODUCT_ID=productimp22_1_.PRODUCT_ID left outer join
MY_PRODUCT productimp22_2_ on productimp22_.PRODUCT_ID=productimp22_2_.PRODUCT_ID left outer join
MY_PHYSICAL_PRODUCT productimp22_3_ on productimp22_.PRODUCT_ID=productimp22_3_.PRODUCT_ID left outer join
MY_VIRTUAL_PRODUCT productimp22_4_ on productimp22_.PRODUCT_ID=productimp22_4_.PRODUCT_ID left outer join
MY_POINT_PRODUCT productimp22_5_ on productimp22_.PRODUCT_ID=productimp22_5_.PRODUCT_ID left outer join
MY_PRODUCT_BUNDLE productimp22_6_ on productimp22_.PRODUCT_ID=productimp22_6_.PRODUCT_ID where order1_.ORDER_ID=? limit ?
Re: Too many tables when fetching order items
Posted: Wed Feb 06, 2013 11:29 am
by phillipuniverse
Discussing internally, we'll get back to you.
Re: Too many tables when fetching order items
Posted: Wed Feb 06, 2013 11:31 am
by denis
Thanks we really appreciate your help
Re: Too many tables when fetching order items
Posted: Mon Feb 11, 2013 12:41 pm
by phillipuniverse
So one possible solution to this scenario would be to use single table inheritance in your product domain (see
http://docs.broadleafcommerce.org/2.2/E ... orial.html near the bottom). The doc is a little out-dated; you should be using Spring instrumentation instead:
http://docs.broadleafcommerce.org/2.2/M ... Setup.html.
This will obviously have a pretty large impact on your database model as now all of the columns will be in a single table with a discriminator column.
Also, could you view the order items on the frontend and see if the query is vastly different than in the admin?
Re: Too many tables when fetching order items
Posted: Mon Feb 11, 2013 2:32 pm
by denis
Ok thanks even if it's a very bad news for us
. Are the broadleaf annotations (@AdminPresentationClass and the others) will continue to work by using single table inheritance and are there any other impacts?
We have already developed many things around products and we have no time to start again from scratch
