Vote up!
Vote down!

Where is product listing function exists in drupal comerce


I am a web developer and integrating our software with drupal commerce store.

I am trying to get all the products which are displaying in admin but unable to fetch all of them. I want to know which drupal commerce function is used to get all the product lists as I need all the products from the store into my software.

Please let me know how can I get all the products from the store.
Please send me the code (with sql queris ) by which I can get product lists.

Quick Answer would be appreciable.


Asked by: maddythecoder
on April 26, 2013


Hello Drupal Support,

I didn't get response from your side? Please reply me.

- maddythecoder on April 29, 2013

1 Answer

Vote up!
Vote down!

Hello Drupal User,

If a "quick answer would be appreciable" maybe next time try our Turbo Ticket offering: https://marketplace.commerceguys.com/support/turbo-ticket

Let me try and answer the underlying question you have posted: You're trying to see all of your product variations, as opposed to the product displays. To do that, I would create a view of your product entities. Here's the SQL that Views generates for a product entity view:

SELECT commerce_product.sku AS commerce_product_sku, commerce_product.title AS commerce_product_title, commerce_product.product_id AS product_id, commerce_product.type AS commerce_product_type, commerce_product.status AS commerce_product_status, 'commerce_product' AS field_data_commerce_price_commerce_product_entity_type
{commerce_product} commerce_product
Josh Miller
Answer by: Josh Miller
Posted: Apr 29, 2013


Thanks for reply but the query you sent doesn't have all information of products.
I need quantity,category and all other info which belongs to a product in a shopping cart.

I am trying following query but it doesn't show all the products because some matches failed in join query.

"SELECT p.product_id,p.sku,p.title,c.commerce_price_amount,q.commerce_stock_value FROM {commerce_product} p,{field_data_commerce_price} c,{field_data_commerce_stock} q, {field_data_field_product} fp where fp.field_product_product_id = p.product_id and p.product_id=c.entity_id and p.product_id=q.entity_id order by p.product_id "

But, I need a query which gives me all the products with all the associated information like below:

Variations or options

Please help me out. Also I found that field_data_commerce_stock table doesn't exists in all version of drupal commerce so how to get quantity.


- maddythecoder on April 29, 2013

Do you know how to use Views? Panels? View Modes? Rules? Not sure how to help you. You're asking for SQL queries and Drupal is designed to give you interfaces to find these kinds of things. Want a list of products with all available data? Use Views. Want a highly configurable layout with fields in various configurations? Use Panels. Want to make sure your price field comes before the title and after the photos? Use View Modes. Want to sync things, modify things, or react on events? Use Rules.

- Josh Miller on April 30, 2013