Scrivener lib doesn't load some records

Questions / Help
,
1

Hello, I created an issue in scrivener github, but scrivener owner closed my topic without any help or comment. but I still have a problem with this lib

my Topic:

Hello, when I use Repo.all(query) to load 49 records I have the record concerned and I have no problem all records are displayed , but when I use scrivener to load 20 records on each page like this:

  def show_error_brands(pagenumber, category_id) do
    query = from u in ErrorBrandSchema,
        where: u.category_id == ^category_id,
        order_by: [desc: u.inserted_at],
        join: c in assoc(u, :error_categories),
        select: %{
          id: u.id,
          title: u.title,
          description: u.description,
          image: u.image,
          status: u.status,
          category_id: u.category_id,
          inserted_at: u.inserted_at,
          updated_at: u.updated_at,
          category_name: c.title
        }
    Repo.paginate(query, %{page: pagenumber, page_size: 20})
  end

some data is not displayed, but they exist in my database, I use UUID

I use psql 11 and {:scrivener_ecto, "~> 2.2"} , elixir 1.9 , {:phoenix_ecto, "~> 4.0"}

Help me please
my migration:

defmodule BankError.Repo.Migrations.ErrorBrands do
  use Ecto.Migration

  def change do
    create table(:error_brands, primary_key: false) do
      add :id, :uuid, primary_key: true

      add :title, :string, size: 150, null: false
      add :short_description, :string, size: 164, null: false
      add :description, :text,  null: false
      add :image, :string, size: 200, null: false
      add :seo_alias_link, :string, size: 200, null: false
      add :seo_words, :string, size: 150, null: false
      add :seo_description, :string, size: 164, null: false
      add :status, :boolean, null: false

      add :category_id, references(:error_categories, on_delete: :nothing, type: :uuid)
      timestamps()
    end
    create(
      index(:error_brands, [:seo_alias_link],
        # concurrently: true,
        name: :unique_index_on_error_brands_seo_alias_link,
        unique: true
      )
    )
  end
end

Please help me to fix this, Thanks

my issue: https://github.com/drewolson/scrivener_ecto/issues/83

the other issue without any comment: https://github.com/drewolson/scrivener_ecto/issues/61

1 Like
2

Try adding ordering by your uuid. If rows have the same inserted_at value ordering between them is not deterministic.

1 Like
3

after clearing this line :

 order_by: [desc: u.inserted_at],

it shows me missing records, but how can order it by inserted_at, when I use UUID?

would you mind giving me a example please ?

by the way, when I add it to my query

order_by: [desc: u.id]

I have this problem and it is not fixed
Thank you

4

You can order by multiple columns.

5

if you mean I should do like this!?

  def show_error_brands(pagenumber, category_id) do
    query = from u in ErrorBrandSchema,
        where: u.category_id == ^category_id,
        order_by: [desc: u.id, desc: u.updated_at],
        join: c in assoc(u, :error_categories),
        select: %{
          id: u.id,
          title: u.title,
          description: u.description,
          image: u.image,
          status: u.status,
          category_id: u.category_id,
          inserted_at: u.inserted_at,
          updated_at: u.updated_at,
          category_name: c.title
        }
    Repo.paginate(query, %{page: pagenumber, page_size: 21})
  end

it dosen’t work, but if I remove order_by line it works me , I don’t know what to do !

6

You should sort the other way round.

This will only consider updated_at when the ids are equal, which is unlikely to happen…

2 Likes
7

I really don’t know how to sort with other way !!! and I can’t understand, I used UUID then it will not equal with other record,

do you have any sample code or any way to fix this ? because it just works when the order_by line is removed without any table like:

where: u.category_id == ^category_id,
        join: c in assoc(u, :error_categories),
        select: %{
          id: u.id,
8

What I meant was, you should do order_by: [desc: u.updated_at, desc: u.id].

The problem with only sorting over u.updated_at is, that items with the same updated_at will be ordered randomly each time the query is sent to the DB. This is by the design of most databases. That might cause, that an item close to pagination border to hop between pages, and beeing either displayed on neither page or on both.

This might then be observed as “items appear twice” or “items are missing”.

1 Like
9

Thanks, I edited my code to:

  def show_error_brands(pagenumber, category_id) do
    query = from u in ErrorBrandSchema,
        where: u.category_id == ^category_id,
        order_by: [desc: u.updated_at, desc: u.id],
        join: c in assoc(u, :error_categories),
        select: %{
          id: u.id,
          title: u.title,
          description: u.description,
          image: u.image,
          status: u.status,
          category_id: u.category_id,
          inserted_at: u.inserted_at,
          updated_at: u.updated_at,
          category_name: c.title
        }
    Repo.paginate(query, %{page: pagenumber, page_size: 21})
  end

and it shows me the missing records, but after reading your text, I am afraid this would happen again in the future :expressionless: :pleading_face: :cold_sweat:

10

Only if they have the same updated_at and id. And I hope id will not clash ever :smiley:

Adding the id to the sorted_by is a trick to stabilize the usually unstable sort.

Any column that is guaranteed to have unique values among the result set is fine. For id its easy to guarantee and therefore its used quite often.

2 Likes
11

Informative, thanks @NobbZ.