Can't add a views relationship to the billing profile

I needed to retrieve the correct address information to search on in a view.

As a workaround I used hook_views_query_alter.

For this to work add the following relations to the view via the interface:
User
User -> Profile

use Drupal\views\ViewExecutable;
use Drupal\views\Plugin\views\query\QueryPluginBase;

/**
 * Implements hook_views_query_alter().
 */
function MY_MODULE_views_query_alter(ViewExecutable $view, QueryPluginBase $query) {
  switch ($view->id()) {
    case 'commerce_orders':
      // Make sure the correct billing profile information is retrieved.
      // Join to the commerce_order table instead of the user table for the profile id.
      $profile_table = $query->getTableInfo('profile_users_field_data');

      if (isset($profile_table['join'])) {
        $profile_table['join']->field = 'profile_id';
        $profile_table['join']->leftTable = 'commerce_order';
        $profile_table['join']->leftField = 'billing_profile__target_id';
      }
      break;
  }
}

Probably can be done cleaner/better, but this worked for me.

Thanks mike.stijlloos, this fix came at just the right time and is working for us.

Thanks, #2 works for us as well. It really came just 1h ahead of me searching for the solution :)

This is actually an "Entity Reference Revisions" issue
#2799479-34: Views doesn't recognize relationship to host

I updated the previous patch to include the relationship for base fields - the commerce profile fields case.

So please follow the related issue and maybe you could help having a solution here (and there).

Hi vasike,

thanks your patch https://www.drupal.org/node/2799479#comment-12080741 solved this issue for me.
Now I can add the billing profile relation to my view and access all separate billing profile fields in my view fields.

Great!

Update to #6

I was able to create a views relationship from order to profile and add anonymous customer profiles address fields using patch #91 2799479-91-DO_NOT_COMMIT.patch found here:

https://www.drupal.org/project/entity_reference_revisions/issues/2799479...

Patch committed against:
core 8.8.1
entity_reference_revisions 8.x-1.7

Patches #108 did NOT work and caused Broken/Missing handlers and broke several views.

I was creating views for advanced order search , where I needed to add filters by billing and shipping profile.

I used hook_views_data_alter and add relationship between the billing profile and order (Order->Billing Profile) and shipping profile with shipment (Order->Shipment->Shipping Profile (need to use aggregation)). For me it work perfect.

Here is my code:

function MY_MODULE_views_data_alter(&$data) {

  $data['commerce_order']['billing_profile'] = [
    'title' => t('Billing Profile'),
    'help' => t('Reference to the billing profile of a commerce order.'),
    'relationship' => [
      'group' => 'Order',
      // Views name of the table being joined to from commerce_order.
      'base' => 'profile',
      // Database field name in profile for the join.
      'base field' => 'profile_id',
      // Real database field name in commerce_order for the join, to override
      // 'unique_dummy_name'.
      'field' => 'billing_profile__target_id',
      // ID of relationship handler plugin to use.
      'id' => 'standard',
      'label' => t('Billing Profile'),
    ],
  ];

  $data['commerce_shipment']['shipping_profile'] = [
    'title' => t('Shipping Profile'),
    'help' => t('Reference to the shipping profile of a commerce shipment.'),
    'relationship' => [
      'group' => 'Shipment',
      // Views name of the table being joined to from commerce_shipment.
      'base' => 'profile',
      // Database field name in profile for the join.
      'base field' => 'profile_id',
      // Real database field name in commerce_shipment for the join, to override
      // 'unique_dummy_name'.
      'field' => 'shipping_profile__target_id',
      // ID of relationship handler plugin to use.
      'id' => 'standard',
      'label' => t('Shipping Profile'),
    ],
  ];

}

@Warlord Rudy your hook in #8 works great. Thank you!

#8 works great. Thanks!

Can you please explain where I put this code? (I'm not a developer.) How do I connect this code to my custom view? Thanks!

@markdc the code in #8 goes into a custom module. You need to create a custom module and paste the code for the hook in your module's .module file. It might seem daunting but it's worthwhile to keep at it until you are comfortable with the general principles of hooks.

You might check out a tutorial, e.g., an explanation of hooks:

https://drupalize.me/tutorial/what-are-hooks?p=2766

And an overview of creating custom modules:

https://drupalize.me/tutorial/create-info-file-module?p=2766

#8 worked great, thanks!

#8 worked like a charm. Should be included in the contrib module.

Code in #8 worked for me too! Thanks! And yes, maybe it is time to add it to contrib module? But one part in commerce module and another in commerce shipment one. @Artem Rudnitskiy maybe you can create an MR or provide a patch?

It works for me too. Adding #8 as MR to order submodule, and patch for composer purposes.

Committed, thanks everyone!

Just FYI... Moved the commerce_shipment part to commerce_shipping as I don't think this belongs to commerce core. Unfortunately realized that right after tagging a commerce_shipping release, so it'll go into the next commerce_shipping release.

I am trying to use the custom module in #8 to allow my list of orders to be searchable based on the first or last name of the billing or shipping profile, but I am not seeing any way to add such a filter in my View. Can someone please direct me on where I can add that?