CODEWITHSUNDEEP
elixirphoenix-frameworkecto
Sasha Fonseca
Sasha Fonseca

Reputation: 2313

Ecto: How to order results from a certain field in a foreign key

So I have a model Ticket which belongs to a User. Every User has many Ticket. So the user_id is a foreign key for every Ticket. How can I build a query that will get me all Tickets ordered by each user's username? I've been trying

    query       = from u in User, 
                                        preload: [:tickets]

    query_tickets = from t in Ticket,
                                        order_by: u.username,
                                        preload: [users: ^query]

    tickets = Repo.all(query_tickets)

But it says the model Ticket doesn't have any User association?

     schema "tickets" do
    field :subject,           :string
    field :body,              :string
    field :followup,          :boolean
    field :closed,            :boolean
    field :file,              :string
    field :filepath,          :map
    belongs_to :user,         UserController
    has_many :ticket_message, TicketMessageController


    timestamps
  end

    schema "users" do 
    field :name,                    :string
    field :username,            :string
    field :password,            :string, virtual: true
    field :password_hash, :string
    field :email,               :string
    field :client,              :string
    field :role,                    :integer
    has_one :services, ServiceController
    has_many :tickets, TicketController

    timestamps
end

Upvotes: 1

Views: 1729

Answers (1)

Gazler
Gazler

Reputation: 84140

You are using preload/3 here, since preload happens after the query (it will fetch all of the associated ids in its own query) you can't sort on the user_id this way.

From the docs:

Repo.all from p in Post, preload: [:comments]

The example above will fetch all posts from the database and then do a separate query returning all comments associated to the given posts.

You have to use a join/5

query_tickets = from t in Ticket,
  join: u in assoc(t, :user)
  order_by: u.username,

tickets = Repo.all(query_tickets)

If you want the users to be set on the user key of the tickets (like you get with preload) then you may want to take a look at https://github.com/elixir-lang/ecto/issues/962

Upvotes: 5

Related Questions