Helping Others: Using SQL Manager in PS 1.5

[Rhapsody]

PS version 1.5 introduced a SQL manager in the BO menu that looks very useful to export data to a screen view or to Excel (csv format). I am only marginally familiar with SQL but can see the power of this. Here are two SQL queries I have stored:

 

Export Names & Email Addresses

 

SELECT  `firstname`,  `lastname`,  `email`  FROM `ps_customer` order by  `lastname`

 

Export All Customer Table Info (Including custom fields I inserted)

 

SELECT  `firstname`,  `lastname`,  `email`,  `spouse`, `yachtclub`, `boat`, `model`, `sailnum`, `phrfspin`, `phrfnonspin` from `ps_customer` order by  `lastname`

 

I started to setup a query to export postal addresses and have the basics, but want to have the ps_state:iso_code displayed instead of the id_state value included in the ps_address table. I also want to limit this to active addresses (e.g. active=1 in the ps_address table). The sql manager does not like the field names address1 and address2 that is the default name in the ps_address table. How would I modify the following query to do that?

Export Postal Mailing List

 

SELECT  firstname, lastname,	city, id_state, postcode, phone, phone_mobile FROM ps_address ORDER BY lastname

 

I'm thinking of ideas on how I could export orders with the attributes and combinations a customer selected on one query. On another query it would do that, plus include selected fields about the customer from the ps_customer table.

 

Can someone proficient in SQL post some samples here I can play with?

 

If you have ideas for useful SQL Queries that others may find useful, please post them here.

Edited February 7, 2013 by Rhapsody (see edit history)

[bellini13]

hey there

 

you can use * to select everything from the table, instead of listing each column individually.

select * FROM `ps_customer` order by  `lastname`

 

add a where clause to filter in/out resuts

select * FROM `ps_customer` where `active`=1 order by  `lastname`

 

to include results from other tables you need to join (aka link) the tables together. you want information from customer, address and state tables, so you would those 3 tables like this

SELECT c.*, a.address1, a.address2, s.iso_code
FROM `ps_customer` c
LEFT JOIN `ps_address` a ON a.`id_customer` = c.`id_customer`
LEFT JOIN `ps_state` s ON s.`id_state` = a.`id_state`
WHERE a.`active`=1
order by c.`lastname`

[Rhapsody]

Bellini,

 

Thanks again, as always for being so helpful. The LEFT JOIN and filter in/out works great. I am also able to get the entire table, but not using the a.address1 in the statement.

 

Further debug shows that this works in phpmysql, but PS 1.5.3.1 does not like that in the SQL manager.

This is the query I wanted that works in phpmysql but not on the PS SQL manager:

SELECT   a.firstname, a.lastname, a.city, a.address1, a.address2, s.iso_code, a.postcode, a.phone, a.phone_mobile, a.other 
FROM `ps_customer` c
LEFT JOIN `ps_address` a ON a.`id_customer` = c.`id_customer`
LEFT JOIN `ps_state` s ON s.`id_state` = a.`id_state`
WHERE a.`active`=1
ORDER BY c.`lastname`

 

I will post on the bug tracker about this being a problem.

 

Again - thanks for the help!

[Rhapsody]

I just searched the bug tracker and found the problem with fields having numbers in the name has already been reported and fixed - #PSCFV-6383

 

I guess we'll see this in the next release.

[bellini13]

hm, thats a strange issue, but glad they have it fixed already.

 

There is a file "classes/RequestSql.php" that was changed to address the issue. If you don't want to wait for the upgrade, you can apply the 2 line code changes documented here.

https://github.com/PrestaShop/PrestaShop/commit/935b5b5d0228c48048d434849562b0774ac3d9f0

 

The red line is replaced with the green line

[Rhapsody]

Yes - I saw that and created an override that works in the attached file. Stay warm for the blizzard that is starting this morning. We could get 2 feet of snow and wind gust of 60 kts!

RequestSql.php

[Rhapsody]

Based on the help provided by Bellini in the post above, I have created a query that might be helpful to others. I have a couple shops that are used for membership registration where dues are collected for a sailing organization. It is also used to enter sailing regattas. There are some custom fields in the ps_customer table that I've added.

 

The query below provides an alphabetized list by last name, for all people who purchased membership / paid dues. There are 3 possible products a person can purchase to pay dues, so the query checks for that.

 

This query might be modified for someone who wants a list of everyone who purchased selected products. I use the product_reference to filter which products are selected.

 

This query

SELECT  c.`firstname`,  c.`lastname`,  c.`email`,  c.`spouse`, c.`yachtclub`, c.`boat`, c.`model`, c.`sailnum`, c.`phrfspin`, c.`phrfnonspin`
FROM `ps_orders` o
LEFT JOIN `ps_customer` c ON c.`id_customer` = o.`id_customer`
LEFT JOIN `ps_order_detail` n ON n.`id_order` = o.`id_order`
WHERE n.`product_reference`="2013Package"
OR n.`product_reference`="2013Member+Race"
OR n.`product_reference`="2013Member"
ORDER BY c.`lastname`

[Rhapsody]

A question for SQL gurus out there. The product_name field in the ps_product_details table is a concatenated string for any product combinations. I created a SQL query that includes the product_name field in the output. Is there a way that I can do string substitution on the fly to populate the field data output to the display?

 

Example: There are three types of membership provided for the customer to select in the FO with a combinations dropdown for Membership that include these options: Individual, Family, Associate. The product_detail field contains the string Membership : Individual, Membership : Family or Membership : Associate - depending on what the customer selected when the product was purchased.

 

Can the SQL Query output be structured so the data for the product_name field (or a temp field created on the fly for this purpose) has a string function to display only Individual, Family or Associate?

 

Another Example:

Ideally, this logic could be applied to fields that have numeric or string codes that would have another string substituted for output display. Say a field named id_code stored the number 1, 2, or 3. The desired display in the output would be Dog, Cat, or Mouse corresponding to the numbers.

[ilisiaraul]

I need this FROM ps_product p

INNER JOIN ps_product_lang pl ON (p.id_product = pl.id_product) 

LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)

LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)

LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category)

LEFT JOIN ps_category c ON (cp.id_category = c.id_category)

LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)

WHERE pl.id_lang = 1

AND cl.id_lang = 1

AND p.id_shop_default = 1

AND c.id_shop_default = 1

GROUP BY p.id_product  

 

to work with this: 

 

FROM ps_configuration conf, ps_product p 

INNER JOIN ps_product_lang pl ON p.id_product = pl.id_product 

LEFT JOIN ps_supplier_lang sl ON p.id_supplier = sl.id_supplier 

LEFT JOIN ps_manufacturer_lang ml ON p.id_manufacturer = ml.id_manufacturer

WHERE conf.name = 'PS_SHOP_DOMAIN'

 

 

 

how do i do it?