CodeExplorer
You are a developer and looking for Shopware projects?
Apply Now!
orWhere example
'supplier.description as description',
$builder->expr()->count('articles.id') . ' as articleCounter',
]);
$builder->from(Supplier::class, 'supplier');
$builder->leftJoin('supplier.articles', 'articles');
$builder->groupBy('supplier.id');
if (\is_array($filter) && \array_key_exists(0, $filter) && ($filter[0]['property'] === 'name')) {
// filter the displayed columns with the passed filter $builder
->where('supplier.name LIKE ?1') // Search only the beginning of the customer number. ->orWhere('supplier.description LIKE ?1'); // Full text search for the first name of the customer
// set the filter parameter for the different columns. $builder->setParameter(1, '%' . $filter[0]['value'] . '%');
}
$builder->addOrderBy($orderBy);
return $builder;
}
/** * Returns an instance of the \Doctrine\ORM\Query object which select a list of article votes. * * @param string|null $filter * @param int|null $offset * @param int|null $limit * @param array|null $order * * @return Query<Vote> */
$builder->expr()->count('articles.id') . ' as articleCounter',
]);
$builder->from(Supplier::class, 'supplier');
$builder->leftJoin('supplier.articles', 'articles');
$builder->groupBy('supplier.id');
if (\is_array($filter) && \array_key_exists(0, $filter) && ($filter[0]['property'] === 'name')) {
// filter the displayed columns with the passed filter $builder
->where('supplier.name LIKE ?1') // Search only the beginning of the customer number. ->orWhere('supplier.description LIKE ?1'); // Full text search for the first name of the customer
// set the filter parameter for the different columns. $builder->setParameter(1, '%' . $filter[0]['value'] . '%');
}
$builder->addOrderBy($orderBy);
return $builder;
}
/** * Returns an instance of the \Doctrine\ORM\Query object which select a list of article votes. * * @param string|null $filter * @param int|null $offset * @param int|null $limit * @param array|null $order * * @return Query<Vote> */
'log.date as date',
'log.user as user',
'log.ipAddress as ip_address',
'log.userAgent as user_agent',
'log.value4 as value4'
)->from(Log::class, 'log');
if ($filter = $this->Request()->get('filter')) {
$filter = $filter[0];
$builder->where('log.user LIKE ?1')
->orWhere('log.text LIKE ?1')
->orWhere('log.date LIKE ?1')
->orWhere('log.ipAddress LIKE ?1')
->orWhere('log.key LIKE ?1')
->orWhere('log.type LIKE ?1');
$builder->setParameter(1, '%' . $filter['value'] . '%');
}
$builder->addOrderBy($order);
$builder->setFirstResult($start)->setMaxResults($limit);
'log.user as user',
'log.ipAddress as ip_address',
'log.userAgent as user_agent',
'log.value4 as value4'
)->from(Log::class, 'log');
if ($filter = $this->Request()->get('filter')) {
$filter = $filter[0];
$builder->where('log.user LIKE ?1')
->orWhere('log.text LIKE ?1')
->orWhere('log.date LIKE ?1')
->orWhere('log.ipAddress LIKE ?1')
->orWhere('log.key LIKE ?1')
->orWhere('log.type LIKE ?1');
$builder->setParameter(1, '%' . $filter['value'] . '%');
}
$builder->addOrderBy($order);
$builder->setFirstResult($start)->setMaxResults($limit);
if (empty($ids)) {
return [];
}
$query = $this->connection->createQueryBuilder();
$query->select('category.id');
$query->from('category');
foreach ($ids as $id) {
$key = 'id' . $id;
$query->orWhere('category.type != :type AND category.path LIKE :' . $key);
$query->setParameter($key, '%' . $id . '%');
}
$query->setParameter('type', CategoryDefinition::TYPE_LINK);
$children = $query->executeQuery()->fetchFirstColumn();
if (!$children) {
return [];
}
return [];
}
$query = $this->connection->createQueryBuilder();
$query->select('category.id');
$query->from('category');
foreach ($ids as $id) {
$key = 'id' . $id;
$query->orWhere('category.type != :type AND category.path LIKE :' . $key);
$query->setParameter($key, '%' . $id . '%');
}
$query->setParameter('type', CategoryDefinition::TYPE_LINK);
$children = $query->executeQuery()->fetchFirstColumn();
if (!$children) {
return [];
}
if (\is_string($templateId)) {
return $templateId;
}
return null;
}
private function deleteRowsByReferencedId(string $associatedId, string $table, string $associationField): void
{
$query = $this->connection->createQueryBuilder()
->delete($table)
->orWhere(\sprintf('%s = :id', $associationField))
->setParameter('id', $associatedId, ParameterType::BINARY)
->executeStatement();
}
private function changeDefaultLanguageToDutch(): void
{
$languageId = $this->createNewLanguageEntry('nl-NL');
$this->swapDefaultLanguageId($languageId);
$this->connection->executeStatement('SET FOREIGN_KEY_CHECKS = 0;');
$this->connection->executeStatement('DELETE FROM language WHERE id != UNHEX(?)', [Defaults::LANGUAGE_SYSTEM]);
$this->connection->executeStatement('SET FOREIGN_KEY_CHECKS = 1;');
}
return $templateId;
}
return null;
}
private function deleteRowsByReferencedId(string $associatedId, string $table, string $associationField): void
{
$query = $this->connection->createQueryBuilder()
->delete($table)
->orWhere(\sprintf('%s = :id', $associationField))
->setParameter('id', $associatedId, ParameterType::BINARY)
->executeStatement();
}
private function changeDefaultLanguageToDutch(): void
{
$languageId = $this->createNewLanguageEntry('nl-NL');
$this->swapDefaultLanguageId($languageId);
$this->connection->executeStatement('SET FOREIGN_KEY_CHECKS = 0;');
$this->connection->executeStatement('DELETE FROM language WHERE id != UNHEX(?)', [Defaults::LANGUAGE_SYSTEM]);
$this->connection->executeStatement('SET FOREIGN_KEY_CHECKS = 1;');
}
|| (isset($payload['footer_category_id']) && $payload['footer_category_id'] === $categoryId)
|| (isset($payload['service_category_id']) && $payload['service_category_id'] === $categoryId)
) {
return false;
}
}
$result = $this->connection->createQueryBuilder()
->select('id')
->from(SalesChannelDefinition::ENTITY_NAME)
->where('navigation_category_id = :navigation_id')
->orWhere('footer_category_id = :footer_id')
->orWhere('service_category_id = :service_id')
->setParameter('navigation_id', $categoryId)
->setParameter('footer_id', $categoryId)
->setParameter('service_id', $categoryId)
->setMaxResults(1)
->executeQuery();
return !$result->fetchOne();
}
}
|| (isset($payload['service_category_id']) && $payload['service_category_id'] === $categoryId)
) {
return false;
}
}
$result = $this->connection->createQueryBuilder()
->select('id')
->from(SalesChannelDefinition::ENTITY_NAME)
->where('navigation_category_id = :navigation_id')
->orWhere('footer_category_id = :footer_id')
->orWhere('service_category_id = :service_id')
->setParameter('navigation_id', $categoryId)
->setParameter('footer_id', $categoryId)
->setParameter('service_id', $categoryId)
->setMaxResults(1)
->executeQuery();
return !$result->fetchOne();
}
}
public function getListQueryBuilder($filter = null, $orderBy = null)
{
/** @var QueryBuilder $builder */
$builder = $this->getEntityManager()->createQueryBuilder();
$builder->select(['emotions', 'categories'])
->from(Emotion::class, 'emotions')
->leftJoin('emotions.categories', 'categories');
// Filter the displayed columns with the passed filter string if (!empty($filter)) {
$builder->where('categories.name LIKE ?2')
->orWhere('emotions.name LIKE ?2')
->orWhere('emotions.modified LIKE ?2')
->setParameter(2, '%' . $filter . '%');
}
if (!empty($orderBy)) {
$builder->addOrderBy($orderBy);
}
return $builder;
}
/** * @param array|null $filter * @param string|null $filterBy * @param int|null $categoryId * * @return DBALQueryBuilder */
{
/** @var QueryBuilder $builder */
$builder = $this->getEntityManager()->createQueryBuilder();
$builder->select(['emotions', 'categories'])
->from(Emotion::class, 'emotions')
->leftJoin('emotions.categories', 'categories');
// Filter the displayed columns with the passed filter string if (!empty($filter)) {
$builder->where('categories.name LIKE ?2')
->orWhere('emotions.name LIKE ?2')
->orWhere('emotions.modified LIKE ?2')
->setParameter(2, '%' . $filter . '%');
}
if (!empty($orderBy)) {
$builder->addOrderBy($orderBy);
}
return $builder;
}
/** * @param array|null $filter * @param string|null $filterBy * @param int|null $categoryId * * @return DBALQueryBuilder */
$idSearch = null;
if (!empty($filters)) {
foreach ($filters as $item) {
if ($item['property'] === 'search') {
$query->setParameter('search', '%' . $item['value'] . '%');
foreach ($this->type->getFields() as $field) {
if (!$field->isSearchAble()) {
continue;
}
$query->orWhere(sprintf('`%s` LIKE :search', $field->getName()));
}
} else {
$where = $item['value'];
$expression = null;
if ($item['property'] === 'id') {
$idSearch = \is_array($where) ? $where : [$where];
}
if (isset($item['expression'])) {
$expression = $item['expression'];
}
if (!empty($filters)) {
foreach ($filters as $item) {
if ($item['property'] === 'search') {
$query->setParameter('search', '%' . $item['value'] . '%');
foreach ($this->type->getFields() as $field) {
if (!$field->isSearchAble()) {
continue;
}
$query->orWhere(sprintf('`%s` LIKE :search', $field->getName()));
}
} else {
$where = $item['value'];
$expression = null;
if ($item['property'] === 'id') {
$idSearch = \is_array($where) ? $where : [$where];
}
if (isset($item['expression'])) {
$expression = $item['expression'];
}
private function getLanguageShops(Shop $shop): array
{
$parentId = $shop->getParentId() ?: $shop->getId();
return $this->connection->createQueryBuilder()
->addSelect('shop.id')
->addSelect('REPLACE(locale.locale, "_", "-") as locale')
->from('s_core_shops', 'shop')
->innerJoin('shop', 's_core_locales', 'locale', 'locale.id = shop.locale_id')
->where('shop.id = :shopId')
->orWhere('shop.main_id = :shopId')
->andWhere('active=1')
->setParameter('shopId', $parentId)
->execute()
->fetchAll();
}
private function getContext(ShopModel $shop, Config $config): Context
{
if (!isset($this->contextCache[$shop->getId()])) {
$this->contextCache[$shop->getId()] = Context::createFromShop($shop, $config);
}
'COUNT(children.id) as childrenCount',
'plugin.translations',
])
->groupBy('form.id')
->setParameter('localeId', $locale->getId())
->setParameter('fallbackId', $fallback)
;
// Search forms if (isset($filter[0]['property']) && $filter[0]['property'] === 'search') {
$builder->where('form.name LIKE :search')
->orWhere('form.label LIKE :search')
->orWhere('translation.label LIKE :search')
->orWhere('translationFallback.label LIKE :search')
->orWhere('element.name LIKE :search')
->orWhere('element.label LIKE :search')
->orWhere('elementTranslation.label LIKE :search')
->setParameter('search', $filter[0]['value']);
$builder->having('childrenCount = 0')
->andWhere('form.parentId IS NOT NULL');
} elseif (!$node) { // Main forms $builder->where('form.parentId IS NULL');
$builder->having('childrenCount > 0');
}
'plugin.translations',
])
->groupBy('form.id')
->setParameter('localeId', $locale->getId())
->setParameter('fallbackId', $fallback)
;
// Search forms if (isset($filter[0]['property']) && $filter[0]['property'] === 'search') {
$builder->where('form.name LIKE :search')
->orWhere('form.label LIKE :search')
->orWhere('translation.label LIKE :search')
->orWhere('translationFallback.label LIKE :search')
->orWhere('element.name LIKE :search')
->orWhere('element.label LIKE :search')
->orWhere('elementTranslation.label LIKE :search')
->setParameter('search', $filter[0]['value']);
$builder->having('childrenCount = 0')
->andWhere('form.parentId IS NOT NULL');
} elseif (!$node) { // Main forms $builder->where('form.parentId IS NULL');
$builder->having('childrenCount > 0');
}
'customer.firstname as firstName',
'customer.lastname as lastName',
'customer.number as number',
]);
$builder->from(Code::class, 'codes')
->leftJoin('codes.customer', 'customer')
->where('codes.voucherId = ?1')
->setParameter(1, $voucherId);
// Search for values if (!empty($filter)) {
$builder->andWhere('codes.code LIKE ?2')
->orWhere('customer.firstname LIKE ?2')
->orWhere('customer.lastname LIKE ?2')
->orWhere('customer.number LIKE ?2')
->setParameter(2, '%' . $filter . '%');
}
if (!empty($order)) {
$builder->addOrderBy($order);
}
return $builder;
}
'customer.lastname as lastName',
'customer.number as number',
]);
$builder->from(Code::class, 'codes')
->leftJoin('codes.customer', 'customer')
->where('codes.voucherId = ?1')
->setParameter(1, $voucherId);
// Search for values if (!empty($filter)) {
$builder->andWhere('codes.code LIKE ?2')
->orWhere('customer.firstname LIKE ?2')
->orWhere('customer.lastname LIKE ?2')
->orWhere('customer.number LIKE ?2')
->setParameter(2, '%' . $filter . '%');
}
if (!empty($order)) {
$builder->addOrderBy($order);
}
return $builder;
}
$exprParameterKey = ':' . $parameterKey;
if (\is_array($where)) {
$exprParameterKey = '(' . $exprParameterKey . ')';
}
$this->operatorValidator->isValid($expression);
$expression = new Comparison($exprKey, $expression, $where !== null ? $exprParameterKey : null);
if (isset($operator)) {
$this->orWhere($expression);
} else {
$this->andWhere($expression);
}
if ($where !== null) {
$this->setParameter($parameterKey, $where);
}
}
return $this;
}
->from($this->getEntityName(), 'blog')
->leftJoin('blog.comments', 'comments', Join::WITH, 'comments.active != 1')
->groupBy('blog.id');
if (!empty($blogCategoryIds)) {
$builder->where('blog.categoryId IN (:blogCategoryIds)')
->setParameter('blogCategoryIds', $blogCategoryIds, Connection::PARAM_INT_ARRAY);
}
if (!empty($filter) && $filter[0]['property'] === 'filter' && !empty($filter[0]['value'])) {
$builder->andWhere('blog.title LIKE ?1')
->orWhere('blog.views LIKE ?1')
->setParameter(1, '%' . $filter[0]['value'] . '%');
}
if (!empty($order)) {
$builder->addOrderBy($order);
}
return $builder;
}
/** * Returns an instance of the \Doctrine\ORM\Query object which select the blog article for the detail page * * @param int $blogArticleId * @param int|null $shopId * * @return Query<Blog> */
->leftJoin('blog.comments', 'comments', Join::WITH, 'comments.active != 1')
->groupBy('blog.id');
if (!empty($blogCategoryIds)) {
$builder->where('blog.categoryId IN (:blogCategoryIds)')
->setParameter('blogCategoryIds', $blogCategoryIds, Connection::PARAM_INT_ARRAY);
}
if (!empty($filter) && $filter[0]['property'] === 'filter' && !empty($filter[0]['value'])) {
$builder->andWhere('blog.title LIKE ?1')
->orWhere('blog.views LIKE ?1')
->setParameter(1, '%' . $filter[0]['value'] . '%');
}
if (!empty($order)) {
$builder->addOrderBy($order);
}
return $builder;
}
/** * Returns an instance of the \Doctrine\ORM\Query object which select the blog article for the detail page * * @param int $blogArticleId * @param int|null $shopId * * @return Query<Blog> */
return array_values($translations);
}
private function getEmotionTranslation($emotionId, array $elementIds)
{
return $this->modelManager->getConnection()->createQueryBuilder()
->select('translation.objectkey, translation.objecttype, translation.objectdata, locale.locale, shop.name as shop')
->from('s_core_translations', 'translation')
->leftJoin('translation', 's_core_shops', 'shop', 'translation.objectlanguage = shop.id')
->leftJoin('shop', 's_core_locales', 'locale', 'shop.locale_id = locale.id')
->where('translation.objecttype = "emotion" AND translation.objectkey = :emotionId')
->orWhere('translation.objectkey IN (:ids)')
->setParameter('emotionId', $emotionId)
->setParameter('ids', $elementIds, Connection::PARAM_INT_ARRAY)
->execute()
->fetchAll();
}
}
}
private function getEmotionTranslation($emotionId, array $elementIds)
{
return $this->modelManager->getConnection()->createQueryBuilder()
->select('translation.objectkey, translation.objecttype, translation.objectdata, locale.locale, shop.name as shop')
->from('s_core_translations', 'translation')
->leftJoin('translation', 's_core_shops', 'shop', 'translation.objectlanguage = shop.id')
->leftJoin('shop', 's_core_locales', 'locale', 'shop.locale_id = locale.id')
->where('translation.objecttype = "emotion" AND translation.objectkey = :emotionId')
->orWhere('translation.objectkey IN (:ids)')
->setParameter('emotionId', $emotionId)
->setParameter('ids', $elementIds, Connection::PARAM_INT_ARRAY)
->execute()
->fetchAll();
}
}
'user.username as username',
'user.lastLogin as lastLogin',
'user.name as name',
'role.name as groupname',
'user.active as active',
'user.email as email',
]);
$builder->from(User::class, 'user');
$builder->join('user.role', 'role');
if (!empty($filter)) {
$builder->where('user.username LIKE ?1')
->orWhere('user.name LIKE ?1')
->setParameter(1, '%' . $filter . '%');
}
$builder->orderBy('username');
return $builder;
}
/** * Returns an instance of the \Doctrine\ORM\Query object which select a list of roles. * * @param int|null $offset * @param int|null $limit * * @return Query<Role> */
'user.lastLogin as lastLogin',
'user.name as name',
'role.name as groupname',
'user.active as active',
'user.email as email',
]);
$builder->from(User::class, 'user');
$builder->join('user.role', 'role');
if (!empty($filter)) {
$builder->where('user.username LIKE ?1')
->orWhere('user.name LIKE ?1')
->setParameter(1, '%' . $filter . '%');
}
$builder->orderBy('username');
return $builder;
}
/** * Returns an instance of the \Doctrine\ORM\Query object which select a list of roles. * * @param int|null $offset * @param int|null $limit * * @return Query<Role> */
$builder->select([
'customer.id as id',
'customer.number as customerNumber',
"CONCAT(CONCAT(customer.firstname, ' '), customer.lastname) as fullName",
'billing.company as company',
'customer.email as email',
]);
$builder->from(Customer::class, 'customer')
->leftJoin('customer.defaultBillingAddress', 'billing')
->where('customer.accountMode = 0')
->andWhere('customer.email = ?0')
->orWhere('customer.number = ?1')
->orWhere('customer.id = ?2');
$builder->setParameter(0, $mappingValue);
$builder->setParameter(1, $mappingValue);
$builder->setParameter(2, $mappingValue);
return $builder;
}
/** * Returns an instance of the \Doctrine\ORM\Query object to validate the Tracking-Code because the Tracking-Code has to be unique * * @param string $trackingCode * @param int $partnerId * * @return Query<Partner> */
'customer.id as id',
'customer.number as customerNumber',
"CONCAT(CONCAT(customer.firstname, ' '), customer.lastname) as fullName",
'billing.company as company',
'customer.email as email',
]);
$builder->from(Customer::class, 'customer')
->leftJoin('customer.defaultBillingAddress', 'billing')
->where('customer.accountMode = 0')
->andWhere('customer.email = ?0')
->orWhere('customer.number = ?1')
->orWhere('customer.id = ?2');
$builder->setParameter(0, $mappingValue);
$builder->setParameter(1, $mappingValue);
$builder->setParameter(2, $mappingValue);
return $builder;
}
/** * Returns an instance of the \Doctrine\ORM\Query object to validate the Tracking-Code because the Tracking-Code has to be unique * * @param string $trackingCode * @param int $partnerId * * @return Query<Partner> */