UC Node Checkout should provide a view to query against the related checkout node

Better status.

Also, it's usually best to create a separate issue for each separate thing you fix. The "Bonus" you mention is harder to fix when it's hidden inside an issue like this.

I think this is a good idea but it doesn't work. The node is not available as a relationship, it looks like the relationship is not set up right (it shows up as 'array' in the relationship list).

Also, it would be better to rename the relationship to prefix it with 'uc_node_checkout' rather than polluting the Views namespace.

One more thing, I would like to see a relationship going the other way too. For instance, I want to create a view of registrations that will link back to the originating orders.

I played around with this some trying to figure out what it would take to get it working and ran out of time.

OK, with merlin's help, I got this to work. Attached is a patch for uc_node_checkout.module and the views include file that creates the relationship (added a .txt extension so it could be uploaded). I have it working on my site.

How can I apply this patch? Where?

Thanks a lot.

Nicolo'

here's a re-roll that's a single patch.

I am clearly missing something, but I cannot seem to get this patch to work. On my system, it is joining the associated node (this is good) with a random node that happens to have an nid that corresponds to the order id. From what I can tell, it is using the order_product_id column as if it was an nid, when, at least on my setup, it is an Ubercart specific (non-node) order product id. It seems that in order for this to work, it would necessarily need to teach Views about the uc_order_products table. Perhaps I have my Ubercart setup incorrectly? I am using the most recent versions of Ubercart (6.x-2.0-rc3) and UC_Node_Checkout (6.x-2.0-beta3). I'm sure this is one of those "oh duh" situations, but at the moment, it's not making a lot of sense.

This patch isn't going to work (I cringed when I saw that drewish re-rolled it as one file). I should have jumped on here and explained that earlier.

The problem was the incorrect assumption I made (as stated by faction) that assumed order_product_id in uc_node_checkout_order_product was a nid value that could be mapped back to the node table. In actuality, it goes to uc_order_product, where you can then get the nid value and link back to the node table. In other words, the correct query is something like this:

select n1.*, n2.*
from node n1, uc_node_checkout_order_products ncop, uc_order_products op, node n2
where n1.nid = ncop.nid
and ncop.order_product_id = op.order_product_id
and op.nid = n2.nid

instead of this:

select n1.*, n2.*
from node n1, uc_node_checkout_order_products ncop, uc_order_products op, node n2
where n1.nid = ncop.nid
and ncop.order_product_id = n2.nid

This can easily be done as a relationship in Views (it might require a custom handler), but I just haven't had the time to sit down and write it. If anyone wants to write it, go for it. I'd be willing to help test it. If I do get time, I'll sit down and do it, but I don't see it happening soon.

#6 / 8 - does that mean this patch is non-functioning? In other words, should I still review it?

Ah rszrama, you are alive in this issue queue!!

No, it is not correct at this point, so reviewing it would not be worth it. As I stated, I need to rewrite it, possibly with a custom views handler, to generate the correct SQL to link node -> uc_node_checkout_order_products -> uc_order_products -> node.

P.S. While I have your attention, could I get you to look at this uc_node_checkout issue, too?

Thanks.

hehe, yeah I had some momentum from a bit of contract work and wanted to capitalize. I cleaned out a lot of issues and got a good local test site for committing some patches. I've done all I can today, but I'll be back to investigate the various access / anonymous user issues. I didn't touch any of those today.

subscribing

I've got this figured out. Woot! OK, you actually don't need another view handler. You were almost there, wonder95. You just needed to add the alias table!

One problem though, is that the built-in uc views automatically link up all the uc views to the node directly. This isn't what you want with the node checkout module, however -- you want the node details that are related with the order via that checkout lookup table.

I haven't finished, but I wanted to post how to start it, anyway. Here is an example for how to link to the price of a product. I'm glad I stumbled into this answer, since I actually did start writing my own handler and figured it out it wouldn't work. After thinking about it, I found this much simpler answer.

Apologies, I haven't finished yet so no patch, as I wanted to share this asap.

First, put this function into uc_node_checkout.module, like wonder95 did:

function uc_node_checkout_views_api() {
  return array(
    'api' => '2.0',
    'path' => drupal_get_path('module', 'uc_node_checkout'),
  );
}

Second, build a file called uc_node_checkout.views.inc and put the following into it. (Again, not finished, but it's the start)

<?php
/**
 * Implementation of hook_views_data().
 */
// allow adding an ubercart order product (not the whole order) to some node data
function uc_node_checkout_views_data() {
	/**
	 * Define the table structures. Note we're using aliases so we don't
	 * go into the namespace of the existing order_products table.
	 * We need to define all our fields from scratch using the join table. 
	 */
   $data = array();
   $data['uc_opc']['table']['group'] = t('UC Node Product');

   $data['uc_opc']['table']['join']['node'] = array(
	   'table' => 'uc_order_products',
	   'left_table' => 'uc_opc_join',
      'left_field' => 'order_product_id',
      'field' => 'order_product_id',
      'type' => 'INNER',
  );
  
   $data['uc_opc_join']['table']['join']['node'] = array(
	   'table' => 'uc_node_checkout_order_products',
      'left_field' => 'nid',
      'field' => 'nid',
      'type' => 'INNER',
  );


  /**
   * Begin defining fields
   */  
  $data['uc_opc']['price'] = array(
    'title' => t('Price'),
    'help' => 'The price of the product that was linked to this node',
    'field' => array(
      'handler' => 'uc_product_handler_field_price',
      'click sortable' => TRUE,
    ),
    'sort' => array(
      'handler' => 'views_handler_sort',
    ),
    'filter' => array(
      'handler' => 'views_handler_filter_numeric',
    ),
  );

  return $data;
}

Interesting, tonight I'll tray it.
Stay tuned ;)

Just starting a project, I'd love to use this approach. Hopefully when this progresses further, there'll be patches. Much appreciated!

any progress on this? it looks like it would be good. how do i best evaluate what is in #13? what feedback would you like on the code in #13.

Chris

Ok, tried to figure this out today.

Here's a diagram showing the database tables we want to work with:

Of these involved tables, the uc_node_checkout module is only responsible for uc_node_checkout_order_products. node is provided by Views module, and uc_orders and uc_product_orders are provided by uc_views module.

However!

The uc_products table is problematic, because it also defines a join between its nid column and the node table. So when it gets added to the mix, suddenly everything breaks, because you're no longer building a list of event registrations; now you're building a list of the referenced event products that the people who bought registrations were registering to (ugh). Surprise! :P

wonder95's first approach is no good (as he said), because the nature of order_product_id was misunderstood as being that of the "Product Node ID" (same as uc_products.nid). What order_product_id actually is is a link to a line item within the order from the uc_order_products table.

whitelancer's approach is basically to re-implement the views integration for uc_order_products (and presumably uc_orders) under an alias so that they don't collide with uc_views's views integration for those tables. I think this will work, but it seems terribly heavy, since improvements to uc_views would need to be made in two places.

OTOH, I'm not sure what choice we have, really, since we can't hook_views_data_alter() out the join between uc_product and node, since that is desired on other types of Views, just not this one.

So.. I'm a bit stumped atm, but at least there's a summary here for others to build from. :P

No time to dig into this right now, but one approach that might work is to go in the direction of #13, but instead of copying and repeating all the data, do it using hook_views_data_alter to copy the original data to a new alias, by doing $data['uc_opc'] = $data[ORIGINAL_NAME]. That way we automatically pick up all fields and any changes to the original data without re-writing everything. Then make whatever tweaks are needed to our alias so it works the way we want.

Ok, thanks to Karen's suggestion, I actually got this to work! Hooray! :D

Here's a patch that pulls in the hook_views_data() info from uc_views into here, and creates a local copy of it, per whitelancer's approach. This version gives you access to anything in the uc_order_products table (I'll try and get it working with stuff from uc_orders next, so you can filter by order status and stuff).

Of course, this also adds uc_views as a dependency of this module, which might not be desired. But given the choice between literally copy/pasting code from uc_views, vs. adding an extra dependency for those who may or may not need it for creating listings, I'd lean toward the latter.

Well. I got this far, anyway:

function uc_node_checkout_views_data() {
  // Pull in hook_views_data() from the uc_views module, which includes the
  // order_products table information. We need to alias this so that we can
  // do our own join to node via the uc_node_checkout_order_products table.
  $uc_views_data = uc_views_views_data();

  $data['ucnc_order_products'] = $uc_views_data['uc_order_products'];
  $data['ucnc_order_products']['table']['group'] = t('UC Node Checkout');
  $data['ucnc_orders'] = $uc_views_data['uc_orders'];
  $data['ucnc_orders']['table']['group'] = t('UC Node Checkout');

  $data['ucnc_order_products']['table']['join']['node'] = array(
    'table' => 'uc_order_products',
    'left_table' => 'uc_node_checkout_order_products',
      'left_field' => 'order_product_id',
      'field' => 'order_product_id',
      'type' => 'INNER',
  );
  $data['ucnc_orders']['table']['join'] = array(
    'ucnc_order_products' => array(
      'left_field' => 'order_id',
      'field' => 'order_id',
      'type' => 'INNER',
    ),
    'uc_node_checkout_order_products' => array(
      'left_table' => 'ucnc_order_products',
      'left_field' => 'order_product_id',
      'field' => 'order_product_id',
      'type' => 'INNER',
    ),
    'node' => array(
      'left_table' => 'uc_node_checkout_order_products',
      'left_field' => 'nid',
      'field' => 'nid',
      'type' => 'INNER',
    ),
  );

  // Definition for the uc_node_checkout_order_products bridge table.
  $data['uc_node_checkout_order_products']['table']['join'] = array(
    'node' => array(
      'left_field' => 'nid',
      'field' => 'nid',
      'type' => 'INNER',
    ),
    'ucnc_order_products' => array(
      'left_field' => 'order_product_id',
      'field' => 'order_product_id',
      'type' => INNER,
    ),
  );

  return $data;
}

However, this generates a query like:

SELECT node.nid AS nid,
   node.created AS node_created,
   node.title AS node_title,
   ucnc_order_products.price AS ucnc_order_products_price
 FROM node node 
 INNER JOIN uc_node_checkout_order_products uc_node_checkout_order_products ON node.nid = uc_node_checkout_order_products.nid
 INNER JOIN uc_order_products ucnc_order_products ON uc_node_checkout_order_products.order_product_id = ucnc_order_products.order_product_id
 INNER JOIN ucnc_orders ucnc_orders ON uc_node_checkout_order_products.nid = ucnc_orders.nid
 WHERE (node.type in ('registration')) AND (((ucnc_order_products.model) LIKE ('%DIWD10%') OR (ucnc_order_products.model) LIKE ('%DIWD11%') OR (ucnc_order_products.model) LIKE ('%DIWD12%') OR (ucnc_order_products.model) LIKE ('%DIWD13%'))) AND (ucnc_orders.order_status in ('completed'))
   ORDER BY node_created DESC

The problem is:

 INNER JOIN ucnc_orders ucnc_orders ON uc_node_checkout_order_products.nid = ucnc_orders.nid

Should be:

 INNER JOIN ucnc_orders ucnc_orders ON uc_node_checkout_order_products.order_id = ucnc_orders.order_id

Any Views experts in da house who can spot what is probably a lousy typo?

YAY!!! :D

Ok, total credit to merlinofchaos for this commit. Turns out, the other methods above were /way/ over-thinking this problem. All we need here is a relationship between the uc_node_checkout_order_products table and the uc_order_products table. Then, all you need to do to pull in product data or order data or whatever is to add those fields/filters to your view and require the relationship. DONE.

I know there might be some concerns about the UX of this approach (relationships are a somewhat complicated concept for people), but honestly, it beats the total pants off of duplicating all of uc_views_views_data() in here.

Relationships are confusing to setup the first time but they are also really important to learn if you want to build anything non-trivial. Thanks for your work on this webchick (and merlinofchaos).

I'm no longer using ucnc so I can't really test :( (we switched to http://drupal.org/project/uc_signup of course).

Excellent, I was wondering how relationships would play in here. With it working this way, is a uc_views dependency still required, or is Views smart enough to know not to permit incomplete relationships?

I tested it without UC Views, and it'll still let you add the relationship. But there are no errors or anything, and in fact it still gives me my same list of registrations as my initial report, minus fields like price and order completion status, which are of course broken. So if you for some reason wanted a list of something like only registration node titles, authors, and creation dates, this would actually work standalone.

So I'll remove the dependency, even though in 99% of cases you'll probably want both modules installed to make any kind of meaningful reports.

Also, I'm in this deep; might as well assign to myself. :P

I think I am interested in following this patch?.. but not certain?

I am looking to refer back upline from a checkout node to its parent to grab a cck field value per http://drupal.org/node/701440 but can't seem to figure it out.. Am I in the right place for a solution to my problem?

I think you are wanting to do what I am wanting to do. I wrote a new custom module called ucnc_document. Basically, it is rough, but I'll post it to another issue thread and you can possibly use the code.

This is great for getting order data from the node view, but how about getting node data when on the Ubercart Order view? I need to do it this way because I want to pull in affiliate order data too, which is only available from the Ubercart Order view, and not from the node view.

Can you suggest how I can adapt this so that I can use this patch to create a relationship on the Ubercart Order view type, returning the node ID of the node linked to the order?

Thanks

Matt

I noticed that the joins in this are 'INNER' which caused my view to lose all of the other registrations (which hadn't made it through the full shopping cart process). I set the join types to 'LEFT' and then it worked as expected.

It also then respected the "required" option in the relationship setting which did change one of the joins back to an 'INNER' which seems to be expected behavior.

Does this seem reasonable? One of my first forays into hand crafted relationships.

Also, there were missing quotes around one of the INNER's in #24.

_Erik

I got asked some questions on IRC about this today. Since this was 6 months ago, I no longer remember all the details, unfortunately. But here's an export of the view I made back then, in case it helps someone else sort of figure out how this was meant to work.

@webchick: I really appreciate the export of the view. With it I was able resolve my issues. Thank you... :)

What is the final patch to make this module work for Views? I tried the last but I can only add relationship but there is no filter related to this.

Is there any other option to get nodes connected to specific product?

Thanks for you help,
Iztok

Can someone please offer up a final wrap patch for this? It appears there are three different versions floating around. I tried to apply it and I ended up with multiple redeclarations of functions and unworkable code. Would love to test a final patch so we can commit this and make this module awesome. Thank you!

AFAIK there's only one viable patch, the most recent one (#28), which corrects a few issues from the earlier patch? Am I missing a cluestick?

The upload at #30 isn't a patch at all; it's a sample view that used the patch in this issue to create it.

Tried applying the patch and it failed on the second line, saying it was incomplete...tried applying it by hand, and nothing is blowing up, yet I still can't get my CCK fields from the uc_node_checkout form to display in a view with the order # from the UC order, so I'm not doing something right -- there's no linkage to the tables. If I include Order #, then the data returned originally by a query simply on the node checkout stuff goes away.

Bummer...was hoping someone might have some insight on this.

Hi, I woudl love to get this working, I need a views relationship from my checked out nodes back to their UC attributes. So basically when we have a views of all the various user created nodes (the attached nodes that are created using uc_node_checkout) we could also access the variables for the Product and Order they belong to. I am just wondering if anyone can point me to the right direction here. I'd really appreciate the help.

Also was this patch ever committed?

http://drupal.org/node/666716#comment-3425530 maybe somehow it will help

THanks a lot to webchick and eporama on this patches, eporama, I get an error when I try to applythis patch :

patch unexpectedly ends in middle of line
patch: **** malformed patch at line 71:  

I can't figure out what the problem is.. should I apply this after I apply the earlier patches or should I apply this right to the latest release?
also when would a patch like this make it's way to a release?

I am testing the patch by webchick and it works fine, I am using #21 since I like to have the dependency to uc_views there. It is working fine so far. thank you very much every one this was a lifesaver.

This is exactly what I needed for one of my projects; and almost what i need for another.

The additional thing I need is a relationship from an order product to the created node; the patch goes the other way.

I think I managed to add just that; my test views look good, but I only have an inkling about what the code does.

The patch is against 6.x-2.x-dev 2010-Nov-12 and includes the earlier patches.

@odalrick

I think your patch for uc_node_checkout.module failed. Hunk #1 FAILED at 970. uc_node_checkout.views.inc passed. Time permitting, I may investigate further. I'm still trying to dial in my needs.

jwant

I'm guessing you tried to patch against the recommended beta version rather than the dev version from 2010-Nov-12 .

If you really want I could give you a patch against that; but if you're prepared to patch anyway, I see no reason not to use the dev version.

Patch in #41 works great, against Dev branch, at least for associating nc nodes to their orders. Haven't tested associating orders with nodes yet.

Great! Thanks to everyone for putting your heads together, I have applied the patch in #41 to the latest -dev.

How can implement patches for last version 6.x-2.0-beta8 from 2011-May-07