Documentation Content

#Introduction

This page explains how the mapping-extension is able to map references to other Shopware entities.

This page provides a general overview and background information that you need in order to understand the mapping of foreign key relations. The step-by-step guide provides several in-depth examples on how to set up the mapping-extension for references.

#Field Configuration

To configure a foreign key, use the field configuration foreignKeyField:

{
    "foreignKeyFieldConfig": {
        "sourcePath": "...",
        "targetFieldName": "...",
        "allowsNullValues": ...,
        "references": {
            "table": "referenced_table_name",
            "field": "referenced_column_name"
        },
        "filterSearchField": "..."
    }
}  

• references.table contains the name of the referenced table
• references.field contains the name of the referenced column (the name of the column your foreign key column is referencing). The default value is id.
• filterSearchField is the name of the field that must be equal to the value of your source path in order to generate a reference

The configuration of foreign keys differs slightly depending on the type of foreign key. The module makes a distinction between so-called "internal" and "external" foreign keys. The difference is explained in the sections below.

#Internal Foreign Keys

Internal foreign keys are references between documents that are both mapped by the mapping extension. This is the case if a Document A references another Document B and both documents are mapped to Shopware by the help of the mapping-extension.

#Tables

Let's take a look at an example and assume that a plugin added two tables to Shopware:

create table example_document_table
(
    id                     binary(16) not null primary key,
    referenced_document_id binary(16) not null,
    constraint `...` foreign key (referenced_document_id) references referenced_document_table (id)
);

create table referenced_document_table
(
    id binary(16) not null primary key
);  

#Documents

The corresponding documents that the mapping-extension uses to populate those tables would look like this:

/** 
* 
* Example document that references another document mapped by the extension as well. Mapped to 'example_document_table'.
* 
* @MongoDB\Document 
*/  
class ExampleDocument extends BaseDocument  
{  
    /** @MongoDB\ReferenceOne(targetDocument=ReferencedDocument::class, cascade="persist") */    
    protected ReferencedDocument $someReferencedDocument = null;  
}  
  
/** 
* Referenced document. This document is mapped to the table `referenced_document_table`.
*
* @MongoDB\Document 
*/  
class ReferencedDocument extends BaseDocument  
{  
    // ...
}  

#Configuration

This will lead to the following configuration for the internal foreign key:

{
    "sourcePath": "someReferencedDocument",
    "targetFieldName": "referenced_document_id",
    "foreignKeyFieldConfig": {
        "allowsNullValues": false,
        "references": {
            "table": "referenced_document_table"
        }
    }
}  

As you can see setting up an internal foreign key is pretty straight forward. All you have to do is to let the sourcePath point to the referenced document and add the target fields and tables to the configuration.

#Source Path

The sourcePath must point to the referenced document that is also transferred by the mapping extension. Do not use any other field of the document (e.g. "someReferencedDocument.id" or "someReferencedDocument.identifier", the only valid source path would be "someReferencedDocument").

The referenced UUID (the specific id of table_b that an entry of table_a references) is calculated automatically from the referenced Document B (either by its ObjectId or Synqup-Identifier, depending on the configured mode).

#Target Field Name

As in every other field type the targetFieldName contains the name of the column where the source value will be written to. But instead of the source value itself the value of the referenced column of the corresponding Shopware entity will be written into this field.

#Referenced Table

The field foreignKeyFieldConfig.references.table contains the name of the referenced Shopware table. For internal references this must be the name of the table that belongs to the referenced document.

#Referenced Field

Internal foreign keys always have to reference the UUID of the other Shopware table. So the extension enforces the value id for the field referencedField (other values than ìd will lead to errors). Therefore, you can simply omit the field in the configuration.

The field foreignKeyFieldConfig.references.field contains the name of the referenced column (the name of the column your foreign key column is referencing). The default value is id.

#Order of Object Mappings

Since object mappings are handled in sequential order it is very important to add the object-mapping of referenced entities before the object-mapping of the referencing entity.

#External Foreign Keys

External foreign keys are references to documents or Shopware entities that are not mapped by the mapping-extension. This can either be a reference to a document that is mapped by the main module (e.g. a Product or Customer) or a reference to a default Shopware entity by a specific/single value.

In difference to internal foreign keys it is not possible to calculate the uuid of the referenced entity without searching it in Shopware. So you need to have a unique value that is present both in your document and the target/referenced entity in Shopware. This value is then used to search the referenced entity/id in Shopware.

#Configuration

Let's take a look on the configuration:

{
    "sourcePath": "...",
    "targetFieldName": "...",
    "foreignKeyFieldConfig": {
        "allowsNullValues": false,
        "references": {
            "table": "referenced_table_name",
            "field": "referenced_column_name"
        },
        "filterSearchField": "..."
    }
}  

#Source Path

The sourcePath must point to a specific/single scalar value that corresponds to a value of an entity in Shopware. This must be a unique value that is present both in your document and the target table in Shopware.

Valid examples: locale or customer.customerInformation.email.

#Referenced Field

The field foreignKeyFieldConfig.references.field contains the name of the referenced column (the name of the column your foreign key column is referencing). The default value is id (you probably never have to change this since foreign keys in Shopware are usually implemented with ids).

#Referenced Table

The field foreignKeyFieldConfig.references.table contains the name of the referenced Shopware table.

#Target Field Name

targetFieldName is the foreign key column in your table. The referenced value of the referenced entity will be written into this column.

An example with a simple mysql query helps to understand this. The value written in your target field is equal to the mysql query SELECT {{references.field}} FROM {{references.table}} WHERE {{filterSearchField}} = {{value @ sourcePath}}.

#Filter Search Field

This is the name of the field that must be equal to the value of your source path in order to generate a reference to the target entity. If you take a look at the next section ("Search Requests") the use of this field becomes way easier to understand.

#Using Filter Paths

You can search external referenced entities with associations. But please note that this must be a valid filter path in Shopware. There are some limitations to using filter paths instead of fields:

• The path must be a valid association in Shopware. Please test the request you want to generate (e.g. in Postman) to avoid FRAMEWORK_ASSOCIATION_NOT_FOUND errors.
• Only single layered associations are supported, the path one.two.three would be invalid.
• Although it is not an association, you can also search for values of customFields (e.g. customFields.identifier).

You can find examples on how to use filter paths instead of fields in the examples below.

#Search Requests

The module will generate a search request from your configuration in order to find the referenced entity.

The value written in your column specified in targetFieldName is equal to the result of the following search request:

{{HOST}}/api/search/{{references.table}}/

{
    "filter": [
        {
            "type": "equals",
            "field": "{{filterSearchField}}",
            "value": "{{value @ sourcePath}}"
        }
    ],
    "associations": {
        "{{extracted-from-'filterSearchField'}}": {}
    }
}  

Or in other words: The value written in your target field is equal to the mysql query SELECT {{references.field}} FROM {{references.table}} WHERE {{filterSearchField}} = {{value @ sourcePath}}.

#Example 1 - Referencing External Documents

This is an example for an external foreign key with a document mapped by the main module. In this scenario a document should reference a country from Shopware (table country).

#Target Table

create table example_document_table
(
    id         binary(16) not null primary key,
    country_id binary(16) null,
    constraint `...` foreign key (country_id) references country (id)
); 

#Source Document

/** @MongoDB\Document */  
class ExampleDocument extends BaseDocument  
{  
    /** @MongoDB\ReferenceOne(targetDocument=Country::class) */    
    protected Country $country = null;  
}  

#Configuration

{
    "sourcePath": "country.iso3",
    "targetFieldName": "country_id",
    "foreignKeyFieldConfig": {
        "allowsNullValues": false,
        "references": {
            "table": "country",
            "field": "id"
        },
        "filterSearchField": "iso3"
    }
}  

Read the configuration as follows: The country (table country) whose value at the field iso3 corresponds to the source value at country.iso3 is going to be referenced. So the mapping-extension generates a search request to find the matching country:

{{HOST}}/api/search/country/

{
    "filter": [
        {
            "type": "equals",
            "field": "iso3",
            "value": "{{value @ country.iso3}}"
        }
    ]
}  

The id of the resulting entity is written into the field specified by targetFieldName.

#Example 2 - Referencing External Shopware Entities

The following example configures an external foreign key to the Shopware entity "language".

#Target Table

create table example_document_table
(
    id            binary(16) not null primary key,
    `language_id` BINARY(16) NOT NULL,
    CONSTRAINT `...` FOREIGN KEY (`language_id`) REFERENCES `language` (`id`) ON ...
);    

#Document

class ExampleDocument extends BaseDocument  
{  
    
    /**  
     * Example for an external reference.
     * 
     * @var string  
     * @MongoDB\Field(type="string")  
     */    
     private string $language;  
  
    // construct, getters, setters, ...  
}

Our document has the value en-GB as language:

$document = new ExampleDocument(...);  
$document->setLanguage('en-GB');

#Search Request

So we want to reference the language in Shopware whose locale code is equal to en-GB. You could read the language with the following api request. Note that there is no locale field in the table language. Instead, locales are stored in a separate table. If we want to find the language whose locale code is equal to en-GB we have to use associations.

{{HOST}}/api/search/language/

{  
  "filter": [  
	 {      
	     "type": "equals",  
	     "field": "locale.code",  
	     "value": "de-DE"  
     }
   ],
   "associations": {
	   "locale": {}
   }
}  

This request returns the language whose locale is equal to en-GB.

#Configuration

If you want the mapping-extension to recreate this request in order to set the uuid of that language to your entity you have to use the following configuration:

{
    "targetFieldName": "language_id",
    "sourcePath": "language",
    "foreignKeyFieldConfig": {
        "references": {
            "table": "language"
        },
        "allowsNullValues": false,
        "filterSearchField": "locale.code"
    }
}

#Many-To-Many Associations

The mapping extension is only able to support one-to-many references. Many-to-many references are not populated automatically. Every Shopware table to be populated by the mapping-extension must exist as a document, even M-N-tables / junction-tables. So in order to populate M-N-references you have to create and configure a matching document for your junction table. There is a very detailed example available in the step-by-step guide.

#Reference Loading Modes

The mapping extension provides two "reference loading modes" (on-demand and bulk). This determines the way the module searches for referenced Shopware entities. This can be set by configuring the field referenceLoadingMode. The default value is bulk. This value can be set individually for any object mapping.

Quick-Reference

The reference loading mode on-demand searches a Shopware entity as soon as a reference is found. Produces a lot of requests. The reference loading mode bulk collects all referenced values and searches them in a bulk search request. Produces way less requests at the cost of two more subsections to go throug.

#On-Demand

The on-demand mode is pretty straight forward: Search a single corresponding Shopware entity when needed, so referenced Shopware entities are searched and cached "on demand". This is done by generating a search request for every referenced entity.

There are 0-1 search requests for every referenced entity. A search request is not necessary if the referenced entity is an internal reference to a document that was mapped on the same flow execution.

#Bulk

This mode is more complex than on-demand. Basically referenced shopware values are extracted from their source objects which allows the extension to search multiple referenced entities in a single search request.

This would lead to a search requests like this (the only difference to the examples from above is the usage of an equalsAny-filter instead of equals):

{
    "filter": [
        {
            "type": "equalsAny",
            "field": "{{references.field}}",
            "value": [
                "1",
                "2",
                "3",
                "4",
                "..."
            ]
        }
    ]
}  

To be able to search several referenced entities at once the module needs to extract those referenced entities beforehand. This is done in the subsection relation-extract. The entities are searched in the subsection relation-cache. Those subsections are only added to the subsection tree of an object-mapping in which the bulk mode is present.

#Which Mode should I use?

This actually depends on the use case. The pros and cons of each mode are listed below.

#Bulk

• Fast: Less amount of search requests necessary to find referenced entities. Example: If you choose a batch size for relation-cache of 100 the number of requests is reduced by a factor of (up to) 100.
• Slow: Extraction of referenced shopware values is necessary. This can be slow in very huge data sets.

#On-Demand

• Fast: Extraction of referenced entities is skipped entirely. Saves a lot of time in very huge data sets.
• Slow: One search request for every referenced entity is necessary. This can be slow if a lot of different entities are referenced.

#Recommendation

The default mode is bulk and in most cases you will be fine using this. It is actually very simple: If you have the feeling that one of these modes is too slow try the other one.

Basically the reference loading mode highly influences the amount of search requests generated by the mapping-extension. So you have to decide whether the extra effort of extracting the referenced values from the documents to reduce the number of requests is worth it or not.

There are cases that showed that there is a turning point from which it is faster to skip the extraction of references and just search entities when needed. Let's take a look at examples:

• Case 1: 4.000 documents with references to several hundred Shopware entities. In this case it is very fast to extract all referenced values. Only a few requests are necessary. The bulk-search is faster than sending several hundred search requests on demand.
• Case 2: 20.000.000 documents that contain references to ~300 Shopware entities. The process of extracting all referenced shopware values took ~3 hours. In this case it was way faster to skip the extraction and just search entities on demand.