Is there a Library of Prestashop SQL Queries?

[Glenstorm]

Is there a place to find or share SQL Queries?

Specifically, I am trying to export my orders and include the customizations from those orders.  
SQL is not my primary strength and it would be great to find some source of frequently used queries.
Hey, that could be a variation on FAQ . . .  Frequently Used Queries. . . Oh. . .  nevermind. . .

 

Thanks!

Edited January 15, 2019 by Glenstorm (see edit history)

[tdsoft]

What is your question for SQL?

[Glenstorm]

The standard orders query looks like this:

SELECT SQL_CALC_FOUND_ROWS a.`id_order`, `reference`, `total_paid_tax_incl`, `payment`, a.`date_add` 
    AS `date_add` , a.id_currency, a.id_order 
    AS id_pdf, CONCAT(LEFT(c.`firstname`, 1), '. ', c.`lastname`) 
    AS `customer`, osl.`name` 
    AS `osname`, os.`color`, 
    IF((SELECT so.id_order FROM `ps_orders` so WHERE so.id_customer = a.id_customer AND so.id_order < a.id_order LIMIT 1) > 0, 0, 1) as new, country_lang.name as cname, 
    IF(a.valid, 1, 0) badge_success, shop.name as shop_name FROM `ps_orders` a LEFT JOIN `ps_customer` c ON (c.`id_customer` = a.`id_customer`) 
    INNER JOIN `ps_address` address ON address.id_address = a.id_address_delivery 
    INNER JOIN `ps_country` country ON address.id_country = country.id_country 
    INNER JOIN `ps_country_lang` country_lang ON (country.`id_country` = country_lang.`id_country` AND country_lang.`id_lang` = 1) 
    LEFT JOIN `ps_order_state` os ON (os.`id_order_state` = a.`current_state`) 
    LEFT JOIN `ps_order_state_lang` osl ON (os.`id_order_state` = osl.`id_order_state` AND osl.`id_lang` = 1) 
    LEFT JOIN `ps_shop` shop ON a.`id_shop` = shop.`id_shop` 
    WHERE 1 AND a.id_shop IN (1) 
    ORDER BY a.id_order 
    DESC LIMIT 0, 50

My grasp of SQL is very basic.  I understand SELECT and JOINS but the IF and AS are tripping me up.
I would like to retrieve the customization data for some of my items but I can't grasp how that data is stored in the database.  It seems to spread across three or four tables:
    customization, customization_field, customized_data, and customization_lang.

My question is, how do I add the customization data to the existing query.

 

[musicmaster]

With Prestools you can switch "verbose" on. Then you can see all queries that are executed.

[El Patron]

have you tried using (1.7) advanced-->database->sql manager?  it might prove useful....

but ps uses db classes so a module for example does not execute a pure mysql command but passes command type and variable to db class.

this makes it bit more difficult as one cannot use normal db tools to build/analyze appl sql.  bit barbaric 

[dloghis]

I have also the standard query for the products but i want a query that exports all the columns from the products and not 10-15 that the default query did .

I want to export all in csv and be able to import them updated

Do you have any query that does that?

Edited February 26, 2020 by dloghis (see edit history)