# How to create a query?

**URL:** <https://forum.shopware.com/t/how-to-create-a-query/93807>\
**Category:** Shopware 6 (English)\
**Created:** [4. April 2022 um 23:54 UTC](https://forum.shopware.com/t/how-to-create-a-query/93807 "2022-04-04T23:54:32Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![hasan.h](https://avatars.discourse-cdn.com/v4/letter/h/e56c9b/32.png) [@hasan.h](https://forum.shopware.com/u/hasan.h)\
**Post date:** [4. April 2022 um 23:54 UTC](https://forum.shopware.com/t/how-to-create-a-query/93807/1 "2022-04-04T23:54:32Z")

</div>

Hello,  
I need the creation date of an order from the past in which the the current user (who is logged in) has bought the current product (whose product detail page is opened currently). What would my query look like?

---

<div class="post-metadata">

**Author:** ![NiklasFechner](https://avatars.discourse-cdn.com/v4/letter/n/e274bd/32.png) [@NiklasFechner](https://forum.shopware.com/u/NiklasFechner)\
**Post date:** [5. April 2022 um 16:04 UTC](https://forum.shopware.com/t/how-to-create-a-query/93807/2 "2022-04-05T16:04:42Z")

</div>

Here an example that should be useful. Although I have to guess that you search for the latest order date as a customer could have more than one order for that product.  
The query has to be executed within the _ProductPageLoadedEvent_

```auto
            $criteria = new Criteria();
            // filter the criteria for orderlineitems by customer id and product id
            $criteria->addFilter(new EqualsFilter('order.orderCustomer.customerId', $customer->getId()));
            $criteria->addFilter(new EqualsFilter('productId', $page->getProduct()->getId()));

            // add an aggregation to get the max (latest) date the product was included within an order of the customer

            $criteria->addAggregation(new MaxAggregation(
                'latest-order',
                'order.orderDateTime'
            ));

            $search = $this->orderLineItemRepository->search($criteria, $event->getContext());
            
            // If needed this will include all orderlineitems the customer is related to
            $lineItems = $search->getEntities();

            // This will get the result of the aggregation
            /** @var MaxResult $aggregation */
            $latestOrder = $search->getAggregations()->get('latest-order');
            // If the customer bought the product, here you have the date of the latest order.
            $latestOrderDate = $latestOrder->getMax();

```

You may tweak that further to filter by paid orders etc.

---

<div class="post-metadata">

**Author:** ![hasan.h](https://avatars.discourse-cdn.com/v4/letter/h/e56c9b/32.png) [@hasan.h](https://forum.shopware.com/u/hasan.h)\
**Post date:** [6. April 2022 um 05:29 UTC](https://forum.shopware.com/t/how-to-create-a-query/93807/3 "2022-04-06T05:29:40Z")

</div>

> [@NiklasFechner](#):
>
> ```auto
> $criteria = new Criteria();
> // filter the criteria for orderlineitems by customer id and product id
> $criteria->addFilter(new EqualsFilter('order.orderCustomer.id', $customer->getId()));
> $criteria->addFilter(new EqualsFilter('productId', $page->getProduct()->getId()));
> 
> // add an aggregation to get the max (latest) date the product was included within an order of the customer
> 
> $criteria->addAggregation(new MaxAggregation(
> 'latest-order',
> 'order.orderDateTime'
> ));
> 
> $search = $this->orderLineItemRepository->search($criteria, $event->getSalesChannelContext());
>             
> // If needed this will include all orderlineitems the customer is related to
> $lineItems = $search->getEntities();
> 
> // This will get the result of the aggregation
> /** @var MaxResult $aggregation */
> $latestOrder = $search->getAggregations()->get('latest-order');
> // If the customer bought the product, here you have the date of the latest order.
> $latestOrderDate = $latestOrder->getMax();
> 
> ```

This is giving NULL on all customers, even ones who have bought this product in the past.

---

<div class="post-metadata">

**Author:** ![hasan.h](https://avatars.discourse-cdn.com/v4/letter/h/e56c9b/32.png) [@hasan.h](https://forum.shopware.com/u/hasan.h)\
**Post date:** [6. April 2022 um 05:41 UTC](https://forum.shopware.com/t/how-to-create-a-query/93807/4 "2022-04-06T05:41:14Z")

</div>

Using var\_dump(), I found out that it is unable to return any order in $search.

---

<div class="post-metadata">

**Author:** ![hasan.h](https://avatars.discourse-cdn.com/v4/letter/h/e56c9b/32.png) [@hasan.h](https://forum.shopware.com/u/hasan.h)\
**Post date:** [6. April 2022 um 06:22 UTC](https://forum.shopware.com/t/how-to-create-a-query/93807/5 "2022-04-06T06:22:39Z")

</div>

I tried changing the customer id to:  
order.orderCustomer.customerId’  
But its still not resolved.

---

<div class="post-metadata">

**Author:** ![NiklasFechner](https://avatars.discourse-cdn.com/v4/letter/n/e274bd/32.png) [@NiklasFechner](https://forum.shopware.com/u/NiklasFechner)\
**Post date:** [6. April 2022 um 08:32 UTC](https://forum.shopware.com/t/how-to-create-a-query/93807/6 "2022-04-06T08:32:26Z")

</div>

Your change is correct. I will update my answer together with one more change (the context shouldn’t be SalesChannel in that case).  
However, the query works otherwise.  
Do you use the order\_line\_item.repository?  
Do you have products with variations?  
And to debug you may remove the product and/or the customer filter to check if it returns something then.

---

<div class="post-metadata">

**Author:** ![hasan.h](https://avatars.discourse-cdn.com/v4/letter/h/e56c9b/32.png) [@hasan.h](https://forum.shopware.com/u/hasan.h)\
**Post date:** [6. April 2022 um 09:20 UTC](https://forum.shopware.com/t/how-to-create-a-query/93807/7 "2022-04-06T09:20:37Z")

</div>

Okay so in my case, the issue was that the customer id i was getting from context was different from the one in database. I deleted all my customers and then created new ones and the code worked just fine. Thank you so much for all the help.