Ecto preload returning the wrong record?

Questions / Help
,
1

I’ve come across an Ecto behavior that I don’t understand. Given these two schemas:

  schema "attribute" do
    field :category, :string
    field :attribute, :string
    field :value, :string
  end

  schema "sample" do
    field :samp_well_name, :string
    field :samp_name, :string
    has_one :samp_type_id, Attribute, foreign_key: :id
  end

The attribute table serves as “standard vocabulary” table, that is, rather than storing strings in a table, the id of the attribute is stored. The samp_type_id field in the sample table is an attribute.

Given the above, I execute the following query :

query = from s in Sample,
        where: s.id == 268, preload: [:samp_type_id]

results = Repo.one(query)

The problem is that the samp_type_id field does not have the correct attribute value. The debug information is shown below.

[debug] QUERY OK source="sample" db=0.0ms                                                                  
SELECT s0."id", s0."source_mat_id", s0."samp_well_name", s0."samp_description", s0."samp_comment", s0."samp
_name", s0."samp_name_alias" FROM "sample" AS s0 WHERE (s0."id" = 268) []                                  
[debug] QUERY OK source="attribute" db=0.0ms                                                               
SELECT a0."id", a0."category", a0."attribute", a0."value", a0."id" FROM "attribute" AS a0 WHERE (a0."id" = 
$1) [268]                                                                                                  
%Metahcr.Browse.Sample{                                                                                    
  __meta__: #Ecto.Schema.Metadata<:loaded, "sample">,                                                      
  analyses: #Ecto.Association.NotLoaded<association :analyses is not loaded>,                              
  id: 268,                                                                                                 
  investigation: #Ecto.Association.NotLoaded<association :investigation is not loaded>,                    
  investigation_sample: #Ecto.Association.NotLoaded<association :investigation_sample is not loaded>,      
  samp_comment: "",                                                                                        
  samp_description: "Sample QHO-B from Qinghuang field",                                                   
  samp_name: "QHO-B",                                                                                      
  samp_name_alias: "",                                                                                     
  samp_type_id: %Metahcr.Browse.Attribute{                                                                 
    __meta__: #Ecto.Schema.Metadata<:loaded, "attribute">,                                                 
    attribute: "material_internal_external",                                                               
    category: "sample",                                                                                    
    id: 268,                                                                                                
    value: "external"                                                                                      
  },                                                                                                        
  samp_well_name: "",                                                                                       
  source_mat_id: "QIN.PW.QHO-B"

The first query returns the correct sample with an id equal to 268. But the second query, which is supposed to be accessing the attribute table with the value of samp_type_id is, instead, using the id of the sample record.

I also tried performing the query with Repo.preload with the same results:

r = Repo.one query
Repo.preload r

I can see what is happening in the debug output but I don’t understand why it is using the sample id instead of the value in the samp_type_id field (I double-checked and the value of this field is 216). I specified that the foreign_key is :id because I know that the query will construct a where clause with the name sample_id, which is not the name of the id in Attribute.

Finally, as I understand it, I don’t think I can use use a belongs_to in the Attribute schema because Attributes are used in many other tables. And, it is often the case that a record will have multiple Attribute fields.

Does anyone see what I am doing wrong?

Thanks very much.

2

has_one is used for “reverse” lookups. It will look at the other table and find that record which has the same ID in the specified :foreign_key-column. So it uses the attribute.id as a reference to your sample.id.

What you actually want is belongs_to. This will have a column in the schema it is defined in which links to the primary key of the other schema.

But this is much better explained in the docs for belongs_to/3

3

Thanks.

Thanks very much for your help.

I changed

has_one :samp_type_id, Attribute, foreign_key: :id

to

belongs_to :samp_type, Attribute, references: :id

It preloads correctly now :grinning:

I was also getting confused with where the foreign key was, thinking that it was in the Attribute table. I let Ecto generate the samp_type_id field in Sample.

Thanks again.

2 Likes