Skip to content
Tutorials Center
My learning

Topics

Lesson 9 of 30 ยท Data Abstraction Layer

Reading and Writing with Repositories

Read and write data in Shopware with the DAL: Criteria filters, sorting, limits, associations, aggregations, search vs searchIds, upsert, delete and Context.

Once an entity has a definition and a table, you work with it through a repository. You never write SELECT statements for entities. Instead you describe what you want in a Criteria object, hand it to the repository, and get typed entities back. The same Criteria can be sent as JSON to the Admin API, so what you learn here applies in both places.

This lesson covers building a Criteria (filters, sorting, paging, associations, aggregations), the difference between search and searchIds, the write methods (create, update, upsert, delete), and the Context that travels with every call. Shopware cannot run here, so the real code is read-only. Two runnable PHP stand-ins turn a small Criteria object into SQL on SQLite, so you can see what a description turns into.

Getting a repository

Every registered entity gets a repository service named <entity>.repository. You inject it into your own service in services.xml:

XML
<service id="Acme\Vendors\Service\VendorLister">
    <argument type="service" id="acme_vendor.repository"/>
</service>

The class receives an EntityRepository:

PHP
<?php declare(strict_types=1);

namespace Acme\Vendors\Service;

use Shopware\Core\Framework\Context;
use Shopware\Core\Framework\DataAbstractionLayer\EntityRepository;
use Shopware\Core\Framework\DataAbstractionLayer\Search\Criteria;
use Shopware\Core\Framework\DataAbstractionLayer\Search\Filter\EqualsFilter;
use Shopware\Core\Framework\DataAbstractionLayer\Search\Sorting\FieldSorting;

class VendorLister
{
    public function __construct(private readonly EntityRepository $vendorRepository)
    {
    }

    public function activeVendors(Context $context): array
    {
        $criteria = new Criteria();
        $criteria->addFilter(new EqualsFilter('active', true));
        $criteria->addSorting(new FieldSorting('name', FieldSorting::ASCENDING));
        $criteria->setLimit(25);

        return $this->vendorRepository->search($criteria, $context)->getEntities()->getElements();
    }
}

The Context argument says who is acting, in which language, with which currency and in which version of the data. In an Administration or console context you typically use Context::createDefaultContext(). In a storefront request, the framework gives you a SalesChannelContext, and its getContext() returns a Context. Never invent a context in code that serves a customer: it skips the language and rule settings of that request.

Building a Criteria

Criteria is a plain, serializable description. The most common building blocks:

Need Code
Exact match new EqualsFilter('active', true)
Match one of many new EqualsAnyFilter('id', $ids)
Text contained new ContainsFilter('name', 'harbor')
Number or date range new RangeFilter('rating', [RangeFilter::GTE => 4])
Negate new NotFilter(NotFilter::CONNECTION_AND, [ ... ])
Combine new MultiFilter(MultiFilter::CONNECTION_OR, [ ... ])
Sorting new FieldSorting('rating', FieldSorting::DESCENDING)
Paging setLimit(25), setOffset(50)
Fetch by ids new Criteria($ids)

Several filters added with addFilter() are combined with AND. For OR, wrap them in a MultiFilter. Field names are property names from your definition, not column names, and associated fields use dot notation: manufacturer.name.

PHP
$criteria = new Criteria();
$criteria->addFilter(
    new MultiFilter(MultiFilter::CONNECTION_OR, [
        new EqualsFilter('active', true),
        new RangeFilter('rating', [RangeFilter::GTE => 4]),
    ])
);
$criteria->addSorting(new FieldSorting('rating', FieldSorting::DESCENDING));
$criteria->setLimit(10);
$criteria->setOffset(20);
$criteria->setTotalCountMode(Criteria::TOTAL_COUNT_MODE_EXACT);

By default the result does not count all matching rows beyond the limit. If you page through results and need the total, set the total count mode. It costs an extra query, so use it only when you show page numbers.

Associations

By default an entity comes back without its relations. Ask for them with addAssociation():

PHP
$criteria->addAssociation('products');
$criteria->getAssociation('products')->setLimit(10);

getAssociation() returns a nested Criteria that can have its own filters, sorting and limit. Load only what you need. An association that is added without a limit can load thousands of rows on a big shop, and nested associations multiply that cost. We return to this in Security and Performance.

Aggregations

An aggregation computes values over the whole matching set, not only the page returned. Typical classes are SumAggregation, AvgAggregation, CountAggregation, MinAggregation, MaxAggregation and TermsAggregation (group by a field):

PHP
$criteria->addAggregation(new AvgAggregation('avg-rating', 'rating'));
$criteria->addAggregation(new TermsAggregation('by-active', 'active'));

$result = $repository->search($criteria, $context);
$avg = $result->getAggregations()->get('avg-rating');

The first argument is a name you choose; you use it to find the result. The result type depends on the aggregation.

search or searchIds?

The repository offers two read methods with the same input:

  • search(Criteria, Context): EntitySearchResult returns hydrated entities, with total, aggregations and association data.
  • searchIds(Criteria, Context): IdSearchResult returns only the matching ids.

Hydrating entities is the expensive part of a read. If you just need to know whether anything matches, or you want to hand ids to another system, use searchIds. A common pattern is to find the ids first, then load full entities for a page of them.

PHP
$ids = $this->vendorRepository->searchIds($criteria, $context)->getIds();
$exists = $this->vendorRepository->searchIds($criteria->setLimit(1), $context)->getTotal() > 0;

For the second line, remember the note about total count mode: it only counts correctly when the criteria asks for it.

flowchart LR
    C["Criteria<br/>filters, sorting, paging"] --> R["EntityRepository"]
    X["Context<br/>language, currency, version"] --> R
    R -->|"search()"| E["EntitySearchResult<br/>entities + total + aggregations"]
    R -->|"searchIds()"| I["IdSearchResult<br/>ids only"]

One description, two kinds of answer. The context rides along with every call.

Writing data

The write methods take arrays of payloads, one array per row, keyed by property names:

PHP
// insert: fails if the id already exists
$this->vendorRepository->create([
    ['id' => Uuid::randomHex(), 'name' => 'Blue Harbor', 'rating' => 5, 'active' => true],
], $context);

// update: the id must exist, only the given fields change
$this->vendorRepository->update([
    ['id' => $id, 'rating' => 4],
], $context);

// upsert: insert when the id is unknown, update when it exists
$this->vendorRepository->upsert([
    ['id' => $id, 'name' => 'Blue Harbor', 'rating' => 3],
], $context);

// delete: only the primary key is needed
$this->vendorRepository->delete([['id' => $id]], $context);

Notes that save time:

  • Ids are hex strings in PHP. Create them with Shopware\Core\Framework\Uuid\Uuid::randomHex(). The DAL converts them to BINARY(16) for MySQL.
  • Write many rows in one call. One call with 500 payloads is much faster than 500 calls with one, since the DAL batches the SQL.
  • Required fields are checked. A missing Required field fails the whole write with a validation error naming the field path.
  • A write returns an event container, EntityWrittenContainerEvent, listing what was written. The DAL also dispatches events after the write, which the next chapter uses.
  • Prefer upsert for syncing. When you import from another system, an upsert keyed on a stable id is safe to repeat, which is the idempotency idea explained in Idempotency.

Writes through the DAL also record created_at and updated_at for you.

Try it: Criteria to SQL

The real DAL builds SQL with a query builder that handles translations, inheritance and versions. This stand-in keeps only the essence: filter objects, sorting, paging, and a builder that turns them into one parameterized SQL string. Real Shopware also never concatenates user values into SQL, and neither does this builder.

PHP A tiny Criteria that builds SQL

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

The real Criteria is just as declarative. What is stand-in: the SQL generation (the real one is far larger), the field names (real ones are property names mapped to columns), and the lack of translations and associations.

Try it: aggregations and upsert

Aggregations and upserts are easiest to understand as SQL. An aggregation runs over the whole filtered set, ignoring the page limit. An upsert can be a single statement keyed on the primary key, which is what makes repeated syncs safe.

PHP Aggregations and upsert on SQLite

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

Real upsert() goes through the full write pipeline (validation, events, translations), so it is heavier than one SQL statement. The shape is the same: the primary key decides between insert and update.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which call returns only matching ids and avoids hydrating entities?

    searchIds() takes the same Criteria but returns an IdSearchResult. It is cheaper when you do not need the entity data.
  2. 2. Several filters are added with addFilter(). How are they combined?

    Separate filters are combined with AND. Use a MultiFilter with MultiFilter::CONNECTION_OR for alternatives.
  3. 3. What is the difference between update() and upsert()?

    upsert is the safe choice for syncing data, because running it twice gives the same result.
  4. 4. Why should an association usually get a limit?

    Nested associations multiply the amount of loaded data. Load only what the caller needs.
  5. 5. Which value do you pass to every repository call to say who is acting and in which language?

    The Context carries language, currency, version and source information for the call.

Summary

  • Use the <entity>.repository service with a Criteria and a Context instead of writing SQL.
  • Criteria describes filters, sorting, limit/offset, associations and aggregations; field names are property names, with dots for associations.
  • search() returns entities, searchIds() returns only ids and is cheaper.
  • create, update, upsert and delete take arrays of payloads; batch many rows into one call.
  • Use upsert with stable ids for repeatable syncs, and limit associations to what you need.

Next, extend entities you do not own in Entity Extensions and Custom Fields.