Discussions

Multiple line items in the database but one in the order page

Hello.
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.

Note:
I am using Drupal Commerce 7.x-1.3.

Thanks for your help.

Tags: 
Line Items
glcp
Posted: Mar 22, 2013

Comments

glcp on April 9, 2013

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.