英文:
How to get the Raw SQL Query with a Criteria Object?
问题
我正在使用来自Shopware的Criteria对象提取数据，但有时Shopware无法满足需求，我需要使用原始的SQL查询。例如，我需要创建嵌套查询。 我应该采用什么方法？是否有用于获取带有Criteria对象的原始SQL查询的函数或类？
英文:
I'm fetching data using the Criteria object that comes from Shopware, but there are times when Shopware falls short and I need to use raw SQL queries. E.g. I need to create nested queries. What approach should I take for this? Is there any function or class for getting raw SQL query with Criteria object?
答案1
得分: 1
你可以注入Shopware\Core\Framework\DataAbstractionLayer\Dbal\CriteriaQueryBuilder并获取Doctrine\DBAL\Query\QueryBuilder的实例，这将允许你使用抽象化或获取通过getSQL调用的原始SQL。
$productDefinition = $container->get(ProductDefinition::class);
$criteria = new Criteria();
$query = new QueryBuilder($container->get(Connection::class));
$qb = $container->get(CriteriaQueryBuilder::class)->build($query, $productDefinition, $criteria, Context::createDefaultContext());
然而：CriteriaQueryBuilder标记为@internal，因此不应依赖它，因为即使在小版本发布中也可能有破坏性更改。如果Shopware自己的数据库抽象层不适合您的需求，最好从头开始使用原始SQL，例如，仅获取某些实体的id。然后，您可以使用更复杂的子查询，随后使用检索的id来获取完整的实体。
$ids = $container->get(Connection::class)->fetchFirstColumn('SELECT LOWER(HEX(`id`)) FROM `product` LIMIT 1');
$criteria = new Criteria($ids);
$product = $container->get('product.repository')->search($criteria, Context::createDefaultContext())->first();
英文:
You could inject Shopware\Core\Framework\DataAbstractionLayer\Dbal\CriteriaQueryBuilder and retrieve an instance of Doctrine\DBAL\Query\QueryBuilder which would allow you to either use the abstraction or to get the raw SQL calling getSQL.
$productDefinition = $container->get(ProductDefinition::class);
$criteria = new Criteria();
$query = new QueryBuilder($container->get(Connection::class));
$qb = $container->get(CriteriaQueryBuilder::class)->build($query, $productDefinition, $criteria, Context::createDefaultContext());
However: CriteriaQueryBuilder is marked @internal and as such you shouldn't rely on it, as there might be breaking changes even on minor releases. If Shopware's own database abstraction layer doesn't suit your needs, it would be arguably better, to use raw SQL from scratch, e.g. to fetch only the ids of certain entities. You could then use more complex subqueries and subsequently use the retrieved ids to fetch the entire complete entities.
$ids = $container->get(Connection::class)->fetchFirstColumn('SELECT LOWER(HEX(`id`)) FROM `product` LIMIT 1');
$criteria = new Criteria($ids);
$product = $container->get('product.repository')->search($criteria, Context::createDefaultContext())->first();
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论