drewolson / scrivener_ecto Goto Github PK

def list_monitored_karmas(username, params) do query = from fe in "karma_entries", join: karma in "karmas", on: fe.karma_id == karma.id, join: kem in "karma_entry_monitors", on: fe.id == kem.karma_entry_id, join: u in "users", on: u.id == kem.user_id and u.username == ^username, where: fe.status != "Done", select: %{ username: fe.username, assigned_to: fe.assigned_to, id: fe.id, karma_id: karma.id, karma_code: karma.karma_code, karma_name: karma.karma_name, subject: fe.subject, status: fe.status, status_date: fe.updated_at }, order_by: [desc: fe.updated_at] Repo.paginate(query, params) end

The above code is giving the error -
the following exception happened when compiling a subquery.
** (FunctionClauseError) no function clause matching in Ecto.Query.Planner.subquery_struct_and_fields/1

The subquery originated from the following query:

from k in subquery(from k0 in "karma_entries", join: k1 in "karmas", on: k0.karma_id == k1.id, join: k2 in "karma_entry_monitors", on: k0.id == k2.karma_entry_id, join: u in "users", on: u.id == k2.user_id and u.username == ^"m2", where: k0.status != "Done"), select: count("*")

I am not able to solve this problem. I am just learning phoenix, ecto and scrivener.
Please help.

I just spent the past few minutes reading through the various issues/PRs on this: #1, #44, #45, etc., and I'm still unsure of the recommended approach for paginating a query with nested join(s).

In SQL, the approach is clear:

SELECT * FROM (SELECT * FROM posts LIMIT 10 OFFSET 20)
  LEFT JOIN comments ...

I can't find any documentation showing how this is to be done with Scrivener. I'm happy to submit a PR if necessary, but what's the recommended approach for this? Should this just be done with multiple queries?

Hi
trying to paginate my records and i'm getting some weird behavior (see below)

deps:

[
  {:jamdb_oracle, "~> 0.1"},
  {:scrivener_ecto, "~> 1.3"},
  {:phoenix, "~> 1.3"},
  {:phoenix_ecto, "~> 3.3"}
]

I'm wondering what's going wrong?

first page, page size of 1

here ecto returns the first record (like expected) and scrivener returns an empty list

ecto:

a = Repo.all(from(a in Schemas.Activity, limit: 1, offset: 0)

results in:

... FROM activities a0 OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY []

Scrivener:

a = Repo.all(from(a in Schemas.Activity, limit: 1, offset: 0))

results in:

... FROM activities a0 OFFSET :2 ROWS FETCH NEXT :1 ROWS ONLY [1, 0]

3rd page, page size of 1

here scrivener returns too many records. (although the query doesn't seem wrong)

ecto:

iex> Enum.count(Repo.all(from(a in Schemas.Activity, limit: 1, offset: 2)))
1

... FROM activities a0 OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY []

scrivener:

iex> Enum.count(Repo.paginate(from(a in Schemas.Activity), page: 3, page_size: 1))
2

... FROM activities a0 OFFSET :2 ROWS FETCH NEXT :1 ROWS ONLY [1, 2]

Hi everyone,
I have 2 models Post and Comments.
What I wanted to do is fetch all post and order by the latest comment.
I created this query
from(
p in Post,
join: c in assoc(p, :comments),
where: p.group_id == ^group_id,
order_by: [desc: c.inserted_at],
preload: [comments: c],
)

When I use Repo.all everything works fine.
However when I use Repo.paginate, the result is weird. On page 1 I have 3 result, on page 2 I have 1 result and so on. I set the page size to be 20 and my post only have 16 record. So it should only be 1 page.
Anyone encounter this same issue before?

Any reason the DISTINCT needs to be a part of the total_entries count? The primary key will be unique.

select([m], count(field(m, ^primary_key), :distinct))

It really kills performance versus a count() without distinct

I have an autocomplete route. Here's the model code.

def search_by_name(query) when is_binary(query) do
    from r in Room, where: ilike(r.name, ^"%#{query}%")
  end

My design allows rooms with the same name, obviously only want distinct names for autocomplete, so I change the query to:

def search_by_name(query) when is_binary(query) do
    from r in Room, where: ilike(r.name, ^"%#{query}%"), distinct: r.name
  end

This causes an error.

** (Postgrex.Error) ERROR (grouping_error): column "r0.name" must appear in the GROUP BY clause or be used in an aggregate function
[debug] QUERY ERROR db=0.7ms
SELECT DISTINCT ON (r0."name") count(DISTINCT r0."id") FROM "rooms" AS r0 WHERE (r0."name" ILIKE $1) ["%Go%"]

The error only occurs when trying to paginate. If I grab all the entries with Repo.all, there are no issues.

Hello,

Ecto 3.0 seems to be coming along. However if I try to compile scrivener_ecto against the master branch of ecto we have an offending line causing compilation error. I am not sure if this is an issue with ecto or with scrivener_ecto. However I figured so far that someone else ought to run into this problem eventually.

https://github.com/drewolson/scrivener_ecto/blob/master/lib/scrivener/paginater/ecto/query.ex#L66

lib/scrivener/paginater/ecto/query.ex:66
|> select([x: source_index], struct(x, ^[field]))

== Compilation error in file lib/scrivener/paginater/ecto/query.ex ==
** (Ecto.Query.CompileError) unbound variable `x` in query. If you are attempting to interpolate a value, use ^var
    expanding macro: Ecto.Query.select/3
    lib/scrivener/paginater/ecto/query.ex:55: Scrivener.Paginater.Ecto.Query.prepare_select/1
    (elixir) expanding macro: Kernel.|>/2
    lib/scrivener/paginater/ecto/query.ex:55: Scrivener.Paginater.Ecto.Query.prepare_select/1

If you are not using group_by in your pagination query I think you should be fine just removing the lines like this as a temporary workaround
rsystem-se@f636d29

Dialyzer is raising the following complaints:

:0: Unknown function 'Elixir.Scrivener':paginate/2
:0: Unknown function 'Elixir.Scrivener.Config':new/3

Hi,

I have two models: Page that has many PageLanguages.

Query preload looks like:

page
|> join(:left, [p], _ in assoc(p, :languages))
|> preload([_, languages], [languages: languages])

The problem is if I have for example 10 posts with the languages I will get total_entries: 20 but result should be 10. I think we need some DISTINCT option to solve this problem, or I am doing something wrong?

I have the following query:

def filter(or_tags, and_tags) do
    or_query = or_query(or_tags)
    num = length(and_tags)
    from record in Record,
      join: recordtag in RecordTag, on: recordtag.record_id == record.id,
      join: tag in Tag, on: tag.id == recordtag.tag_id,
      where: tag.name in ^and_tags,
      group_by: record.id,
      having: count(tag.name, :distinct) == ^num,
      intersect: ^or_query,
      select: record
  end

def or_query(tags) do
    from record in Record,
      join: recordtag in RecordTag, on: recordtag.record_id == record.id,
      join: tag in Tag, on: tag.id == recordtag.tag_id,
      where: tag.name in ^tags,
      group_by: record.id,
      select: record
  end

When I try to paginate, I get the following error:
ERROR 42601 (syntax_error) each INTERSECT query must have the same number of columns
The produced query:

query: SELECT count('*') FROM (SELECT r0."id" AS "id" FROM "records" AS r0 INNER JOIN "record_tags" AS r1 ON r1."record_id" = r0."id" INNER JOIN "tags" AS t2 ON t2."id" = r1."tag_id" WHERE (t2."name" = ANY($1)) GROUP BY r0."id" HAVING (count(DISTINCT t2."name") = $2) INTERSECT (SELECT r0."id", r0."address1", r0."address2", r0."address3", r0."cert_end", r0."cert_id", r0."cert_start", r0."cert_type", r0."city", r0."country", r0."dba1", r0."dba_other", r0."email", r0."fax", r0."first_name", r0."last_name", r0."legal_name", r0."license", r0."naics", r0."phone", r0."postal_code", r0."state", r0."url", r0."inserted_at", r0."updated_at" FROM "records" AS r0 INNER JOIN "record_tags" AS r1 ON r1."record_id" = r0."id" INNER JOIN "tags" AS t2 ON t2."id" = r1."tag_id" WHERE (t2."name" = ANY($3)) GROUP BY r0."id")) AS s0

If I use a JOIN and subquery vs. intersect pagination works, however I take a large performance hit.

Any clues on how I can get this to work? Much appreciated!

Thanks for the great library. I was recently building a query and noticed a small quirk.

query = Trip
        |> join(:left, [t], d in assoc(t, :destinations))
        |> join(:left, [t, _d], c in assoc(t, :companies))
        |> join(:left, [t, _d, _c], u in assoc(t, :travellers))
        |> where([_t, _d, c, _u], c.id == ^company.id)
        |> order_by([t, _d, _c, _u], desc: t.start)
        |> preload([_t, d, _c, u], destinations: d, travellers: u)

query
==> #Ecto.Query<from t0 in Radar.Trips.Trip,
 left_join: d in assoc(t0, :destinations),
 left_join: c in assoc(t0, :companies), 
 left_join: t1 in assoc(t0, :travellers),
 where: c.id == ^"91955970-34eb-4a1b-97e6-53e9084abbfa",
 order_by: [desc: t0.start], preload: [travellers: t1, destinations: d]>

query |> Repo.all() |> length()
==> 3424

# to mimic the count query
query |> exclude(:preload) |> exclude(:order_by) |> Repo.all() |> length()
==> 4556

Notice that by excluding the preloads, the count jumps. This is likely because the joins are kept, but the removal of preloads results in duplicate rows during the count query.

Adding distinct removes the issue.

query = Trip
        |> distinct(true)
        |> join(:left, [t], d in assoc(t, :destinations))
        |> join(:left, [t, _d], c in assoc(t, :companies))
        |> join(:left, [t, _d, _c], u in assoc(t, :travellers))
        |> where([_t, _d, c, _u], c.id == ^company.id)
        |> order_by([t, _d, _c, _u], desc: t.start)
        |> preload([_t, d, _c, u], destinations: d, travellers: u)

query |> Repo.all() |> length()
==> 3424

query |> exclude(:preload) |> exclude(:order_by) |> Repo.all() |> length()
==> 3424

Should a small note be made in the readme about it?

Can we upgrade this to use ecto ~>2.1.0

# Query.ex
  defp total_entries(query, repo) do
    primary_key =
      query.from
      |> elem(1)
      |> apply(:__schema__, [:primary_key])
      |> hd # <- error

    query
    |> exclude(:order_by)
    |> exclude(:preload)
    |> exclude(:select)
    |> exclude(:group_by)
    |> select([m], count(field(m, ^primary_key), :distinct))
    |> repo.one!
  end

Errors for me. Does this code support composite primary keys?

I'm trying to paginate a join table.

Hi!

I facing the following issue trying to implement pagination with this library:

Protocol.UndefinedError

protocol Scrivener.Paginater not implemented for [%TestApp.Stuff{__meta__: #Ecto.Schema.Metadata<:loaded, "stuffs">(...)

My Repo file:

defmodule TestApp.Repo do
  use Ecto.Repo, otp_app: :test_app
  use Scrivener, page_size: 5, max_page_size: 10
end

And I'm trying to use it the controller (using phoenix) as this:

  def index(conn, _params) do
    stuffs = Repo.all(Stuff) |> Repo.preload(:others) |> Repo.paginate(page: 2, page_size: 5)
    render(conn, "index.html", stuffs: stuffs)
  end

I tough maybe I had to use the scrivener dependency or so, but seems that don't.

I'm fetching from our own API passing in page 1, and page_size 10, and I'll get the results but in this case lets say the 10th entry is item1. When I fetch again passing in page 2, and page_size 10, the 1st entry for this call is also item1. Any thoughts on how to fix this? Thanks!

Hi, I'd like to increase the database timeout for a specific query that needs to be paginated, which can be achieved in Ecto by passing timeout: value to Repo.all. Is there a way to pass ecto repo options to scrivener_ecto? Looking at the source code it seems not. I could look into implementing this feature but it would be nice if you could point me in the right direction first. For example, I am not sure if changes need to be made in the scrivener base repo as well.

At the moment the following options are supported by ecto Repo.all/Repo.one:

• https://hexdocs.pm/ecto/Ecto.Repo.html#module-shared-options
• https://hexdocs.pm/ecto/Ecto.Repo.html#c:all/2-options

Thanks for your consideration!

I had updated phoenix ecto, so scrivener_ecto also was updated to 2.0.0
mix.exs
{:scrivener_ecto, "~> 2.0.0"},

code call pagination

Division
    |> select([d], d)
    |> where([d], d.id in ^ids)
    |> join(:inner, [d], l in assoc(d, :legal_entity))
    |> join(:inner, [..., l], e in Employee,
      on:
        e.legal_entity_id == l.id and e.employee_type in [@type_owner, @type_pharmacy_owner] and
          e.is_active
    )
    |> join(:inner, [..., e], innm in assoc(e, :party))
    |> preload([..., l, e, p], legal_entity: {l, employees: {e, party: p}})
    |> preload([:addresses])
    |> Repo.paginate(Map.take(params, ~w(page page_size)a) |> IO.inspect())

I added IO.inspect just to ensure page is 2
Output:
%{page: 2}

Result:
{:ok, %Scrivener.Page{entries: [%Core.Replica.Division{ .... }], page_number: 1, page_size: 10, total_entries: 1, total_pages: 1}}

So the problem is I receive page_number: 1, when %{page: 2} was set
Also I got same wrong result when call |> Repo.paginate(page: 2) according to doc

(Following up on #20 )

Detecting if there's an order_by and using a combination of WHERE pk > :last_id and LIMIT :page_size seems like a good approach.

What do you think?

http://use-the-index-luke.com/no-offset

I have post many_to_many :tags, MyApp.Tag, join_through: MyApp.PostTag

and tag many_to_many :posts, MyApp.Post, join_through: MyApp.PostTag

I am trying to list and paginate all the posts that have been tagged with a particular tag.
In the posts controller I have:

def tag_index(conn, params) do
    page = Tag
          |> where([t], t.tag_name == ^params["tag_name"])
          |> preload(posts: :user)
          |> Repo.paginate(params)

    render(conn, "tag_index.html", tag: page.entries, page: page)
end

In the tag_index.html I have:

<%= for post <- @tag.posts do %>
    <%= render "_index_content.html", post: post, conn: @conn %>
<% end %>

<%= if @page.page_number > 1 do %>
    <%= link "Prev Page", to: post_path(@conn, :tag_index, @tag.tag_name %>
<% end %>
<%= if @page.page_number < @page.total_pages do %>
    <%= link "Next Page", to: post_path(@conn, :tag_index, @tag.tag_name %>
<% end %>

The above causes an (ArgumentError) argument error for the line <%= for post <- @tag.posts do %>. I debugged and saw that page.entries is properly loaded and all the page.entries and total_entries and all. But doing @tag.posts does not work. Any help in rectifying this issue will be appreciated.

Using Phoenix 1.2.0
Elixir 1.4.2
Scrivener_ecto:1.2.1

Does Scrivener support MySQL since MySQL has subquery support?

test "can not be used with a group by clause" do
      create_posts()

      page =
        Comment
        |> group_by([c], c.post_id)
        |> Scrivener.Ecto.Repo.paginate

      assert page.total_entries == 1
end

This test fails with the following errors:

1) test paginate can not be used with a group by clause (Scrivener.Paginator.Ecto.QueryTest)
     test/scrivener/paginator/ecto/query_test.exs:208
     ** (Postgrex.Error) ERROR 42803 (grouping_error): column "c0.id" must appear in the GROUP BY clause or be used in an aggregate function
     stacktrace:
       (ecto) lib/ecto/adapters/sql.ex:436: Ecto.Adapters.SQL.execute_and_cache/7
       (ecto) lib/ecto/repo/queryable.ex:130: Ecto.Repo.Queryable.execute/5
       (ecto) lib/ecto/repo/queryable.ex:35: Ecto.Repo.Queryable.all/4
       (ecto) lib/ecto/repo/queryable.ex:68: Ecto.Repo.Queryable.one/4
       (scrivener_ecto) lib/scrivener/paginater/ecto/query.ex:38: Scrivener.Paginater.Ecto.Query.total_entries/3
       (scrivener_ecto) lib/scrivener/paginater/ecto/query.ex:10: Scrivener.Paginater.Ecto.Query.paginate/2
       test/scrivener/paginator/ecto/query_test.exs:214: (test)

The select statement is important in group by clauses, so excluding it causes these issues. I realize that using Repo.all with the query that is passed in may not be the most performant option, but at least results would be accurate and it would work for all queries.

I'm using scrivner_ecto 1.3, Phoenix 1.3, and Ecto 2.1.

I can't get Repo.preload() to work. Here's an example:

def list_account_memberships(query, %Account{} = account) do
  query
  |> Bodyguard.scope(account)
  |> Repo.preload(user: :credential)
  |> Repo.paginate()
end

Bodyguard.scope(account) filters down to this:

def scope(query, account, _params) do
  from q in query, where: [account_id: ^account.id]
end

I can get this to work by using an expression to preload the associations. For example:

def list_account_memberships(query, %Account{} = account) do
  query
  |> Bodyguard.scope(account)
  |> expression_preload(user: :credential)
  |> Repo.paginate()
end

defp expression_preload(query, opts), do: from q in query, preload: ^opts

Using Repo.preload() causes the following error:

no match of right hand side value: #Ecto.Query<from m in Paper.Accounts.Membership, where: m.account_id == ^1>

Any ideas?

The pull request below breaks a number of clients we have that increment the page until the entries array is empty.

https://github.com/drewolson/scrivener_ecto/pull/43/files

Would you be open to a pull request making this configurable?

==> scrivener_ecto
Compiling 2 files (.ex)

== Compilation error in file lib/scrivener/paginater/ecto/query.ex ==
** (Ecto.Query.CompileError) unbound variable `x` in query. If you are attempting to interpolate a value, use ^var
    expanding macro: Ecto.Query.select/3
    lib/scrivener/paginater/ecto/query.ex:55: Scrivener.Paginater.Ecto.Query.prepare_select/1
    (elixir) expanding macro: Kernel.|>/2
    lib/scrivener/paginater/ecto/query.ex:55: Scrivener.Paginater.Ecto.Query.prepare_select/1

This syntax looks like it probably conflicts with the new named binding syntax in Ecto 3.

i have to add category associated products in same page. any idea how to achieve this?

def associated_products(conn, %{"category_id" => id}) do
    page =
      Urbanitas.Query.Category.get!(Repo, id)
      |> Repo.preload([products: Urbanitas.Query.Product.products_with_master_variant])
      |> Urbanitas.Repo.paginate() 
    categories = Urbanitas.Query.Category.with_associated_products(Repo)

    render conn, "products.html",
      categories: categories,
      products: page.entries,
      page: page

  end

iam getting this error
protocol Scrivener.Paginater not implemented

This is probably an oversight in #64, but queries that have an DISTINCT ON clause (postgresql only) can not be counted on without a group_by. They need to be counted through a subquery.

I'll try the write a test for this...

Issue:

Ecto 2.1.x - Incompatible with elixir 1.2

https://github.com/elixir-ecto/ecto/blob/v2.1/CHANGELOG.md

I'm setup a build matrix on travis.yml and I'm getting the following error when using

    - otp_release: 18.3
      elixir: 1.2

warning: the dependency :ecto requires Elixir "~> 1.3" but you are running on v1.2.6

https://travis-ci.org/stephenmoloney/scrivener_list/builds/233549003

Potential solutions:

1. Add a warning to readme.md about potential issue and to fix ecto manually if using old elixir.
2. Up version of elixir required to use scrivener_ecto to 1.3
3. Fix ecto at 2.0 --- not a good idea 👎

If let's say the last page number is 99, then page 100 will simply return the page 99. That's IMHO really wrong. I pass the page number from API and the page 100 need to return 0 results.

|> Repo.paginate(page: page_number)

Is there a way how to turn this off? Would you accept a patch that cancel this behaviour and point me in the direction where should this be changed in code?

Thank you.

Page number should not be greater than total_pages.

Maybe change this:

  def paginate(query, %Config{page_size: page_size, page_number: page_number, module: repo, caller: caller}) do
    total_entries = total_entries(query, repo, caller)

    %Page{
      page_size: page_size,
      page_number: page_number,
      entries: entries(query, repo, page_number, page_size, caller),
      total_entries: total_entries,
      total_pages: total_pages(total_entries, page_size)
    }
  end

to

  def paginate(query, %Config{page_size: page_size, page_number: page_number, module: repo, caller: caller}) do
    total_entries = total_entries(query, repo, caller)
    total_pages = total_pages(total_entries, page_size)
    page_number = min(page_number, total_pages)

    %Page{
      page_size: page_size,
      page_number: page_number,
      entries: entries(query, repo, page_number, page_size, caller),
      total_entries: total_entries,
      total_pages: total_pages
    }
  end

NOTE: code not tested.

On a "normal app" works an ecto query then paginate.

Ex:

Normal.People.Lists.list_filter("por", true, "demo") |> Normal.Repo.paginate(%{page_size: 10})

With the same config on an umbrella app, it fails. (Both Repo and Scrievener/Ecto on the same app)

Ex:

UmbrellaApp.People.Lists.list_filter("por", true, "demo") |> UmbrellaApp.Repo.paginate(%{page_size: 10})

Output:

** (Protocol.UndefinedError) protocol Scrivener.Paginater not implemented for [%UmbrellaApp.People.List{__meta__: #Ecto.Schema.Metadata<:built, "lists">, data: %{"author_id" => 1, "author_subdomain" => "demo", "cdn_file" => "accounts/demo/demo_list_template-pt.xlsx", "country" => "BRA", "editor_id" => 1, "editor_subdomain" => "demo", "locale" => "por", "targets" => 1, "usage" => 27, "version" => 13}, enabled: true, id: 1, inserted_at: nil, name: "Vieira", targets: #Ecto.Association.NotLoaded<association :targets is not loaded>, updated_at: {{2018, 7, 23}, {13, 11, 14, 653266}}}]. This protocol is implemented for: Atom, Ecto.Query
    (scrivener) deps/scrivener/lib/scrivener/paginater.ex:1: Scrivener.Paginater.impl_for!/1
    (scrivener) deps/scrivener/lib/scrivener/paginater.ex:10: Scrivener.Paginater.paginate/2

Good morning,

Was just giving Scrivener a shot, and it's not immediately obvious to me how to get it to use Ecto's prefix option in the queries it builds.

Am I missing something or is this not supported?

Thanks!

why does scrivener need 2 queries ? when I load pagination , my code shows me 2 query for loading it, why ?

my code :

	def load_all_category_info(["admin"], pagenumber) do
		query = from u in PostCategory,
					order_by: u.inserted_at,
          select: %{
						title: u.title,
						status: u.status,
						language: u.language,
						group_acl: u.group_acl,
						description: u.description,
						seo_alias_link: u.seo_alias_link,
						seo_words: u.seo_words,
						seo_description: u.seo_description,
						seo_language: u.seo_language,
						seo_language_link: u.seo_language_link,
						pic_x1_link: u.pic_x1_link,
						pic_x2_link: u.pic_x2_link,
						pic_x3_link: u.pic_x3_link,
					}
		Repo.paginate(query, %{page: pagenumber})
	end

elixir : v1.6.4 and {:scrivener_ecto, "~> 1.3"}

please help me .

Getting the following error

function Repo.paginate/2 is undefined (module Repo is not available)

and my code

def index(conn, params) do
    #magnets = Magnets.list_magnets()
    #render(conn, "index.html", magnets: magnets)

    page = Magnets
           |> Repo.paginate(params)

    #magnets = Magnets.list_magnets(params)
    render(conn, "index.html", magnets: page.entries, page: page)
  end

I'm using Repo.paginate(query). Because the lib uses Repo.one!, it throws (Ecto.NoResultsError) expected at least one result but got none in query: if there are no results.
I think it would be helpful if this would return either empty page or {:error, :something} tuple. Repo.paginate! could be added with the current behaviour.

Please let me know what you think about it, I can submit a PR if you agree.

Hello @drewolson, first I want to thank you for your work on this package.

I've taken some dig into the source code and looks like you use limit/offset based pagination, I think it's worth mentioning this in README as it would result in very bad performance in some use cases (eg. going to very deep page).

Additionally, it would be awesome to see there how many queries are performed instead of just one (I believe you issue a second query to count total_entries if it's not in the struct options).

total_enties behavior is not correct for some use cases too, eg. you filter messages by conversation, default implementation would drop this filter and believe that total entries are equal to total messages in all conversations.

Being able to set a max page_size can be useful, especially when dealing with complex queries where requesting a lot of data could impact server performance.

got a error. (Mix) No package with name scrivener_ecto (from: mix.exs) in registry

defp deps do
  [..., 
  {:scrivener_ecto, "~> 1.0"},
  ...]
end

then run mix deps.get

@drewolson just a heads up this commit broke queries with preload: c6d1c45

Subqueries cannot contain preloads, so you would still need to remove it, if you wanted to use the subquery syntax. So in the example from the README:

  page =
    MyApp.Person
    |> where([p], p.age > 30)
    |> order_by(desc: :age)
    |> preload(:friends)
    |> MyApp.Repo.paginate(params)

Since we have preload(:friends) in the query (which is used in a subquery for pagination) this error will be raised (Ecto.QueryError) cannot preload associations in subquery in query

As things stand, the order_by clause seems to be run on each individual page.

This is highly problematic. For example, if you try to implement a reddit-style post ordering whereby posts are ordered by vote count, the first result on the second page will potentially have a higher vote count than the last result on the first page.

Example dataset:

Post1 - 3 votes
Post2 - 2 votes
Post3 - 4 votes
Post4 - 5 votes
Post5 - 1 vote
Post6 - 6 votes

Example query with page_size: 3:

def list_posts(params)
  query =
    from p in Post,
    left_join: v in assoc(p, :votes),
    order_by: [desc: count(v.id)],
    group_by: p.id,
    select: p

  Repo.all(query)
end

This returns the results in the correct order.

Post6 - 6 votes
Post4 - 5 votes
Post3 - 4 votes
Post1 - 3 votes
Post2 - 2 votes
Post5 - 1 vote

But if I replace Repo.all with Repo.paginate(query, params), the sort order breaks. Page 1 ends up having:

Post3 - 4 votes
Post1 - 3 votes
Post2 - 2 votes

And page 2 ends up having:

Post6 - 6 votes
Post4 - 5 votes
Post5 - 1 vote

So it looks like Scrivener gets the first 3 results for page 1, and the next 3 results for page 2, and so on. This causes the order_by clause to run only across each page's results, which severely limits the usefulness of this library.

Perhaps a pre_order option is needed whereby paginate grabs all results and then paginates them internally.

Hello, I've found that pagination fails with a query that includes a group_by, it looks like the pagination is calculated without doing this grouping, so the number of pages is greater than expected (and the extra pages are therefore empty).

For example, this query without the group_by will result in 9 pages in my project:

issues = from(i in Issue,
  where: i.project_id == ^project.id) |> Repo.paginate(params)

But if I group the issues, then although only 1 page of results should be available, I still see 9 pages:

issues = from(i in Issue,
  where: i.project_id == ^project.id,
  select: %{ sample_id: min(i.id),
             type: i.type,
             sub_type: i.sub_type,
             message: i.message,
             how_many: count("*") },
  group_by: [i.type, i.sub_type, i.message],
  order_by: [:type, desc: count("*")]) |> Repo.paginate(params)

Not sure I'm doing this right, as I'm new to Elixir but I have the following

 alias Magnify.Magnets
 alias Magnify.Magnets.Magnet

  def index(conn, _params) do
   magnets = Magnets.list_magnets()
    render(conn, "index.html", magnets: magnets)
  end

and when I try to implement scrivener like so

def index(conn, _params) do
    magnets = Magnify.Magnets
           |> Magnify.Repo.paginate(_params)
    render conn, "index.html", magnets: magnets
  end

I'm getting the following error

function MagnifyWeb.MagnetController.init/1 is undefined (module MagnifyWeb.MagnetController is not available)

I have a table with no PK, caused

** (exit) an exception was raised:
    ** (ArgumentError) argument error
        :erlang.hd([])
        lib/scrivener/paginater/ecto/query.ex:35: Scrivener.Paginater.Ecto.Query.total_entries/2
        lib/scrivener/paginater/ecto/query.ex:10: Scrivener.Paginater.Ecto.Query.paginate/2

Offending code

defp total_entries(query, repo) do
    primary_key =
      query.from
      |> elem(1)
      |> apply(:__schema__, [:primary_key])
      |> hd

Already marked the schema with @primary_key false

(UndefinedFunctionError) function Scrivener.Config.new/3 is undefined (module Scrivener.Config is not available)

Scrivener.Config.new(MyApp.Repo, [page_size: 2], %{})
(myapp) lib/myapp/repo.ex:5: Myapp.Repo.paginate/2

my app name is elixirer,

lib/elixirer/repo.ex

defmodule Elixirer.Repo do
    use Ecto.Repo, otp_app: :elixir
    use Scrivener, page_size: 2
end

mix.exs

 defp deps do
    [{:phoenix, "~> 1.2.0"},
     {:phoenix_pubsub, "~> 1.0.0"},
     {:postgrex, ">= 0.0.0"},
     {:phoenix_ecto, "~> 3.0"},
     {:phoenix_html, "~> 2.4"},
     {:phoenix_live_reload, "~> 1.0", only: :dev},
     {:gettext, "~> 0.9"},
     {:cowboy, "~> 1.0"},
     {:comeonin, "~> 2.0"},
     {:qiniu,"~> 0.3.3"},
     {:earmark, "~> 0.2"},
     {:scrivener_ecto, "~> 1.0"},
     {:scrivener_html, "~> 1.1"},
     {:tirexs, "~> 0.8"},
     {:scrivener_elasticsearch, git: "https://github.com/feixionglee/scrivener_elasticsearch.git"},
     {:edeliver, "~> 1.4.0"},
     # {:exrm, ">= 0.16.0", warn_missing: false},
     {:distillery, "~> 0.9"}
    ]
  end

Subj

Pagination should work correctly for queries with deeply nested joins.

Scrivener Ecto doesn't work with OTP releases because it doesn't include Scrivener in its applications of included_applications properties. I fixed it in my project by adding Scrivener to my included_applications.