Filter orders through a single metadata - Orderservice

[chilly summit]

How can I filter orders through a single metadata? If I have one metadata in the order, it lists the orders without any problems, but when I have more than one, it doesn't find any orders.

newmeta:"432dacad"
external_id:"72f4dd50-9044-11ee-b6f7-2f9a0e7a9344"

When I try to list orders using this method, it doesn't filter them by newmeta. I have to specify both parameters to get the orders filtered

Filtering Not WORK

async getOrder() {
const order = await this.OrderSerivce.list({
metadata: {
newmeta:"432dacad"
},
});

return { order };

}
Filtering WORK

async getOrder() {
const order = await this.OrderSerivce.list({
metadata: {
newmeta:"432dacad"
external_id:"72f4dd50-9044-11ee-b6f7-2f9a0e7a9344"
},
});

return { order };

}

[shy quarry]

Hello, maybe try with the createQueryBuilder of typeORM directly to the repository like

const order =  await orderRepo.createQueryBuilder('order').where('order.metadata.newmeta = :newmeta', { newmeta: '432dacad' }).getMany()

[vital sleet]

@shy quarry based on your answer Im trying the following:

import { 
  Order,
  type MedusaRequest, 
  type MedusaResponse,
} from "@medusajs/medusa"
import { EntityManager } from "typeorm"

export const GET = async (
  req: MedusaRequest, 
  res: MedusaResponse
) => {
  const manager: EntityManager = req.scope.resolve("manager")
  const orderRepo = manager.getRepository(Order)

  return res.json({
    orders:  await orderRepo.createQueryBuilder('order').where('order.metadata.reference = :reference', { reference: '23005344' }).getMany()
  })
}

but Im getting a QueryFailedError: syntax error at or near "order" error.

Say I want to query for orders where metadata.birdblocker_company.slug is bb (like this order), how would I do that?

My order's metadata object is:

"metadata": {
                "invoice": {
                    "url": "https://cxvcxv024_133_60e06139a8.pdf",
                    "name": "2024.133"
                },
                "message": "levering voor 9-2-2024",
                "reference": "23005344",
                "creator_id": "2",
                "invoice_emails": [
                    "[email protected]"
                ],
                "pipedrive_deal_id": 4070,
                "birdblocker_company": {
                    "id": "2",
                    "bic": "RABONL2U",
                    "rex": "NLREX6171",
                    "city": "Den Hoorn",
                    "eori": "NL8596.30.493",
                    "iban": "NL 87 RABO 0337 8612 26",
                    "logo": {
                        "alt": null,
                        "url": "https://birdblocker-admin-bucket.s3.eu-central-1.amazonaws.com/birdblocker_logo_square_5c86c0ab95.svg",
                        "name": "birdblocker-logo-square.svg"
                    },
                    "name": "BirdBlocker",
                    "slug": "bb",
                    "email": "[email protected]",
                    "phone": "[phone]",
                    "address": "Hooipolderweg 20",
                    "country": "The Netherlands",
                    "website": "birdblocker.com",
                    "zip_code": "2635 CZ",
                    "coc_number": "KvK12345678",
                    "vat_number": "NL8596.30.493.B01",
                    "invoice_slug": "{year}.{number}",
                    "contact_person": "P.L. van der Ven"
                },
                "manual_payment_term": "afterpay_14",
                "stripe_payment_method": null,
                "strapi_order_relation_id": 1265,
                "sent_invoice_to_account_email_address": false
            },