[MySQL Table list]

Thank you so much. This helps massively!

[Exporting all details from all orders using SQL]

Unfortunately I couldn't find it in the datakick module it was in the SQL manager within the thirty bees back office. 

[Exporting all details from all orders using SQL]

On 12/22/2021 at 3:45 PM, Mark said:

The customer data isn't in any datasets, I too would love to see this, particularly notes also, in a dataset.

Hey Mark!

I don't know if this helps you at all but i managed to pull the note for customer data with an SQL query. It's in the table 'customer' and the attribute is 'note'.

Hope this helps!

[MySQL Table list]

Apologies guys and gals. I think I've figured it out.

 

Here's a part of a query I've found by a helpful forum member:

 

 

 

SELECT d.id_order, o.payment

 

    FROM order_detail d

 

    LEFT JOIN orders o ON (d.id_order = o.id_order)

 

 

 

My question was referring to the d and the o before the id_order and payment respectively from SELECT. After some fiddling about I've discovered these are specified after the tables being referenced in the FROM and LEFT JOIN parts. I.e. you change the 'd' after 'order_detail' to another letter, and then the 'd' before 'id_order' to match and it works.

 

In short there is no 'prefix' that exists outside of this specific query.

Is this correct?

Sorry for trying to run before i can walk and bothering everyone. I should learn the language.

[MySQL Table list]

Hey all,

I'm beginning to build my own SQL Queries to get information from orders etc.
Having a lot of fun, but struggling with the prefix's used. For example I've worked out that the g. prefix is connected to customer information table, but how can I find out the prefix's used for all the other tables?

I've searched for ages and I can't find anything.

Thanks,
Sam

[Exporting all details from all orders using SQL]

Hey Petr,

Thanks for the info!
So am I right in thinking there's no way for me to pull the notes I add to people's orders into a spreadsheet?

Also I have a custom field on the product that the customer fills in, is there a way of bringing that in?

Thank you for your help!

[Exporting all details from all orders using SQL]

19 minutes ago, datakick said:

    SELECT d.id_order, os.name AS payment, d.product_name, d.product_reference, d.product_price, d.product_quantity, o.payment, o.date_upd, CONCAT_WS(' ', g.firstname, g.lastname) AS Customer_name, g.id_customer AS CustomerID, CONCAT_WS(' ', ad.address1, ad.address2, ad.city, ad.postcode, ad.other, 'Mobile: ', ad.phone_mobile) AS Delivery_Address, CONCAT_WS(' ', ai.address1, ai.address2, ai.city, ai.postcode, ai.other, 'Mobile: ', ai.phone_mobile) AS Invoice_Address, gl.name AS group_name, s.quantity AS quantity_in_stock, g.email
    FROM order_detail d
    LEFT JOIN orders o ON (d.id_order = o.id_order)
    LEFT JOIN address ad ON (o.id_address_delivery = ad.id_address)
    LEFT JOIN address ai ON (o.id_address_invoice = ai.id_address)
    LEFT JOIN stock_available s ON (d.product_id = s.id_product)
    LEFT JOIN customer g ON (o.id_customer = g.id_customer)
    LEFT JOIN group_lang gl ON (g.id_default_group = gl.id_group) AND gl.name LIKE 'piiri%'
    LEFT JOIN order_state_lang os ON (o.current_state = os.id_order_state)
    WHERE os.id_lang = 1

The tables that are causing you issues are group_lang and stock_available.

Table group_lang has compound primary key  (`id_group`,`id_lang`), but  you join in only using gl.id_group. You need to include language condition as well:

LEFT JOIN group_lang gl ON (g.id_default_group = gl.id_group and gl.id_lang = 1)

Stock available is more complicated. Depending on you multistore setup, there can be a lot of entries for each product. First of all, for every combination there is a separate entry, and there is also an entry for combination with id = 0. If you use multistore, and each store has it's own quantity, then there are other records identified by id_shop or id_shop_group as well.

If you don't use multistore, then you should just add id_product_attribute into the join clause:

LEFT JOIN stock_available s ON (d.product_id = s.id_product AND s.id_product_attribute = d.product_attribute_id)

Alternatively, you can get rid of this table from the 'FROM', and use subquery. Instead of s.quantity AS quantity_in_stock, you would put this expression instead (and remove LEFT JOIN stock_available s ON from FROM clause):

// this to calculate quantity for product

(SELECT sum(s.quantity) FROM stock_available s WHERE d.product_id = s.id_product) as quantity_in_stock

// or this to calculate quantity available per combination

(SELECT sum(s.quantity) FROM stock_available s WHERE d.product_id = s.id_product AND s.id_product_attribute = d.product_attribute_id) as quantity_in_stock

Thanks!
The stock available change helped with the 9 lines per order problem. I don't understand what the language condition would fix? It seems to generate ok without changing that line, and when I change it it returns the error:

1. When multiple tables are used, each attribute must refer back to a table.
2. Undefined "checkedFrom" error

My next issue is getting the private note I've added to some orders..

I'm also building a list in datakick as this seems like a very useful module, but again I can't find the private note option in any of the data sets?

[Exporting all details from all orders using SQL]

These are all fantastic suggestions thank you.
I'm having a look at datakick now.

 

7 hours ago, Mediacom87 said:

Hi,

i made an article about this last year : https://www.mediacom87.fr/en/tip-csv-export-of-order-details-on-prestashop-and-thirtybees/

I've just tried your code Mediacom87 and it returns 'FROM does not exist'
I've renamed it order_detail instead of the prefix ps_ as I'm running thirtybees instead of prestashop. I've also changed the other table names. But still no luck, any suggestions?

 

Thanks all!

[Exporting all details from all orders using SQL]

Hey,

I'm on a steep learning curve with Thirty Bees, it's been great so far but I've hit a snag!

I want to export all my orders into one Spreadsheet where I can see all the different details, including private notes, delivery messages and personalised text.
Along with all the normal bits and pieces like order number and name and address etc.

I've found this from another post and I've altered it a little, but can't work out how to add private notes, delivery messages and personalised text...

The other issue is it's currently giving me 9 lines per individual product order. Is there a way to stop that happening without losing any info? For example I would like to see 3 lines if someone ordered 3 of exactly the same product. currently I would essentially see the same line 27 times. (I only say this because I could easily delete duplicate lines in my spreadsheet, but I don't want to accidentally delete orders if they do exist but appear the same on the spreadsheet)

Quote

    SELECT d.id_order, os.name AS payment, d.product_name, d.product_reference, d.product_price, d.product_quantity, o.payment, o.date_upd, CONCAT_WS(' ', g.firstname, g.lastname) AS Customer_name, g.id_customer AS CustomerID, CONCAT_WS(' ', ad.address1, ad.address2, ad.city, ad.postcode, ad.other, 'Mobile: ', ad.phone_mobile) AS Delivery_Address, CONCAT_WS(' ', ai.address1, ai.address2, ai.city, ai.postcode, ai.other, 'Mobile: ', ai.phone_mobile) AS Invoice_Address, gl.name AS group_name, s.quantity AS quantity_in_stock, g.email
    FROM order_detail d
    LEFT JOIN orders o ON (d.id_order = o.id_order)
    LEFT JOIN address ad ON (o.id_address_delivery = ad.id_address)
    LEFT JOIN address ai ON (o.id_address_invoice = ai.id_address)
    LEFT JOIN stock_available s ON (d.product_id = s.id_product)
    LEFT JOIN customer g ON (o.id_customer = g.id_customer)
    LEFT JOIN group_lang gl ON (g.id_default_group = gl.id_group) AND gl.name LIKE 'piiri%'
    LEFT JOIN order_state_lang os ON (o.current_state = os.id_order_state)
    WHERE os.id_lang = 1

I've searched a lot about SQL and what it means but I think I'd learn quicker if someone was able to help me build my query in relation to Thirtybees.

The forum has been a great source of info fo me so far! It's a great piece of software!