Elixir Ecto使用苦艾酒中的args查询多个表

Question

我目前正在一个使用GraphQL和苦艾酒的项目中工作。我在查询多个表时遇到问题，其中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模型(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

(类别模型(类别。例如

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

解析器/post.ex

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

(Phoenix上下文(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

My 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

用以下代码解决了它：

(Phoenix上下文(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