(Ecto.NoResultsError) expected at least one result but got none in query:

Phoenix Forum Questions / Help
, ,
1

Hello all,

All help and advise is appreciated. I kept on getting the error (Ecto.NoResultsError) expected at least one result but got none in query: .

I tried running the query in the database itself and it works.
SELECT a0.“id”, a0.“airline”, a0.“arrivalcity”, a0.“arrivaldate”, a0.“arrive_time”, a0.“arrivedatetime”, a0.“date”, a0.“depart_time”, a0.“departcity”, a0.“departdate”, a0.“departdatetime”, a0.“flightnumber”, a0.“isconn”, a0.“pnr_id”, a0.“segment_confirmation”, a0.“segmentnumber”, a0.“status”, a0.“tripit_id”, a0.“user_id” FROM “airsegments” AS a0 WHERE a0.“id” = 2000000000

But when I try in my application, I keep on getting the below:
[info] GET /api/airsegments/2000000000
[debug] Processing with ApiWeb.AirSegmentController.show/2
Parameters: %{“id” => “2000000000”}
Pipelines: []
[debug] QUERY OK source=“airsegments” db=3375.0ms queue=47.0ms idle=1703.0ms
SELECT a0.“id”, a0.“airline”, a0.“arrivalcity”, a0.“arrivaldate”, a0.“arrive_time”, a0.“arrivedatetime”, a0.“date”, a0.“depart_time”, a0.“departcity”, a0.“departdate”, a0.“departdatetime”, a0.“flightnumber”, a0.“isconn”, a0.“pnr_id”, a0.“segment_confirmation”, a0.“segmentnumber”, a0.“status”, a0.“tripit_id”, a0.“user_id” FROM “airsegments” AS a0 WHERE (a0.“id” = $1) [2000000000]
[info] Sent 404 in 3546ms
[debug] ** (Ecto.NoResultsError) expected at least one result but got none in query:

from a0 in Api.Itineraries.AirSegment,
where: a0.id == ^“2000000000”

(ecto 3.4.4) lib/ecto/repo/queryable.ex:122: Ecto.Repo.Queryable.one!/3
(api 0.1.0) lib/api_web/controllers/air_segment_controller.ex:24: ApiWeb.AirSegmentController.show/2
(api 0.1.0) lib/api_web/controllers/air_segment_controller.ex:1: ApiWeb.AirSegmentController.action/2
(api 0.1.0) lib/api_web/controllers/air_segment_controller.ex:1: ApiWeb.AirSegmentController.phoenix_controller_pipeline/2
(phoenix 1.4.17) lib/phoenix/router.ex:288: Phoenix.Router.__call__/2
(api 0.1.0) lib/api_web/endpoint.ex:1: ApiWeb.Endpoint.plug_builder_call/2
(api 0.1.0) lib/plug/debugger.ex:132: ApiWeb.Endpoint."call (overridable 3)"/2
(api 0.1.0) lib/api_web/endpoint.ex:1: ApiWeb.Endpoint.call/2
(phoenix 1.4.17) lib/phoenix/endpoint/cowboy2_handler.ex:42: Phoenix.Endpoint.Cowboy2Handler.init/4
(cowboy 2.7.0) c:/projects-api/api-phoenix/deps/cowboy/src/cowboy_handler.erl:41: :cowboy_handler.execute/2
(cowboy 2.7.0) c:/projects-api/api-phoenix/deps/cowboy/src/cowboy_stream_h.erl:320: :cowboy_stream_h.execute/3
(cowboy 2.7.0) c:/projects-api/api-phoenix/deps/cowboy/src/cowboy_stream_h.erl:302: :cowboy_stream_h.request_process/3
(stdlib 3.8) proc_lib.erl:249: :proc_lib.init_p_do_apply/3

The only reason I could think of is that the psql db table data type does not match my schema? This is a table that existed before the schema, so I built the schema around it.
Schema:
field :airline, :string
field :arrivalcity, :string
field :arrivaldate, :date
field :arrive_time, :string
field :arrivedatetime, :naive_datetime
field :date, :string
field :depart_time, :string
field :departcity, :string
field :departdate, :date
field :departdatetime, :naive_datetime
field :flightnumber, :string
field :isconn, :boolean, default: false
field :pnr_id, :integer
field :segment_confirmation, :string
field :segmentnumber, :integer
field :status, :string
field :tripit_id, :string
field :user_id, :integer

Table Structure:
“id”,“bigint”
“pnr_id”,“bigint”
“segmentnumber”,“smallint”
“date”,“character varying”
“flightnumber”,“character varying”
“departdate”,“date”
“departcity”,“character varying”
“arrivalcity”,“character varying”
“arrivaldate”,“date”
“user_id”,“bigint”
“airline”,“character varying”
“tripit_id”,“character varying”
“depart_time”,“character varying”
“arrive_time”,“character varying”
“status”,“character varying”
“departdatetime”,“timestamp without time zone”
“arrivedatetime”,“timestamp without time zone”
“isconn”,“boolean”
“segment_confirmation”,“character varying”

2

The id you are receiving in params is a string, you should convert it to integer before querying the database.

By the way, you can alias your names in the schema to make them more uniform in your Elixir code

    field :arrival_city, :string, source: :arrivalcity
    field :arrival_date, :date, source: :arrivaldate
3

You don’t need to convert, You should get result with id or “id”.

Every controller show action receive id as string, and yet we can use Context.get(id) without convertting.

4

@hectorsq and @kokolegorille I tested the query WHERE a0.“id” = 2000000000 and WHERE a0.“id” = ‘2000000000’ and both worked.

And thank you for the info hectorsq regarding the alias.

5

That SQL looks like it should work; can you try running this to verify that there’s no misconfiguration?

In the DB client that does show a result, run SELECT current_database()

In your application’s iex console, run Ecto.Adapters.SQL.query(YOUR_REPO_MODULE_HERE, "SELECT current_database()") (you’ll need to fill in the name of your repo)

The behavior you’re seeing suggests that those will return different values.

Also to think about: are both database connections using the same credentials? IIRC it’s possible to have row-level access control in Postgres, which is another potential reason why records wouldn’t show up.

6

Thanks for the clarification.

7

Hello,

Thanks for all of the explanation and tips. I’ve added this for my notes.

I figured out the issue, such as rookie mistake. I didn’t realize that I was grabbing data from a live server, and our phoenix program is connected to a test server.

Again, I appreciate all your time and replies!