I am doing a report module to show various information about drupal commerce orders and line items.

I am now facing a problem with the line items:

For some orders, in the commerce_line_item table, there are multiple line items for the same product and quantity.

However, in the order page of these orders, I only see one line item. I suppose only one of these line items is the good one, but how can I know which one is good in the table?

They looks the same except the line_item_id field which is different. So how can I get the line items of an order without any of these dupplicate line items?

Why are there dupplicate line items in the table?

For instance, this query:

SELECT SUM(quantity)
FROM {commerce_line_item}
WHERE order_id = 388

gives me 5 intead of 1.


I am using Drupal Commerce 7.x-1.3.

Dump for the order 388:

-- commerce_product
product_id  revision_id sku     title   type    language    uid status  created changed data
1   52  sku     Product Name    product_type    und     1   1   1342634788  1352819763  [BLOB - 4o]
-- commerce_order
order_id    order_number    revision_id type    uid mail    status  created changed hostname    data
388     388     1865    commerce_order  123     [email protected]    completed   1359728586  1359755276     [BLOB - 208o]
-- commerce_order_revision
order_id    order_number    revision_id revision_uid    mail    status  log revision_timestamp  revision_hostname   data
388     388     1838    984     [email protected]    cart    Log message     1359754659     [BLOB - 6o]
388     388     1859    984     [email protected]    checkout_checkout   Log message     1359754827     [BLOB - 6o]
388     388     1860    984     [email protected]    checkout_review     Log message     1359754829     [BLOB - 6o]
388     388     1861    984     [email protected]    cart    Log message     1359755005     [BLOB - 6o]
388     388     1862    984     [email protected]    checkout_checkout   Log message 1359755034     [BLOB - 6o]
388     388     1863    984     [email protected]    checkout_review     Log message     1359755074     [BLOB - 74o]
388     388     1864    984     [email protected]    checkout_payment    Log message 1359755075     [BLOB - 153o]
388     388     1865    0   [email protected]    completed   Log message     1359755276     [BLOB - 208o]
-- commerce_line_item
line_item_id    order_id    type    line_item_label quantity    created changed data
773     388     product     sku     1.00    1359754516  1359754829  [BLOB - 232o]
763     388     product     sku     1.00    1359728686  1359728686  [BLOB - 232o]
762     388     product     sku     1.00    1359728699  1359728699  [BLOB - 232o]
761     388     product     sku     1.00    1359728689  1359728689  [BLOB - 232o]
758     388     product     sku     1.00    1359728642  1359728642  [BLOB - 232o]

Thanks for your help.

  • Can you give dumps of commerce_product, commerce_order, commerce_order_revision and commerce_line_item tables? Mar 26, 2013 at 22:27
  • I added the dumps. Any idea to solve my problem?
    – antoyo
    Mar 31, 2013 at 20:54
  • No ideas about scalar sql. But you can use entity_metadata_wrapper(), it returns required data in drupal commerce rules. Apr 1, 2013 at 19:51
  • entity_load('commerce_order', array(388), array(), TRUE); gives me the right number of line item (one), but I am not able to figure out the undelyring SQL query. I used the devel module to show every single SQL queries and it shows the SQL query fetching the order, but I don't see any SQL query fetching the line items (even if the $reset argument is TRUE). I'll continue looking for the SQL query fetching the line items. Do you think entity_load() only loads the last line item from the line items with the same sku?
    – antoyo
    Apr 3, 2013 at 1:55

1 Answer 1


I finally found out how to do what I want. The last commerce line items are in the field_data_commerce_line_items table. The commerce_line_item table seems to contain all the commerce line item revisions.

SELECT SUM(quantity)
FROM {commerce_line_item}
INNER JOIN {field_data_commerce_line_items}
    ON {field_data_commerce_line_items}.commerce_line_items_line_item_id = {commerce_line_item}.line_item_id
WHERE order_id = 388

I don't know if it is documented somewhere. However, it is what I found by reading the source code and the SQL queries.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.