Interacting with the database

Active records and magic getters

Note

Watch a short video tutorial on YouTube: Active Records.

The OXID eShop architecture is based on an MVC design pattern.

To implement models, the Active Record pattern is used.

In general, each model class is linked to a database table.

For example, the Article model is linked to the oxarticles table, Order to the oxorders table etc.

All models are stored in the Application/Models directory.

Let’s take one of them, for example the Article model, and try to fetch the product (with the ID demoId) data from database:

$product = oxNew(\OxidEsales\Eshop\Application\Model\Article::class); // creating model's object
$product->load( 'demoId' ); // loading data
//getting some information
echo $product->oxarticles__oxtitle->value;
echo $product->oxarticles__oxshortdesc->value;

Magic getters are used to get models attributes; they are constructed in this approach:

$model->tablename__columnname->value;
'tablename' is the name of the database table where the model data is stored
'columnname' is the name of the column of this table containing the data you want to fetch

To set data to a model and store it, database magic setters (with the same approach as magic getters) are used:

$product = oxNew(\OxidEsales\Eshop\Application\Model\Article::class);
$product->oxarticles__oxtitle = new \OxidEsales\Eshop\Core\Field ( 'productTitle' );
$product->oxarticles__oxshortdesc = new \OxidEsales\Eshop\Core\Field( 'shortdescription' );
$product->save();

In this example the new record will be inserted into the table. To update information, we load the model, set the new data and call the save() method:

$product = oxNew(\OxidEsales\Eshop\Application\Model\Article::class);
$product->load( 'demoId' );
$product->oxarticles__oxtitle = new \OxidEsales\Eshop\Core\Field ( 'productTitle' );
$product->oxarticles__oxshortdesc = new \OxidEsales\Eshop\Core\Field( 'shortdescription' );
$product->save();

There are other ways to do the same - without loading the data - just by simply setting the ID with the setId()-method:

$product = oxNew(\OxidEsales\Eshop\Application\Model\Article::class);
$product->setId( 'demoId' );
$product->oxarticles__oxtitle = new \OxidEsales\Eshop\Core\Field( 'productTitle' );
$product->oxarticles__oxshortdesc = new \OxidEsales\Eshop\Core\Field( 'shortdescription' );
$product->save();

In this example there is a check to determine if this ID exists and if so, the record in the database will be updated with the new record.

Making a query

Note

Watch a short video tutorial on YouTube: SQL Statements with the Query Builder.

To execute a query, an instance of QueryBuilderFactoryInterface is required to create the Query Builder.

Since it is defined as a service, you can just inject it into your class:

public function __constructor(QueryBuilderFactoryInterface $queryBuilderFactory)
{
    $this->queryBuilderFactory = $queryBuilderFactory;
}

or access it with Service Locator if Dependency Injection is not possible:

$queryBuilderFactory = ContainerFacade::get(QueryBuilderFactoryInterface::class);

At this point the database connection is ready and the create method must be called to create a queryBuilder.

$queryBuilder = $queryBuilderFactory->create();

Now all types of SQL queries can be generated, based on the Doctrine DBAL Documentation.

Sample:

$queryBuilder
      ->select('*')
      ->from('oxtplblocks')
      ->where('oxshopid = :shopId')
      ->andWhere('oxblockname = :name')
      ->setParameters([
          'shopId'    => $shopId,
          'name'      => $name,
      ]);

$blocksData = $queryBuilder->execute();
$blocksData = $blocksData->fetchAllAssociative();

Note

The application’s data access layer should always be accessed through the DBAL. The use of direct SQL queries is considered a bad practice and should be avoided.