在A的另一端查询某些东西，可以通过关联来进行许多

Question

我想通过a的结果过滤许多通过关联查询。我是否必须使用Elixir进行此操作，或者我可以使用ECTO在数据库中完成工作？

设置

mix phx.new shop
cd shop
mix echo.create
mix phx.gen.html Accounting Invoice invoices number issues_on:date
mix phx.gen.html Accounting Product products name category
mix phx.gen.html Accounting LineItem line_items 
                                     invoice_id:references:invoices 
                                     product_id:references:products
mix ecto.migrate

priv/repo/seeds.exs

{:ok, invoice1} = Shop.Accounting.create_invoice(%{number: "1", issued_on: "2017-01-01"})
{:ok, invoice2} = Shop.Accounting.create_invoice(%{number: "2", issued_on: "2017-01-01"})
{:ok, invoice3} = Shop.Accounting.create_invoice(%{number: "3", issued_on: "2017-01-02"})
{:ok, banana} = Shop.Accounting.create_product(%{name: "Banana", category: "Fruits"})
{:ok, potato} = Shop.Accounting.create_product(%{name: "Potato", category: "Vegetables"})
Shop.Accounting.create_line_item(%{invoice_id: invoice1.id, product_id: banana.id})
Shop.Accounting.create_line_item(%{invoice_id: invoice2.id, product_id: banana.id})
Shop.Accounting.create_line_item(%{invoice_id: invoice2.id, product_id: potato.id})
Shop.Accounting.create_line_item(%{invoice_id: invoice3.id, product_id: potato.id})

模式

lib/shop/accounting/invoice.ex

schema "invoices" do
  field :issued_on, :date
  field :number, :string
  has_many :line_items, Shop.Accounting.LineItem
  has_many :products, through: [:line_items, :product]
  timestamps()
end

lib/shop/accounting/line_item.ex

schema "line_items" do
  belongs_to :invoice, Shop.Accounting.Invoice
  belongs_to :product, Shop.Accounting.Product
  timestamps()
end

查询预加载蔬菜的发票

我如何查询从1月1日至1月5日的所有invoices，包括具有category值Vegetables的products。如果他们也有line_items的水果也可以。我只想确保没有只有水果但没有蔬菜的invoice。使用此种子，这将是发票2和3。

我知道如何用长生不老药过滤invoices。但是我想知道我是否可以更快地在数据库中解决此问题。有没有办法用ecto过滤？

import Ecto.Query
alias Shop.Accounting.Invoice
alias Shop.Repo
{:ok, starts_on} = Date.from_erl({2017, 1, 1})
{:ok, ends_on} = Date.from_erl({2017, 1, 5})

query = from i in Invoice, where: i.issued_on >= ^starts_on,
                           where: i.issued_on <= ^ends_on,
                           preload: [:products]
invoices = Repo.all(query)
# Here I would loop through invoices to filter the once 
# with vegetables.

Answer 1

使用Ecto.Query join：https：//hexdocs.pm/ecto/ecto.query.html#join/5

在您的情况下，将是：

query = from i in Invoice, where: i.issued_on >= ^starts_on,
                           where: i.issued_on <= ^ends_on,
                           join: p in assoc(i, :products),
                           where: p ...,
                           preload: [:products]

并在您想要的条件下工作。

另外，当我学习ecto时，我经常使用这些示例：https：//elixirnation.io/references/ecto-query-examples

它们有点过时，但通常很好。