Posted by joachim on October 23, 2011 at 9:09am
7 followers
| Project: | Drupal Commerce |
| Version: | 7.x-1.x-dev |
| Component: | Views integration |
| Category: | feature request |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | needs review |
Issue Summary
This seems to me like it should be a fairly common use case: show a view of products filtered by the current order. For example, a view of 'recommended products' shouldn't show products that the customer is already buying.
For products *in* an order, Commerce's cart view in fact shows an order view, with a relationship to line items. This works for a single order, and only when we want to include things.
If we want to exclude them, it's rather more complicated, and getting it to work with Views is currently stumping me.
Here is some SQL which works, but getting Views / DBTNG to produce it doesn't seem feasible:
select cp.sku, fdcp.*, commerce_line_item.* from commerce_product cp
LEFT JOIN
(
field_data_commerce_product fdcp
INNER JOIN commerce_line_item ON (fdcp.entity_id = commerce_line_item.line_item_id AND commerce_line_item.order_id = 4)
)
ON cp.product_id = fdcp.commerce_product_product_id
WHERE order_id IS NULLWithout the grouped join, we need:
select cp.sku, fdcp.*, commerce_line_item.* from commerce_product cp
LEFT JOIN
field_data_commerce_product fdcp
LEFT JOIN
commerce_line_item ON (fdcp.entity_id = commerce_line_item.line_item_id AND commerce_line_item.order_id = 4)
ON cp.product_id = fdcp.commerce_product_product_id
WHERE
fdcp.commerce_product_product_id IS NULL AND
order_id IS NULLI'm still not sure whether that's doable as an argument handler.
Any improvements on that query?
Comments
#1
Hmm, this is a great idea. I'm going to ping bojanz on this, as he's our resident Views wizard. : )
#2
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 :)
#3
Fixed a bug when the user's cart is empty.
#4
Hmm something weird is going on when products are removed from the shopping cart.
What happens to the line item data in that event?
#5
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.
#6
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
#7
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.
#8
I´m looking for an option to mark products in a view table, who has been add to the cart already.
I played with rules around and achieved no success.
It seems the "provide a views argument to filter products in or out of a given order" function could be a solution.
So i was wondering if their any progress here??
Kirk
#9
Hi, mysoftmusic, did you reviewed the patch? I think the guys want to know if the patch is correct before bringing it in..
greetings, Martijn