Jump to content

sql query for export all images url


Asep Komarudin
 Share

Recommended Posts

Hi,, anyone can help me with sql query?
I have a query for export products with combination,, but I only get 1 images for 1 combination item.
example if I have 1 products with 2 combination and 3 images in each combination,, this query only give me 2 images url.
how can I get all images url?
 
*sorry for my english, hope someone can help me,, thanks..
 

SELECT
p.active 'Active',
m.name 'Manufacturer',
p.id_product 'Product number',
p.reference 'Reference',
pl.name 'Product name',
GROUP_CONCAT(DISTINCT(al.name) SEPARATOR ", ") AS 'Combination',
s.quantity 'Quantity',
p.price 'Price w/o VAT',
pa.price 'Combination price',
p.wholesale_price 'Wholesale price',
GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ",") AS 'Product groups',
p.weight 'Weight',
p.id_tax_rules_group 'TAX group',
pa.reference 'Combination reference',
pl.description_short 'Short description',
pl.description 'Long description',
pl.meta_title 'Meta Title',
pl.meta_keywords 'Meta Keywords',
pl.meta_description 'Meta Description',
pl.link_rewrite 'Link',
pl.available_now 'In stock text',
pl.available_later 'Coming text',
p.available_for_order 'Orderable text',
p.date_add 'Added',
p.show_price 'Show price',
p.online_only 'Only online',
CONCAT("http://", su.domain, su.physical_uri , IF(pai.id_image IS NOT NULL,pai.id_image,im.id_image), "/", REPLACE(REPLACE(REPLACE(pl.name," ","_"),"\"",""),"#",""), ".jpg") as "Image URL"

FROM
ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product and pl.id_lang=2)
LEFT JOIN ps_manufacturer m ON (p.id_manufacturer = m.id_manufacturer)
LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN ps_category c ON (cp.id_category = c.id_category)
LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category and cl.id_lang=2)
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)
LEFT JOIN ps_stock_available s ON (p.id_product = s.id_product and (pa.id_product_attribute=s.id_product_attribute or pa.id_product_attribute is null))
LEFT JOIN ps_product_tag pt ON (p.id_product = pt.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 and al.id_lang=2)
LEFT JOIN ps_shop sh ON p.id_shop_default = sh.id_shop 
LEFT JOIN ps_shop_url su ON su.id_shop = sh.id_shop AND su.main = 1
LEFT JOIN ps_image im ON (p.id_product = im.id_product AND im.cover = 1)
LEFT JOIN ps_product_attribute_image pai ON (pai.id_product_attribute = s.id_product_attribute)
GROUP BY p.id_product,pac.id_product_attribute order by p.id_product

Share this post


Link to post
Share on other sites

No answer for this question :(

well,, I'm done with this problem..

Maybe someone need it, I'll share a query.

 

Thanks.

SELECT
p.active 'Active',
m.name 'Manufacturer',
p.id_product 'Product number',
p.reference 'Reference',
pl.name 'Product name',
GROUP_CONCAT(DISTINCT(al.name) SEPARATOR ", ") AS 'Combination',
s.quantity 'Quantity',
p.price 'Price w/o VAT',
pa.price 'Combination price',
p.wholesale_price 'Wholesale price',
GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ",") AS 'Product groups',
p.weight 'Weight',
p.id_tax_rules_group 'TAX group',
pa.reference 'Combination reference',
pl.description_short 'Short description',
pl.description 'Long description',
pl.meta_title 'Meta Title',
pl.meta_keywords 'Meta Keywords',
pl.meta_description 'Meta Description',
pl.link_rewrite 'Link',
pl.available_now 'In stock text',
pl.available_later 'Coming text',
p.available_for_order 'Orderable text',
p.date_add 'Added',
p.show_price 'Show price',
p.online_only 'Only online',
GROUP_CONCAT("http://", su.domain, su.physical_uri , IF(pai.id_image IS NOT NULL,pai.id_image,im.id_image), "/", REPLACE(REPLACE(REPLACE(pl.name," ","_"),"\"",""),"#",""), ".jpg") as "Image URL"

FROM
ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product and pl.id_lang=2)
LEFT JOIN ps_manufacturer m ON (p.id_manufacturer = m.id_manufacturer)
LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN ps_category c ON (cp.id_category = c.id_category)
LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category and cl.id_lang=2)
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)
LEFT JOIN ps_stock_available s ON (p.id_product = s.id_product and (pa.id_product_attribute=s.id_product_attribute or pa.id_product_attribute is null))
LEFT JOIN ps_product_tag pt ON (p.id_product = pt.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 and al.id_lang=2)
LEFT JOIN ps_shop sh ON p.id_shop_default = sh.id_shop 
LEFT JOIN ps_shop_url su ON su.id_shop = sh.id_shop AND su.main = 1
LEFT JOIN ps_image im ON (p.id_product = im.id_product AND im.cover = 1)
LEFT JOIN ps_product_attribute_image pai ON (pai.id_product_attribute = s.id_product_attribute)
GROUP BY p.id_product,pac.id_product_attribute order by p.id_product
  • Like 3

Share this post


Link to post
Share on other sites

  • 6 months later...
  • 1 year later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More