v2 -> v3: queryCollection().sort() unexpected order

[chrisjansky]

Environment

• Operating System: Darwin
• Node Version: v23.6.1
• Nuxt Version: 3.15.4
• CLI Version: 3.21.1
• Nitro Version: 2.10.4
• Package Manager: pnpm@9.14.4
• Builder: -
• Runtime Modules: @nuxt/content@3.0.1

Version

v3

Reproduction

I would like to post a reproduction, but I am unable to setup @nuxt/content3 in StackBlitz: getting this error:

Restarting Nuxt due to error: `Error: Could not locate the bindings file. Tried:                                                                                     nuxi:nuxi 18:08:53
 → /home/project/node_modules/better-sqlite3/build/better_sqlite3.node
...

Is there a workaround or a starter template that I am not seeing?

Description

Coming from v2, I am unable to reproduce the same JSON sorting and filtering behaviour.

Before (v2):

queryContent
      .where({ _type: "json", status: { $not: "hidden" } })
      .sort({ status: 1, priority: 1 })
      .find()

Expected result:

- one
  - status: something
  - priority: 1
- two
  - status: something
  - priority: 2
- three
  - status: null
  - priority: 2

Discarded:

- four
  - status: hidden <= // discarded because status = hidden
  - priority: null

After (v3):

queryCollection
      .andWhere((query) => query.where("extension", "=", "json").where("status", "<>", "hidden"))
      .order("status", "ASC")
      .order("priority", "ASC")

Actual result:

- bar
  - status: something
  - priority: 1
- baz
  - status: something
  - priority: 2

Namely item three discarded because status = null, but only status = hidden should be discarded.

Also in v2 it was possible to have a complex sorting query using multiple criteria, i.e. sort items with status first and then sort those based on priority.

I am missing something? 😅

Thanks!

Additional context

No response

Logs

[farnabaz]

I'm confused about the issue, for me it's more like filtering than ordering.
Content v3 uses Sqlite for filtering and Sqlite deals with null values a bit differently than v2. If you use <> operator in the query it will ignore null values.

You can use orWhere to resolve it, you can check the internal usage

content/src/runtime/internal/navigation.ts

Lines 15 to 18 in b46fc37

	.orWhere(group => group
	.where('navigation', '<>', 'false')
	.where('navigation', 'IS NULL'),
	)

As for StackBlitz, Just run this command and it will fix:

npm i https://pkg.pr.new/@nuxt/content@b46fc37 sqlite3

(better-sqlite3 does not support WebContainers and in current dev release Module detects WebContainer and uses sqlite3 package to resolve it)

[chrisjansky]

@farnabaz Thanks for the reply.

I understand the motivation behind migrating to SQLite, but the resulting new API is not very intuitive to use.

What was before a simple prefixing with dot to ignore a file: .ignoreMe.md (see #3090) has now become:

---
hidden: true
---

queryCollection("projects")
    .orWhere((query) => query.where("hidden", "<>", true).where("hidden", "IS NULL"))
    .all()

It’s unfortunate that one has to do these mental gymnastics in a compound filtering in order to filter a very simple use case: omit a particular value.

Also I have noticed a discrepancy:

projects: defineCollection({
      type: "page",
      source: "projects/*.md",
      schema: z.object({
        hidden: z.boolean().optional(),
      }),
    })

You quoted the source code with .where('navigation', '<>', 'false'), but it turns out queryCollection schema already unwraps the values and returns the parsed boolean, so it must be false boolean in query.where, not a string 😢

I know it’s absolutely pointless to be nagging about an open source project that you do in your free time. The reality is the migration is very painful and there are quite a few undocumented foot guns along the way. Nevertheless thank you for your effort – just hoping to provide some feedback.