provide a views argument to filter products in or out of a given order

Taking the above query apart:

select cp.sku, fdcp.*, commerce_line_item.* from commerce_product cp
LEFT JOIN
field_data_commerce_product fdcp
  ON cp.product_id = fdcp.commerce_product_product_id
  -- we have table definition for field_data_commerce_product that gets us this far.

LEFT JOIN
commerce_line_item 
  ON (
    fdcp.entity_id = commerce_line_item.line_item_id 
    AND 
    -- this clause is tricky, as it requires the views join 'extra' to have dynamic data,
    -- hence requires some mucking about with our join
    commerce_line_item.order_id = 4
  )

WHERE
  -- this is just a case of adding an extra 'where' when the argument is set to exclude the value
  fdcp.commerce_product_product_id IS NULL
  AND
  order_id IS NULL

Here's a patch.
It turns out that the above can be generalized into an argument handler that gives products in *or* out of a given order.
This combined with an argument default plugin that returns the current user's cart order ID seems to me to be a nicely flexible combination (and that's in the patch too :)

Fixed a bug when the user's cart is empty.

Hmm something weird is going on when products are removed from the shopping cart.

What happens to the line item data in that event?

Argh, no the problem is the query.

This version better demonstrates it, as it selects the columns that cause it to go wrong:

SELECT commerce_product.product_id AS product_id,
	field_data_commerce_product.entity_id AS fdcp_line_id,
	field_data_commerce_product.commerce_product_product_id AS fdcp_prod_id,
	commerce_product_commerce_line_item.*
FROM 
commerce_product commerce_product
LEFT JOIN field_data_commerce_product field_data_commerce_product 
	ON 
	commerce_product.product_id = field_data_commerce_product.commerce_product_product_id 
	AND 
	field_data_commerce_product.deleted = '0'
LEFT JOIN commerce_line_item commerce_product_commerce_line_item 
	ON 
	field_data_commerce_product.entity_id = commerce_product_commerce_line_item.line_item_id 
	AND
	commerce_product_commerce_line_item.order_id = '3'

WHERE 
-- not in cart:
  -- field_data_commerce_product.commerce_product_product_id IS NULL AND
	-- commerce_product_commerce_line_item.order_id IS NULL AND
-- in cart:
	-- commerce_product_commerce_line_item.order_id = 3 AND

1 = 1

The join to field_data_commerce_product gets us all line item data for each product, causing multiple rows. The problem is that we only get a row that the 'not in cart' filter conditions work on if the product *had no line items at all*. In other words, it's not in any cart or order or ever has been.

So the problem was not that items were removed from the cart, but that they were meanwhile in another user's cart or order.

Hence we really do need to be able to take field_data_commerce_product and commerce_line_item in one go, because we only want to join onto field_data_commerce_product for the given order ID, and that's not in that table!

Given we can't do grouped joins, it's going to have to be a subquery.

In other words, we need it to be of this form:

SELECT *
FROM 
commerce_product commerce_product
LEFT JOIN 
(SELECT *
FROM field_data_commerce_product field_data_commerce_product 
LEFT JOIN commerce_line_item commerce_product_commerce_line_item 
	ON 
	field_data_commerce_product.entity_id = commerce_product_commerce_line_item.line_item_id 
	AND
	commerce_product_commerce_line_item.order_id = '3'
	) alias
	ON [conditions]	
WHERE 
-- not in cart:
  -- field_data_commerce_product.commerce_product_product_id IS NULL AND
	-- commerce_product_commerce_line_item.order_id IS NULL AND
-- in cart:
	-- commerce_product_commerce_line_item.order_id = 3 AND
1 = 1 -- this is just so I can easily comment out clauses ;)

with all the right fields and conditions reworked for the subquery.

Here's the updated query. The WHERE clauses are simpler at least!

(Ignore the dummy numerical columns and clauses; they're there so I don't need to worry about commas when I add or remove parts of the query!)

SELECT commerce_product.product_id AS product_id,
	alias.line_item_id,
	alias.product_id,
	42
FROM 
commerce_product commerce_product
LEFT JOIN 
  (SELECT 
    -- field_data_commerce_product.entity_id AS line_item_id,
  	 field_data_commerce_product.commerce_product_product_id AS product_id,
     commerce_line_item.line_item_id,
     commerce_line_item.order_id,
     42
   
  FROM field_data_commerce_product field_data_commerce_product 
  INNER JOIN commerce_line_item commerce_line_item 
  	ON 
    	field_data_commerce_product.entity_id = commerce_line_item.line_item_id 
    	AND
    	commerce_line_item.order_id = '3'
	 WHERE field_data_commerce_product.deleted = 0
  ) alias
ON 
	commerce_product.product_id = alias.product_id

WHERE 
-- not in cart:
	-- alias.order_id IS NULL AND
-- in cart:
	-- alias.order_id = 3 AND
1 = 1

And here's the patch that makes that query work in Views.

This time, rather than fiddle with the join's 'extra' clause, we need our own join class to make the subquery.