Query to export products with url from DB

[Sono_fre]

Hi all !

I'm trying to execute a query on a prestashop 1.4 db in order to extract all the products, but i'm missing something when trying to retrieve a product category given a product in order to calculate the exact product url as domain/category/product_id-product-name.html.

Here i'm trying to extract all the products with urls:

select

    ps_product.id_product as "id",

    ps_product_lang.name as "name",

    ps_product_lang.description_short as "description",

    ps_product.wholesale_price * 1.22 as "wholesale_price",

    ps_product.price * 1.22 as "retail_price",

    ps_product.ean13 as "EAN13",

 

           ifnull(ps_configuration.value,'examplet.com'), 

      '/',

           clang.link_rewrite ,

           '/' ,

           ps_product.id_product ,

           '-',

           ps_product_lang.link_rewrite ,

           '.html') as "product_url"

into

    outfile '/tmp/products.csv'

    fields terminated by ','

    optionally enclosed by '"'

    escaped by '\\'

    lines terminated by '\n'

from

        ps_product, ps_product_lang , 

    ps_configuration ,

ps_category_lang clang

where

    clang.id_shop = ps_product_lang.id_shop and 

    clang.id_lang = ps_product_lang.id_lang and 

    clang.id_category = ps_product_lang.id_product and

    ps_configuration.name = 'PS_SHOP_DOMAIN' and

    ps_product.id_product = ps_product_lang.id_product and

    ps_product.active = true and

    ps_product_lang.id_lang = (select value from ps_configuration where name = "PS_LANG_DEFAULT");

 

The problem is that this query gives me only 177 items while the whole catalogue is of about 1777 products.

For documentation purpose this is the query that i'm using to extract correctly all the products from the db:

 

select

    ps_product.id_product as "id",

    ps_product_lang.name as "name",

    ps_product_lang.description_short as "description",

    ps_product.wholesale_price * 1.22 as "wholesale_price",

    ps_product.price * 1.22 as "retail_price",

    ps_product.ean13 as "EAN13"

into

    outfile '/tmp/products.csv'

    fields terminated by ','

    optionally enclosed by '"'

    escaped by '\\'

    lines terminated by '\n'

from

    ps_product, ps_product_lang

where

    ps_product.id_product = ps_product_lang.id_product and

    ps_product.active = true and

    ps_product_lang.id_lang = (select value from ps_configuration where name = "PS_LANG_DEFAULT");

 

Any help will be appreciated, thanks.

Edited January 21, 2014 by Sono_fre (see edit history)

[vekia]

in fact exact url of product depends on many other things, like rewrite param and also it's related to seo & urls settings (friendly urls)

 

so, it's impossible to get VALID url of product from database only.

[Sono_fre]

I'm able to read the friendly url from db,

i made this query that actually works:

select
    ps_product.id_product as "id",
    ps_product_lang.name as "name",
    ps_product_lang.description_short as "description",
    ps_product.wholesale_price * 1.22 as "wholesale_price",
    ps_product.price * 1.22 as "retail_price",
    ps_product.ean13 as "EAN13",

           ifnull(ps_configuration.value,'example.com'),
           '/',
           ps_product.id_product ,
           '-',
           ps_product_lang.link_rewrite ,
           '.html') as "product_url"
into
    outfile '/tmp/products.csv'
    fields terminated by ','
    optionally enclosed by '"'
    escaped by '\\'
    lines terminated by '\n'
from
        ps_product, ps_product_lang ,
        ps_configuration
where
    ps_configuration.name = 'PS_SHOP_DOMAIN' and
    ps_product.id_product = ps_product_lang.id_product and
    ps_product.active = true and
    ps_product_lang.id_lang = (select value from ps_configuration where name = "PS_LANG_DEFAULT");

 

I just ignored the category and let the shop redirect me while accessing to the the url built as above,

hope this could help someone

Edited January 22, 2014 by Sono_fre (see edit history)