Many to many relationship in Ecto

[lau...@kabisa.nl]

Hello,

I have a many-to-many relationship defined as follows:

  schema "rates" do

    many_to_many :offers, Offer, join_through: "offers_rates"

  end

  schema "offers" do

    many_to_many :rates, Rate, join_through: "offers_rates"

  end 

The offers_rates table is simply a table that has a offer_id column, and a rate_id column. I am saving records as follows:

    Enum.map rates, fn (rate) ->

      Offer.changeset(%Offer{rates: [rate]}, %{shipment_id: rate.shipment_id, percentage: 1.05})

      |> Repo.insert!()

    end 

For every rate, this is generating an offer. I am association the rates with the offer in the changeset. This seems to work as I can see the join table being filled properly. When I try to fetch them later, the association is empty however:

 offers = Repo.preload(shipment, offers: :rates) 

 %Offer{rates: []} 

Are these the proper ways of association records and fetching them from the database again? Any push in the right direction is greatly appreciated.

Thanks! 

[José Valim]

Try:

 offers = Repo.preload(shipment, [offers: :rates], force: true) 

If the association was already loaded, we won't load over it.

José Valim

www.plataformatec.com.br

Skype: jv.ptec

Founder and Director of R&D

--
You received this message because you are subscribed to the Google Groups "elixir-lang-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elixir-lang-ta...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-lang-talk/90e0e96e-a6ca-4111-b4cc-e9d180b99b6b%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[lau...@kabisa.nl]

Hi José,

Thanks for the fast response. That doesn't seem to do it however. If I do not preload anything, I get an error message saying that the offers are not preloaded. If I preload the offers, it says the rates are not preloaded, and if I preload everything, with force set to true, the rates are still empty. Is there anything else I can try?

Op donderdag 26 mei 2016 17:07:36 UTC+2 schreef José Valim:

[lau...@kabisa.nl]

So I have been digging a little deeper and Ecto is issuing the following query:

SELECT r0."id", *snip*, o1."offer_id" FROM "rates" AS r0 INNER JOIN "offers_rates" AS o1 ON o1."offer_id" = ANY($1) WHERE (o1."rate_id" = r0."id") ORDER BY o1."offer_id" [[<<163, 3, 242, 94, 74, 2, 77, 110, 159, 78, 123, 64, 97, 42, 5, 33>>]]

This comes up as an empty association in my code. When I do the following query in psql however, I do get the result I expect:

SELECT * FROM rates INNER JOIN offers_rates ON offers_rates.offer_id='a303f25e-4a02-4d6e-9f4e-7b40612a0521' WHERE offers_rates.rate_id = rates.id; 

Does this offer some more insight? 

Op donderdag 26 mei 2016 18:09:47 UTC+2 schreef lau...@kabisa.nl:

[José Valim]

Which Ecto version are you using?

José Valim

www.plataformatec.com.br

Skype: jv.ptec

Founder and Director of R&D

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-lang-talk/589094f3-2694-4b15-9d39-03d8e4869682%40googlegroups.com.

[lau...@kabisa.nl]

This is Ecto 2.0.0-rc, through phoenix_ecto 3.0.0-rc.0

Op donderdag 26 mei 2016 19:51:44 UTC+2 schreef José Valim:

[José Valim]

Which RC version? I believe this may have been fixed on more recent RCs.

José Valim

www.plataformatec.com.br

Skype: jv.ptec

Founder and Director of R&D

[lau...@kabisa.nl]

My bad, this is rc-5.

Op donderdag 26 mei 2016 20:19:34 UTC+2 schreef José Valim:

[José Valim]

Can you tell me what is the type of your ID columns in the database and in your migrations?

José Valim

www.plataformatec.com.br

Skype: jv.ptec

Founder and Director of R&D

[lau...@kabisa.nl]

In my migrations I have specified binary_id:

  def change do

    create table(:offers, primary_key: false) do

      add :id, :binary_id, primary_key: true

      add :percentage, :float, null: false

      add :shipment_id, references(:shipments, on_delete: :delete_all, type: :binary_id), null: false

      timestamps

    end

    create index(:offers, [:shipment_id])

  end

It has been mapped to a UUID column in Postgres.

Op donderdag 26 mei 2016 21:03:34 UTC+2 schreef José Valim:

[José Valim]

Sorry, I was supposed to ask in your migrations and schema. :)

There is a chance this is an Ecto bug, can you reproduce this in a public application?

--

[lau...@kabisa.nl]

Sorry it took a while, I had to find some time to setup a public repo and reproduce this. You can find it at https://github.com/lboekhorst/many_to_many. It has two tests, one to assert that a record is added to the join table (passes), and one test that checks if the length of the preloaded list is equal to 1 (fails).

Op donderdag 26 mei 2016 21:53:42 UTC+2 schreef José Valim:

[José Valim]

Can you please open up an issue and link to the repository you created? Thank you!

[lau...@kabisa.nl]

Issue posted, thanks for your help!

Op maandag 30 mei 2016 19:40:12 UTC+2 schreef José Valim: