使用Absinthe的args查询多个表

Question

我目前正在使用GraphQL和Absinthe进行一个项目。我对多个表的查询有问题，该表在WHERE子句中有arg (从GraphQL变量接收)。

我希望我的SQL是这样的：

SELECT p.*
FROM posts p 
JOIN categories c 
ON p.category_id = c.id 
WHERE c.name = <the-GraphQL-arg> AND p.deleted_at IS NULL AND p.is_published IS TRUE

这是我的药方代码：(邮政模型) post.ex

schema "posts" do
  field :body, :string
  field :deleted_at, :date
  field :description, :string
  field :image_url, :string
  field :is_published, :boolean, default: false
  field :title, :string
  belongs_to :category, MyPrj.Post.Category

  timestamps()
end

(类别模型) category.ex

schema "categories" do
  field :name, :string
  field :image_url, :string
  field :title, :string
  has_many :posts, MyPrj.Post.Post

  timestamps()
end

schema.ex

query do
  @desc "Get a list of posts by category"
  field :posts_by_category, list_of(:post) do
    arg :order, type: :sort_order, default_value: :desc
    arg :category_name, non_null(:string)
    arg :limit, :integer
    arg :offset, :integer
    resolve &Resolvers.Posts.posts_by_category/3
  end
end

resolvers/post.ex

def posts_by_category(_, args, _) do
  {:ok, Post.list_posts_by_category!(args)}
end

(凤凰上下文) post.ex

def list_posts_by_category!(criteria) do
  query = from p in Post,
    join: c in Category,
    on: p.category_id == c.id,
    where: p.is_published == true and is_nil(p.deleted_at)

  Enum.reduce(criteria, query, fn
    {:limit, limit}, query ->
      from q in query, limit: ^limit

    {:offset, offset}, query ->
      from q in query, offset: ^offset

    {:order, order}, query ->
      from q in query, order_by: [{^order, :inserted_at}, {^order, :id}]

    {:category_name, category_name}, query ->
      from q in query, where: q.name == ^category_name
  end)
  |> Repo.all()
end

我的GraphQL查询：

query postsByCategory {
  postsByCategory(categoryName: "family", order: DESC) {
    body
    category {
      id
      title
    }
    id
    title
  }
}

但是它返回如下错误：

[error] #PID<0.580.0> running MyPrjWeb.Endpoint (connection #PID<0.579.0>, stream id 1) terminated
Server: localhost:4000 (http)
Request: POST /api
** (exit) an exception was raised:
    ** (Ecto.QueryError) lib/my_prj/post.ex:185: field `name` in `where` does not exist in schema MyPrj.Post.Post in query:

from p in MyPrj.Post.Post,
  join: c in MyPrj.Post.Category,
  on: p.category_id == c.id,
  where: p.is_published == true and is_nil(c.deleted_at),
  where: p.name == ^"family",
  order_by: [desc: p.inserted_at, desc: p.id]

如何将类别中的字段"name“(c.name)输入到list_posts_by_category！(条件)函数的查询中？

非常感谢!

Answer 1

用以下代码解决这个问题：

(凤凰上下文) post.ex

def list_posts_by_category!(criteria) do
  query = from p in Post,
    join: c in Category,
    as: :category,
    on: p.category_id == c.id,
    where: p.is_published == true and is_nil(p.deleted_at)

  Enum.reduce(criteria, query, fn
    {:limit, limit}, query ->
      from q in query, limit: ^limit

    {:offset, offset}, query ->
      from q in query, offset: ^offset

    {:order, order}, query ->
      from q in query, order_by: [{^order, :inserted_at}, {^order, :id}]

    {:category_name, category_name}, query ->
      from [q, category: c] in query, where: c.name == ^category_name
  end)
  |> Repo.all()
end