How to look up some fields from the main table and a sub-table with Ecto?

Questions / Help
1

I want to look up some fields from the main table [Guards], and then the main table [Guards] has a sub-table [Tiles] reference association, how should I write this query statement.
I looked up the relevant documentation and wrote the following query, but there is an error

error: unexpectedly reached end of line. The current expression is invalid or incomplete

How should I fix it???

schema "guards" do
	field :name, :string
	field :desc, :string
	field :index, :integer
	field :scene_id, :integer
	field :locked, :boolean
	has_many :tiles, MyApp.Repo.Tiles, foreign_key: :guard_id
end

schema "tiles" do
    field :guard_id, :integer
    field :q, :integer
    field :r, :integer
    field :h, :integer
end

def get_detail(guard_id) do
    query = Ecto.Query.from(g in MyApp.Repo.Guards,
			select: %{
				id: g.id,
				name: g.name,
				desc: g.desc,
				index: g.index,
			}),
			where: g.id == ^guard_id
			join: t in  MyApp.Repo.Tiles,
			on: g.id == t.guard_id,
			# join: t in assoc(g, :tiles),
			preload: [tiles: t]
		
		Friends.Repo.one(query)
end

env: 
  {:ecto_sql, "~> 3.12.0"},
  {:postgrex, "~> 0.19.1"},
  elixir 1.17.3
2

The closing paren here is in the wrong place - it should be after the preload clause

1 Like
3 
query = Ecto.Query.from(g in MyApp.Repo.Guards,
		select: %{
			id: g.id,
			name: g.name,
			desc: g.desc,
			index: g.index
		},
		where: g.id == ^guard_id
		join: t in  MyApp.Repo.Tiles,
		on: g.id == t.guard_id,
		# join: t in assoc(g, :tiles),
		preload: [tiles: t])
	
	Friends.Repo.one(query)

Is that right? But it still has mistakes

  • (Ecto.QueryError) the binding used in from must be selected in select when using preload in query:*
4

You’re asking Ecto to preload records into tiles on the result, but the shape you’ve requested in select doesn’t have any corresponding association.

Consider refactoring your approach to use whole schemas and the already-declared associations. This version keeps the same names as the original:

def get_detail(guard_id) do
  query =
    Ecto.Query.from(
      g in MyApp.Repo.Guards,
      where: g.id == ^guard_id,
      preload: [:tiles]
    )

  Friends.Repo.one(query)
end