Need help - SQL Query for Product Customization on Orders

[Rhapsody]

I am using the 1.5.4.1 BO SQL Manager to generate some custom queries that produce reports of certain products ordered. When I create a SQL query that pulls the data without the product customization, everything is fine, and each product ordered is displayed on a single line.

 

If I have product customizations (e.g. 4 text fields), the display repeats the product on multiple lines with the product customizations displayed one at a time in the designated field columns, in repeated lines. (e.g. customized field 1 on 1st line, customized field 2 on 2nd line, etc.)

 

How do I force the 4 text fields for display customizations on one line with the product ordered?

 

This is the code that is supposed to display the customized text:

IF(LOCATE('Club 420 Skipper', cfl.name)> 0, cd.value , " ") AS "Club 420 Skipper",
IF(LOCATE('Club 420 Crew', cfl.name)> 0, cd.value , " ") AS "Club 420 Crew",
IF(LOCATE('Club or program', cfl.name)> 0, cd.value , " ") AS "Club ",
IF(LOCATE('Sail Number', cfl.name)> 0, cd.value , " ") AS "Sail ",

 

This is the code that joins the tables to pull the data:

LEFT JOIN `ps_customization` cz ON cz.`id_cart` = o.`id_cart`
LEFT JOIN `ps_customization_field` cf ON cf.`id_product` = cz.`id_product`
LEFT JOIN `ps_customized_data` cd ON cd.`id_customization` = cz.`id_customization`
LEFT JOIN `ps_customization_field_lang` cfl ON cfl.`id_customization_field` = cd.`index`

 

Below is the full SQL query

 

SELECT  concat_ws(' ', c.`firstname`, c.`lastname`) "Ordered by",  (c.`email`) "Email" ,  (a.phone_mobile) "Phone", (o.id_order) "Order",  (n.product_reference) "Ref", (o.id_order) "Order", (n.product_quantity)  "Qty",(o.payment)  "Payment", (o.valid)  "Rcvd",
IF(LOCATE('Club 420 Skipper', cfl.name)> 0, cd.value , " ") AS "Club 420 Skipper",
IF(LOCATE('Club 420 Crew', cfl.name)> 0, cd.value , " ") AS "Club 420 Crew",
IF(LOCATE('Club or program', cfl.name)> 0, cd.value , " ") AS "Club ",
IF(LOCATE('Sail Number', cfl.name)> 0, cd.value , " ") AS "Sail ",
IF(LOCATE('NBYA Member : Yes', n.product_name)> 0, "Member" , "No") AS "NBYA",
IF(LOCATE(' Liability Waiver Accepted : Yes', n.product_name)> 0, "Ok" , "Needed") AS "Waiver",
IF(LOCATE('Class Entered : C420 Div I', n.product_name)> 0, 1 , "") AS "420 Div I",
IF(LOCATE('Class Entered : C420 DII', n.product_name)> 0, 1, "") AS "420 Div II",
IF(LOCATE('Class Entered : Opti Red Girl', n.product_name)> 0, 1, "") AS "Opti Red Girl",
IF(LOCATE('Class Entered : Opti Blue Girl', n.product_name)> 0, 1, "") AS "Opti Blue Girl",
IF(LOCATE('Class Entered : Opti White Girl', n.product_name)> 0, 1, "") AS "Opti White Girl",
IF(LOCATE('Class Entered : Opti Green Fleet', n.product_name)> 0, 1, "") AS "Opti Green",
IF(LOCATE('Class Entered : Laser Radial', n.product_name)>  0, 1, "") AS "Laser Radial"

FROM `ps_orders` o
LEFT JOIN `ps_customer` c ON c.`id_customer` = o.`id_customer`
LEFT JOIN `ps_address` a ON a.`id_customer` = c.`id_customer`
LEFT JOIN `ps_order_detail` n ON n.`id_order` = o.`id_order`
LEFT JOIN `ps_customization` cz ON cz.`id_cart` = o.`id_cart`
LEFT JOIN `ps_customization_field` cf ON cf.`id_product` = cz.`id_product`
LEFT JOIN `ps_customized_data` cd ON cd.`id_customization` = cz.`id_customization`
LEFT JOIN `ps_customization_field_lang` cfl ON cfl.`id_customization_field` = cd.`index`
WHERE (o.current_state < 5)
AND  (n.`product_reference` LIKE "MRM13")
AND o.`id_address_delivery` = a.`id_address`
AND YEAR (o.`date_add`)=YEAR(NOW())

ORDER BY  c.lastname, o.id_order,  n.product_reference

[xfearx]

get it solve?

 

 

ty.

[Rhapsody]

No - The following tip was provided but does not meet my needs:

 

group_concat(cd.value separator ', ')

[PascalVG]

Maybe try to RTRIM the value?

 

IF(LOCATE('Club 420 Skipper', cfl.name)> 0, RTRIM(cd.value) , " ") AS "Club 420 Skipper",

 

 

(Sorry, Have no database contents available to try...)

pascal

[xfearx]

Edited October 1, 2013 by xfearx (see edit history)

[Rhapsody]

 

Maybe try to RTRIM the value?

 

IF(LOCATE('Club 420 Skipper', cfl.name)> 0, RTRIM(cd.value) , " ") AS "Club 420 Skipper",

 

 

(Sorry, Have no database contents available to try...)

pascal

 

Pascal - the problem is the various values that may have been entered for a single product are not displayed, only a single customized text field that displays a single value then moves on to the next record.  I did try your rtrim suggestion but it didn't work.  I can give you BO office if you would like to try the query on actual data.  Send me a pm and I will setup a login for you.

Edited September 30, 2013 by Rhapsody (see edit history)

[tuk66]

Try GROUP_CONCAT function in the SELECT statement.

[PascalVG]

hi rhapsody,

did tuk66's suggestion work?

 

if still some problems: IP: 171.98.5.204

pascal

[Rhapsody]

Try GROUP_CONCAT function in the SELECT statement.

This is close, but not quite there.  Here is a sample of the output.  The data in the fields seem to be repeated.

Benjamin Collins, Benjamin Collins, Benjamin Collins, Myles Carey, Myles Carey, Myles Carey, 6669, 6669, 6669

[PascalVG]

What did your select statement look like (with the group_concat)?

[PascalVG]

xfearx,

Is your problem solved, that you took away your original question?

[Rhapsody]

What did your select statement look like (with the group_concat)?

This is the group concat used:

group_concat(cd.value separator ', ') AS "Text Fields",

I also sent you a pm with BO login information so you can see the query with the actual data.

[PascalVG]

I'll have a look. Which one is your full query? C420 #2? 

[PascalVG]

Hi Rhapsody,

A tough one....

 

As we have more customization fields (i.e. they are in separate rows in ps_custmization_data) you want to add on a single line, it seems to me you could, instead of a locate(...), a sub-SELECT or so to get the correct line out of 4 (i.e. the four that belong to the data on the rest of the line) and then show this one.

 

Not sure how to interconnect them all though... :-(

 

pascal

[tuk66]

Publish your SQL query and we can do something with it.

[Rhapsody]

Publish your SQL query and we can do something with it.

Below is the SQL query.  I'll be offline for the next week at sea and will check in when I return.  Even if the product customization results are grouped on multiple lines but the customizations are not repeated, that is better than nothing. Thanks for the help.

SELECT  concat_ws(' ', c.`firstname`, c.`lastname`) "Ordered by",  (c.`email`) "Email" ,  IF((a.phone_mobile)!="", a.phone_mobile, a.phone) "Phone", (o.id_order) "Order",  (n.product_reference) "Ref", (o.id_order) "Order", (n.product_quantity)  "Qty",(o.payment)  "Payment", (o.valid)  "Rcvd",
IF(LOCATE('Club 420 Skipper', cfl.name)> 0, RTRIM(cd.value) , " ") AS "Club 420 Skipper",
IF(LOCATE('CREW', cfl.name)> 0, cd.value , " ") AS "Club 420 Crew",
IF(LOCATE('Club or program', cfl.name)> 0, cd.value , " ") AS "Club ",
IF(LOCATE('Sail Number', cfl.name)> 0, cd.value , " ") AS "Sail ",
group_concat(cd.value separator ', ') AS "Text Fields",

IF(LOCATE('NBYA Member : Yes', n.product_name)> 0, "Member" , "No") AS "NBYA",
IF(LOCATE(' Liability Waiver Accepted : Yes', n.product_name)> 0, "Ok" , "Needed") AS "Waiver",
IF(LOCATE('Div I ', n.product_name)> 0, 1 , "") AS "420 Div I",
IF(LOCATE('Div II ', n.product_name)> 0, 1, "") AS "420 Div II"

FROM `ps_orders` o
LEFT JOIN `ps_customer` c ON c.`id_customer` = o.`id_customer`
LEFT JOIN `ps_address` a ON a.`id_customer` = c.`id_customer`
LEFT JOIN `ps_order_detail` n ON n.`id_order` = o.`id_order`
LEFT JOIN `ps_customization` cz ON cz.`id_cart` = o.`id_cart`
LEFT JOIN `ps_customization_field` cf ON cf.`id_product` = cz.`id_product`
LEFT JOIN `ps_customized_data` cd ON cd.`id_customization` = cz.`id_customization`
LEFT JOIN `ps_customization_field_lang` cfl ON cfl.`id_customization_field` = cd.`index`

WHERE (o.current_state < 5)
AND  (n.`product_reference` LIKE "C4202")
AND o.`id_address_delivery` = a.`id_address`
AND YEAR (o.`date_add`)=YEAR(NOW()) 

GROUP BY o.id_order
ORDER BY  c.lastname, o.id_order,  n.product_reference

Edited October 2, 2013 by Rhapsody (see edit history)

[xfearx]

xfearx,

Is your problem solved, that you took away your original question?

not solved, but I will not distort the post, ty

[elbassir]

To make it work use max(if(cfl.name='CREW', cd.value, null )) AS 'Club 420 Crew',

instead of this: 

IF(LOCATE('CREW', cfl.name)> 0, cd.value , " ") AS "Club 420 Crew",

 

(do the same thing for the other fields in the customization table)

Edited September 9, 2015 by elbassir (see edit history)