Scrivener lib doesn't load some records

shahryarjb

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: scrivener dosent load some records · Issue #83 · mojotech/scrivener_ecto · GitHub

the other issue without any comment: Paginate join incorrect · Issue #61 · mojotech/scrivener_ecto · GitHub

10 comments

#troubleshooting #scrivener

1 1264 10

2019-12-18 20:51:59 UTC

Showing Posts 1 to 10

Show Best Posts
Show All (oldest first)
Show All (newest first)

LostKobrakai

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

Post #1

shahryarjb

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

Post #2

LostKobrakai

You can order by multiple columns.

Post #3

shahryarjb

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 !

Post #4

NobbZ

You should sort the other way round.

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

Post #5

shahryarjb

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,

Post #6

NobbZ

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”.

Post #7

shahryarjb

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