Need help with basic db query

Hi all,

So I have this function that takes a truck_load(which has fulfillment_authorization.driver_contract_id property) and renders some info:

  defp get_driver_current_status(truck_load) do

    current_status =
      TSS.CycleTime.DriverCycleStatus.get_driver_status(
        truck_load.fulfillment_authorization.driver_contract_id
      )

    "#{current_status}"
  end

It calls the get_driver_status function, which takes a driver_contract_id. This function queries the db model DriverCycleStatuses, which has a driver_contract_id field and a status field. I’m just trying to find the instance where driver_contract_id matches the passed in driver_contract_id, and return that instances’s status.

Right now, my get_driver_status function looks like this:

  def get_driver_status(driver_contract_id)
      when not is_nil(driver_contract_id) do
    DriverCycleStatus
    |> where([status], status.driver_contract_id == ^driver_contract_id)
    |> Repo.one()
  end

But I am getting an error saying that status is not defined. Is this the correct way to write a db query?

Thank you!!

Here is an example from one of my apps:

def get_title(post_id) do
  from(p in Post,
    where: p.id == ^post_id,
    select: p.title
  )
  |> Repo.one()
end

This query only selects the title in this case. If I want the whole Post it is even simpler:

def get_post(post_id), do: Repo.get(Post, post_id)

Hope this helps. Let me know if you have more questions.

1 Like

Can you post the specific error message? At first glance, this makes me wonder if you have something shadowing the where macro from Ecto.Query, since code like [status], status.driver_contract_id... shouldn’t ever be “evaluated” exactly…

1 Like

Thank you so much!! Based on that, I’ve changed my function to look like this:

  def get_driver_status(driver_contract_id) do
    from(d in DriverCycleStatus,
      where: d.driver_contract_id == ^driver_contract_id,
      select: d.status
    )
    |> Repo.one()
  end

But I’m getting the following errors:

variable "d" does not exist and is being expanded to "d()", please use parentheses to remove the ambiguity or change the variable name

(CompileError) cannot use ^driver_contract_id outside of match clauses

Not sure how to fix these…

Thanks for the help!! please see my to APB9785 above, after changing my function based on his suggestions, I am still getting the similar errors to before:

variable "d" does not exist and is being expanded to "d()", please use parentheses to remove the ambiguity or change the variable name

(CompileError) cannot use ^driver_contract_id outside of match clauses

Hmm… That’s strange. Is this function inside a DriverCycleStatus context, with import Ecto.Query included at the top?

2 Likes

wow, the issue was that import Ecto.Query query wasn’t at the top :sweat_smile: thank you sm!! you are a life saver!

Also, with my get_driver_status function, if I want to return d.status and other properties, like d.cycle_seconds_remaining and d.last_available, how could I do that? I tried doing multiple selects and it didn’t work…

There are several ways to do this, but easiest is probably just a list:

select: [d.status, d.cycle_seconds_remaining, d.last_available]

And for future reference, the Ecto documentation is very helpful to learn the syntax (Ecto.Query - select/3)

2 Likes

awesome thank you! really appreciate the help