How to use Scrivener Pagination lib in Json api

Questions / Help
,
1

Hello,
The backend and frontend are separate in my project , either backend or frontend are phoenix, at first I want to create Pagination in my project backend which is web service which works with Json, not render Html.

Step One

my code for getting all category’s info in my project is :

query = from u in "cms_post_category",
					where: u.group_acl in ^group_acl,
					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.all(query)

Now how do I edit it for Pagination ? for example I edited it like this which have an 500 error :

def load_all_category_info(group_acl, pagenumber) do
		query = from u in "cms_post_category",
					where: u.group_acl in ^group_acl,
					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, pagenumber)
	end

I think I am mistaken because I have an 500 error , based on its source https://github.com/drewolson/scrivener_ecto , page number should be a map , but which map’s params does its dock mean ? what do I write in it ?

my error :

** (exit) an exception was raised:
    ** (Protocol.UndefinedError) protocol Enumerable not implemented for "2". This protocol is implemented for: DBConnection.PrepareStream, DBConnection.Stream, Date.Range, Ecto.Adapters.SQL.Stream, File.Stream, Function, GenEvent.Stream, HashDict, HashSet, IO.Stream, List, Map, MapSet, Postgrex.Stream, Range, Stream

Step Two

After your guidance which helps me to fix this , what should I do in the frontend of my project which receives Json from my backend Json creator ?

Please help me to fix this . Unfortunately, all of tutorials which exist in internet speak about Render Html in Phoenix , not Json api.

2

Maybe try using page: ... (as shown in the docs) as Repo.paginate params?

def load_all_category_info(group_acl, pagenumber) do
		query = from u in "cms_post_category",
					where: u.group_acl in ^group_acl,
					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
3

I have tested it , but it has not worked fo me.

	def load_all_category_info(["admin"], pagenumber) do
		query = from u in "cms_post_category",
					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,
					}
		page = Repo.paginate(query, page: pagenumber)

pagenumber == 2 for example

terminal error :

** (exit) an exception was raised:
    ** (Ecto.SubQueryError) 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 c in subquery(from c in "cms_post_category"),
  select: count("*")
4

page = Repo.paginate(query, %{page: pagenumber})
or
page = Repo.paginate(query, %{"page" => pagenumber})

5

unfortunately, it didn’t works ,

my controller :

   def info_category_all_post(conn, %{"group_acl" => group_acl}) do
      
      info_category_all_posts =  case PostQuery.load_all_category_info(string_to_array(group_acl), "2") do
         [] -> 
            conn
            |> put_status(404)
            |> json(%{error_code: "404", msg: "Categories haven't been found."})
         n  ->
            conn
            |> put_status(200)
            |> json(%{categories: n})
      end
      info_category_all_posts
   end

and my db’s code :

	def load_all_category_info(["admin"], pagenumber) do
		IO.puts "++++++++++++"
		query = from u in "cms_post_category",
					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,
					}
		page = Repo.paginate(query, %{page: pagenumber})
		IO.inspect page

	end

23%20pm
23%20pm1440×900 182 KB

6

Then something wrong with query. That query works without scrievener ?

I checked in my app schema names gave error. You should use module names. So from u in "cms_post_category" should be from u in YourApp.Context.Module

1 Like
7

Hello, yes it works without scrievener

I do what you said, and now I think it works , why does it have the behavior ?

I have another question. Why did the select grow so big? it did shorted before pagination?

please see blow :

SELECT count('*') FROM (SELECT c0."id" AS "id", c0."title" AS "title", c0."status" AS "status", c0."language" AS "language", c0."group_acl" AS "group_acl", c0."description" AS "description", c0."seo_alias_link" AS "seo_alias_link", c0."seo_words" AS "seo_words", c0."seo_description" AS "seo_description", c0."seo_language" AS "seo_language", c0."seo_language_link" AS "seo_language_link", c0."pic_x1_link" AS "pic_x1_link", c0."pic_x2_link" AS "pic_x2_link", c0."pic_x3_link" AS "pic_x3_link", c0."inserted_at" AS "inserted_at", c0."updated_at" AS "updated_at" FROM "cms_post_category" AS c0) AS s0 []
[debug] QUERY OK source="cms_post_category" db=8.1ms decode=0.8ms queue=0.1ms
SELECT c0."title", c0."status", c0."language", c0."group_acl", c0."description", c0."seo_alias_link", c0."seo_words", c0."seo_description", c0."seo_language", c0."seo_language_link", c0."pic_x1_link", c0."pic_x2_link", c0."pic_x3_link" FROM "cms_post_category" AS c0 ORDER BY c0."inserted_at" LIMIT $1 OFFSET $2 [2, 0]

Now my query has 2 time for loading.

08%20pm
08%20pm1382×197 23.1 KB

Thank you.

8

Maybe a bug. Yes same 2 query, i dont know why scrivener needs 2 query. Maybe other developers can explain to us.

edit: After search on google i found that question.

1 Like