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

Question

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.

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  1.2.3.4     [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  1.2.3.4     [BLOB - 6o]
388     388     1859    984     [email protected]    checkout_checkout   Log message     1359754827  1.2.3.4     [BLOB - 6o]
388     388     1860    984     [email protected]    checkout_review     Log message     1359754829  1.2.3.4     [BLOB - 6o]
388     388     1861    984     [email protected]    cart    Log message     1359755005  1.2.3.4     [BLOB - 6o]
388     388     1862    984     [email protected]    checkout_checkout   Log message 1359755034  1.2.3.4     [BLOB - 6o]
388     388     1863    984     [email protected]    checkout_review     Log message     1359755074  1.2.3.4     [BLOB - 74o]
388     388     1864    984     [email protected]    checkout_payment    Log message 1359755075  1.2.3.4     [BLOB - 153o]
388     388     1865    0   [email protected]    completed   Log message     1359755276  1.2.3.4     [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.

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.