Prestashop: export products without ANY module

If you were used to needing a Prestashop export products module to export products from your shop, you can finally drop it now. Prestashop 1.5 allows us to export products, and all sort of data, in handy CSV files without any third party module. How? Read on.

Prestashop version: 1.5.1

SQL Manager – What is it?

Prestashop export products with SQL Manager
Those of you who upgraded from 1.4 and spent some time in Prestashop 1.5’s back office, will have certainly noticed this new feature called SQL Manager. If you didn’t, it can be found under the Advanced Parameters tab.

Even though masked under a complex name this is an extremely usefult tool that finally lets Prestashop export products and all sort of data you can imagine. Yes, it’s a bit complicated, but we can finally export all kind of data we need and send it to, for example, price comparison sites or anything else. The cool thing is, we can export data in the exact way we want, choosing the exact stuff we need.

Yes, but how to? Well, to be honest, there is a reason why this Prestashop export tool has been called SQL Manager. In fact, you have to know a bit of SQL to retrieve the data you want, and this is an enormous obstacle to who doesn’t even understand a single line of html. No fear though! If you only want to export products, I am to provide a simple enough explanation on which query should be used to retrieve values, and which tables are usually storing valuable product data.

The downside – We can only export CSV

Yep, no xml export for retrieved data. But, I guess, since Prestashop only natively supports CSV Import, this has been done subsequently. Of course, this does not mean the system can’t be edited to allow xml export, too!
Furthermore, at this stage we can’t export images, and the sql query is limited to 1000 characters, which means something will need to be left off in most cases.

Export products: Prestashop SQL Manager example

In this example, I’ll be assuming we are exporting data for another Prestashop site, or as a backup for the same store. Therefore I’ll add most of the information needed by the Prestashop CSV Import tool in my csv. Let’s see which data is needed by going to Advanced Parameters -> CSV Import.

Prestashop Export Products - Available fields
A whole bunch of data we need, isn’t it? So, let’s start building our query keeping this pag as a reference. Back to the SQL manager, click Add new.

The request can be given a name, so that it can be stored and used again in the future. I’ll call it “Product Query”.

Step 1 – Product Table

Now, let’s start by grabbing some data from the ps_product table. Please note that “ps_” is the default prefix, and yours might be different. if this is your case, replace “ps_” with your prefix on every occurrence of a table.

	"SELECT p.id_product, p.active, p.price, p.id_tax_rules_group, p.wholesale_price, p.reference, p.supplier_reference, p.id_supplier, p.id_manufacturer, p.upc, p.ecotax, p.weight, p.quantity, p.available_for_order, p.date_add, p.show_price, p.online_only, p.condition, p.id_shop_default
	FROM ps_product p
	GROUP BY p.id_product"

I’m using a group by statement to be sure there a re no duplicate products in the final result.

Finally, you’ll note I had to leave off the field EAN13, since, for some reason, the SQL manager is not accepting numbers in column names

This is all for the ps_product table. Of course, many things are still missing. Let’s deal with translatable fields. As you may know, Prestashop only allows us to import one language at time, so we will need to add a condition that tells the query to only look for a specific language. In this case, English has ID #1.

Step 2 – Let’s grab Translatable Fields

	"SELECT p.id_product, p.active, <strong>pl.name,</strong> p.price, p.id_tax_rules_group, p.wholesale_price, p.reference, p.supplier_reference, p.id_supplier, p.id_manufacturer, p.upc, p.ecotax, p.weight, p.quantity, <strong>pl.description_short,</strong> <strong>pl.description,</strong> <strong>pl.meta_title,</strong> <strong>pl.meta_keywords,</strong> <strong>pl.meta_description,</strong> <strong>pl.link_rewrite,</strong> <strong>pl.available_now,</strong> <strong>pl.available_later,</strong> p.available_for_order, p.date_add, p.show_price, p.online_only, p.condition, p.id_shop_default
	FROM ps_product p
	<strong>LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product) WHERE pl.id_lang = 1</strong>"

So, we added a bunch of terms to look for (the ones in bold) and, after joining the ps_product_lang table, we set the language to be equal to 1, which, as i said, it’s English. Now, if at this point you find yourself rising a gun at your head because you don’t understand the code, you can google for mysql, or have a look at Net Tuts Plus to learn something about mySQL, queries, joins, and so. Of course, you can just copy/paste the end code if all you need is exporting products for a backup, or to another Prestashop store.

Step 3 – Dealing with categories

Let’s take one step further, things start becoming a bit complicated here

	"SELECT p.id_product, p.active, pl.name, <strong>GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ",") as categories,</strong> p.price, p.id_tax_rules_group, p.wholesale_price, p.reference, p.supplier_reference, p.id_supplier, p.id_manufacturer, p.upc, p.ecotax, p.weight, p.quantity, pl.description_short, pl.description, pl.meta_title, pl.meta_keywords, pl.meta_description, pl.link_rewrite, pl.available_now, pl.available_later, p.available_for_order, p.date_add, p.show_price, p.online_only, p.condition, p.id_shop_default
	FROM ps_product p
	LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
	<strong>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)</strong>
	WHERE pl.id_lang = 1
	<strong>AND cl.id_lang = 1</strong>
	GROUP BY p.id_product"

At this stage, I’m retrieving categories as a single value, separated by commas, since it’s the default separator for multiple values in the Prestashop CSV Import Tool. I also want to make sure I’m only selecting english category names.

Step 4 – Finishing touch

Finally, if you have multiple shops, don’t forget to add AND id_default_shop = 1 for each table which has multiple shops values. In this case, categories and products. Of course, since we didn’t join the category table before, we must do it now. Be sure to replace the id#1 of the shop i’ve set, to whatever is the one of the shop you’re exporting.

	"SELECT p.id_product, p.active, pl.name, GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ",") as categories, p.price, p.id_tax_rules_group, p.wholesale_price, p.reference, p.supplier_reference, p.id_supplier, p.id_manufacturer, p.upc, p.ecotax, p.weight, p.quantity, pl.description_short, pl.description, pl.meta_title, pl.meta_keywords, pl.meta_description, pl.link_rewrite, pl.available_now, pl.available_later, p.available_for_order, p.date_add, p.show_price, p.online_only, p.condition, p.id_shop_default
FROM ps_product p
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
<strong>AND p.id_shop_default = 1 AND c.id_shop_default = 1</strong>
GROUP BY p.id_product"

Step 6 – What was not exported

At this stage, the query is complete. Yes, unfortunately not all of the things needed can be exported using a single query. For example, images, which are dinamically generated, can’t be exported with a single query, since there is no url stored in the database. Of course, if you know a way to do this, please, let me know, and I will definitely upgrade the guide. But at any rate, for some weird reason the SQL can only be 1000 chars long, (that’s way i also had to leave off tags), and therefore something must probably be removed in order to add images in any way.

Also, Prestashop allows us to apply multiple discounts to a single product, isn’t it? Yeah. Then why is it allowing us to only import one discount per product? I’ve yet to understand this. Due to this limitation, I’m not going to export discounts (it would be useless to export something incomplete).

Lastly, note that I am leaving out “delete existing image” since there is no place where to retrieve it from. Of course, you should set it to 0 after downloading the csv, or just ignore the field when importing.

Now, to export the CSV, save, and back to the SQL Manager page click the little icon as shown below. You can also take a look at the generated results by clicking the magnifier icon.

Prestashop: export products without any module – Final Source

	"SELECT p.id_product, p.active, pl.name, GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ",") as categories, p.price, p.id_tax_rules_group, p.wholesale_price, p.reference, p.supplier_reference, p.id_supplier, p.id_manufacturer, p.upc, p.ecotax, p.weight, p.quantity, pl.description_short, pl.description, pl.meta_title, pl.meta_keywords, pl.meta_description, pl.link_rewrite, pl.available_now, pl.available_later, p.available_for_order, p.date_add, p.show_price, p.online_only, p.condition, p.id_shop_default
FROM ps_product p
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"

And this is everything on how to export products in Prestashop without the use of any module. It has some huge limitations, but at least it’s free. As always, if you have questions, feel free to contact me, and I’ll try to clarify your doubts! And, if you want to learn something more on how to import products using CSV files, you might want to check out my tutorial: Importing data into your store: Prestashop Import CSV Tool

 

Users’ contributions

Here is a nice addition on how to export tags, by Yoann Masrevery.

SELECT ps_product.id_product, GROUP_CONCAT( DISTINCT (
ps_tag.name
)
SEPARATOR ”,” ) AS tags
FROM ps_product
LEFT JOIN ps_product_tag ON ( ps_product.id_product = ps_product_tag.id_product )
LEFT JOIN ps_tag ON ( ps_product_tag.id_tag = ps_tag.id_tag )
GROUP BY ps_product.id_product

You like the tuts and want to say "thank you"? Well, you can always feel free to donate:

  • Pamela Bourque

    I keep getting this error message- wth does it mean? The “*” operator cannot be used in a nested query.

  • vlad

    I’m trying to end up with a file (.csv, .txt or something text) with a list of all my products with reference, features name and values and category (just for sorting). Is there any way of doing that?

  • nicogaldo

    Good guide. In my case I need to export the list of products in their combinations.

    SELECT
    p.id_product,
    pa.reference,
    pl.name,
    GROUP_CONCAT(DISTINCT(pal.name) SEPARATOR “, “) as combination,
    p.price,
    pq.quantity
    FROM ps_product_attribute pa
    LEFT JOIN ps_product p ON (p.id_product = pa.id_product)
    LEFT JOIN ps_stock_available pq ON (p.id_product = pq.id_product AND pa.id_product_attribute = pq.id_product_attribute)
    LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
    LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute)
    LEFT JOIN ps_attribute_lang pal ON (pac.id_attribute = pal.id_attribute)
    WHERE pl.id_lang = 1
    AND pal.id_lang = 1
    GROUP BY pa.reference
    ORDER BY p.id_product, pac.id_attribute

    Until that point I think I have controlled. There will be a way to export with Prestashop format to import the combinations of products?

    I guess as you can add a ‘,’ separating the combinations, you could add the text takes the format of import

    This is the format that I need, I mean the final result:
    http://www.ingemaronline.com/docs/csv_import/combinations_import.csv

    I use Prestashop 1.6.0.13 if it serves as data

    • Marco Guglielmelli

      Hello I’ve used this query in Prestashop 1.5.4.1 and it doesn’t work: it gives a generic “Error” alert.

      But if I use this query directly on the database in phpMyAdmin, it works!
      Do you know where could be the problem?
      Thanks

  • El Manouche

    Here is an improved version with the product image url and with the id_shop and id_lang not hardcoded.

    SELECT 
        p.id_product,
        p.active,
        pl.name,
        GROUP_CONCAT(DISTINCT (cl.name)
            SEPARATOR ',') as categories,
        p.price,
        p.id_tax_rules_group,
        p.wholesale_price,
        p.reference,
        p.supplier_reference,
        p.id_supplier,
        p.id_manufacturer,
        p.ean13,
        p.upc,
        p.ecotax,
        p.weight,
        p.quantity,
        pl.description_short,
        pl.description,
        pl.meta_title,
        pl.meta_keywords,
        pl.meta_description,
        pl.link_rewrite,
        pl.available_now,
        pl.available_later,
        p.available_for_order,
        p.date_add,
        p.show_price,
        concat('http://',
                ifnull(shop_domain.value, 'domain'),
                '/img/p/',
                if(CHAR_LENGTH(pi.id_image) &gt;= 5,
                    concat(SUBSTRING(pi.id_image from - 5 FOR 1),
                            '/'),
                    ''),
                if(CHAR_LENGTH(pi.id_image) &gt;= 4,
                    concat(SUBSTRING(pi.id_image from - 4 FOR 1),
                            '/'),
                    ''),
                if(CHAR_LENGTH(pi.id_image) &gt;= 3,
                    concat(SUBSTRING(pi.id_image from - 3 FOR 1),
                            '/'),
                    ''),
                if(CHAR_LENGTH(pi.id_image) &gt;= 2,
                    concat(SUBSTRING(pi.id_image from - 2 FOR 1),
                            '/'),
                    ''),
                if(CHAR_LENGTH(pi.id_image) &gt;= 1,
                    concat(SUBSTRING(pi.id_image from - 1 FOR 1),
                            '/'),
                    ''),
                pi.id_image,
                '.jpg') as image_url,
        p.online_only,
        p.condition,
        p.id_shop_default
    FROM
        ps_product p
            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)
            LEFT JOIN
        ps_image pi ON p.id_product = pi.id_product
            LEFT JOIN
        ps_configuration shop_domain ON shop_domain.name = 'PS_SHOP_DOMAIN'
    WHERE
        pl.id_lang = (SELECT id_lang FROM ps_lang WHERE active = '1' ORDER BY id_lang ASC LIMIT 1)
            AND cl.id_lang = pl.id_lang
            AND p.id_shop_default = (SELECT id_shop FROM ps_shop WHERE active = '1' ORDER BY id_shop ASC LIMIT 1)
            AND c.id_shop_default = p.id_shop_default
    GROUP BY p.id_product
    
  • AHN

    I am not a SQL guy – can anybody help me to get category data as per following fields:
    ID : Active : Name : Parent category : Root category : Description : Meta title : Meta Keywords : Meta description : URL rewritten : Image URL : ID/Name of shop

  • AHN

    fields retrieved do not match with 1.6…

  • Whispar Design

    Excellent article as usual. For me, this works but only for products without combinations. It does pull the main product id but does not pull the product ideas for the combinations within the main product (i.e. color combinations) Any thoughts on this?

  • Kon Rad

    Not working at 1.5.6.2

    SELECT p.id_product, p.active, pl.name, GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR “,”) as categories,
    p.price, p.id_tax_rules_group, p.wholesale_price, p.reference,
    p.supplier_reference, p.id_supplier, p.id_manufacturer, p.upc, p.ecotax,
    p.weight, p.quantity, pl.description_short, pl.description,
    pl.meta_title, pl.meta_keywords, pl.meta_description, pl.link_rewrite,
    pl.available_now, pl.available_later, p.available_for_order, p.date_add,
    p.show_price, p.online_only, p.condition, p.id_shop_default
    FROM ps_product p
    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

    This querry dont list any results with preview and download 0 byte file..
    Help?

    • El Manouche

      This is maybe because your default id_lang and or id_shop is not ‘1’.

      try replacing the where statement with this one:

      WHERE
      pl.id_lang = (SELECT id_lang FROM ps_lang WHERE active = ‘1’ ORDER BY id_lang ASC LIMIT 1)
      AND cl.id_lang = pl.id_lang
      AND p.id_shop_default = (SELECT id_shop FROM ps_shop WHERE active = ‘1’ ORDER BY id_shop ASC LIMIT 1)
      AND c.id_shop_default = p.id_shop_default

  • Boris

    Great post! I actually never programmed anything except some autoscript fishing code for a well know mmrpg :p However, since our onsite SEO relevant parts are very deteriogated I will try to make use of this information:
    I will try to export into LibreOffice a CSV with the necessary info to mass edit in a quick way all SEO relvant data.
    Ref, Name, Title, Meta Title, (Meta Description), URL

    Perhaps there are already better ways to do it. I do not know since I just started to dig into this topic of SEO. At least I had the time :)
    Boris

  • Zaki

    Import procedure in Store Manager for Prestashop application allows to download necessary data from a file of csv, xml, xls, xlsx, ods, txt and xml formats to your store.
    Futhermore it is possible to automate import with it’s addon called Automated Product Import: http://www.prestashopmanager.com/useful-articles/how-to/how-to-accomplish-prestashop-cron-import-with-automated-product-import-addon/
    The combination of the mentioned products is very good for stores with a large amount of products. Also you will be able to run import locally, so it will not overload your server.

  • Beluga

    The 1000 char limit seems to be gone in more recent versions, can anyone confirm? At least my order export query is 1182 chars long and works just fine. Didn’t find anything in PS changelogs, though.

  • Graham

    If you have a query that requires more than a 1000 characters then simply create the query as a view within the mysql database and then use select against the view within the backend of Prestashop. This way you can have a query that is only limited by the intrinsic capability of mysql.

    eg I defined a view to extract particular product information from the database which is 1244 characters long

    CREATE
    ALGORITHM = UNDEFINED
    DEFINER = `root`@`localhost`
    SQL SECURITY DEFINER
    VIEW `view_products` AS
    select
    `ps_product`.`id_product` AS `id_product`,
    `ps_product`.`active` AS `active`,
    `ps_product`.`reference` AS `reference`,
    `ps_product_lang`.`name` AS `product_name`,
    `ps_product_lang`.`description_short` AS `description_short`,
    `ps_product_lang`.`available_now` AS `available_now`,
    `ps_product`.`price` AS `price`,
    `ps_stock_available`.`quantity` AS `quantity`,
    `ps_product`.`weight` AS `weight`,
    `ps_product`.`condition` AS `condition`,
    `ps_category_lang`.`name` AS `category_name`,
    `ps_supplier`.`name` AS `Supplier_name`
    from
    (((((`ps_product`
    join `ps_product_lang` ON ((`ps_product`.`id_product` = `ps_product_lang`.`id_product`)))
    join `ps_category` ON ((`ps_product`.`id_category_default` = `ps_category`.`id_category`)))
    join `ps_category_lang` ON (((`ps_category_lang`.`id_lang` = `ps_product_lang`.`id_lang`)
    and (`ps_category`.`id_category` = `ps_category_lang`.`id_category`))))
    join `ps_stock_available` ON ((`ps_product`.`id_product` = `ps_stock_available`.`id_product`)))
    left join `ps_supplier` ON ((`ps_product`.`id_supplier` = `ps_supplier`.`id_supplier`)))

    And then have the following as the SQL manager view.
    eg SELECT * FROM `view_products`

  • Nikos

    If i would like instead of csv,an xml file..Does it happen..?

  • raph

    Hi,

    I’m new to prestashop. How to I get the images URL of a product trough a simple query?

    • http://nemops.com Nemo

      Not with a query, unfortunately! You need to build the url using Link::getImageLink()

    • El Manouche

      It’s quite tricky.

      The image url is formed from the domain name and the image id.

      Here is the complete request:

      SELECT
      p.id_product,
      p.active,
      pl.name,
      GROUP_CONCAT(DISTINCT (cl.name)
      SEPARATOR ‘,’) as categories,
      p.price,
      p.id_tax_rules_group,
      p.wholesale_price,
      p.reference,
      p.supplier_reference,
      p.id_supplier,
      p.id_manufacturer,
      p.ean13,
      p.upc,
      p.ecotax,
      p.weight,
      p.quantity,
      pl.description_short,
      pl.description,
      pl.meta_title,
      pl.meta_keywords,
      pl.meta_description,
      pl.link_rewrite,
      pl.available_now,
      pl.available_later,
      p.available_for_order,
      p.date_add,
      p.show_price,
      concat(‘http://’,
      ifnull(shop_domain.value, ‘domain’),
      ‘/img/p/’,
      if(CHAR_LENGTH(pi.id_image) >= 5,
      concat(SUBSTRING(pi.id_image from – 5 FOR 1),
      ‘/’),
      ”),
      if(CHAR_LENGTH(pi.id_image) >= 4,
      concat(SUBSTRING(pi.id_image from – 4 FOR 1),
      ‘/’),
      ”),
      if(CHAR_LENGTH(pi.id_image) >= 3,
      concat(SUBSTRING(pi.id_image from – 3 FOR 1),
      ‘/’),
      ”),
      if(CHAR_LENGTH(pi.id_image) >= 2,
      concat(SUBSTRING(pi.id_image from – 2 FOR 1),
      ‘/’),
      ”),
      if(CHAR_LENGTH(pi.id_image) >= 1,
      concat(SUBSTRING(pi.id_image from – 1 FOR 1),
      ‘/’),
      ”),
      pi.id_image,
      ‘.jpg’) as image_url,
      p.online_only,
      p.condition,
      p.id_shop_default
      FROM
      ps_product p
      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)
      LEFT JOIN
      ps_image pi ON p.id_product = pi.id_product
      LEFT JOIN
      ps_configuration shop_domain ON shop_domain.name = ‘PS_SHOP_DOMAIN’
      WHERE
      pl.id_lang = (SELECT
      id_lang
      FROM
      ps_lang
      WHERE
      active = ‘1’
      ORDER BY id_lang ASC
      LIMIT 1)
      AND cl.id_lang = pl.id_lang
      AND p.id_shop_default = (SELECT
      id_shop
      FROM
      ps_shop
      WHERE
      active = ‘1’
      ORDER BY id_shop ASC
      LIMIT 1)
      AND c.id_shop_default = p.id_shop_default
      GROUP BY p.id_product

  • Beluga

    I made a post on PS forums on using the SQL manager to to export detailed orders.

  • https://manzol.hu Mátyus Botond

    Hi,

    Thank you for SQL query. I want to export my products with attributes and it help me.

    I added some table for attributes:

    SELECT p.id_product, pl.name, GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR “,”) as categories, p.price, pa.price, p.id_tax_rules_group, p.wholesale_price, p.reference, p.supplier_reference, p.id_supplier, p.id_manufacturer, p.upc, p.ecotax, p.weight, p.quantity, pl.description_short, pl.description, pl.meta_title, pl.meta_keywords, pl.meta_description, pl.link_rewrite, pl.available_now, pl.available_later, p.available_for_order, p.date_add, p.show_price, p.online_only, p.condition, p.id_shop_default, al.name
    FROM ps_product p
    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)
    LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)
    LEFT JOIN ps_product_attribute_combination pac ON (pac.id_product_attribute = pa.id_product_attribute)
    LEFT JOIN ps_attribute_lang al ON (al.id_attribute = pac.id_attribute)
    WHERE pl.id_lang = 1
    AND cl.id_lang = 1
    AND p.id_shop_default = 1
    AND c.id_shop_default = 1
    GROUP BY pac.id_product_attribute

    I hope it will help who want to export products with attributes.

    • http://proyectoFactultad Fernando Coitiño

      Mátyus, many (many) thanks for your SQL, is very good!
      I have a question for you:
      In my case I have 2 attributes for a product (color and size), to execute the query brings back the correct items, but always with the first attribute (color), I was trying to modify the SQL but I have not succeeded.
      Can you help me??

      Thanks again and greetings from Uruguay!
      Fernando

    • http://thecrewonline.net/ The Crew Game

      Oh man, this is awesome!

You like the tuts and want to say "thank you"? Well, you can always feel free to donate: