Looking for reviewers: a howto guide on associations

as you all understood, I have been exposed to Elixir for less than a month, and to Ecto for a fraction of that. coming from SQLAlchemy/Django, I seldom had to write any SQL. anyhow, let’s say it’s refreshing.

I have learned, as I summarized in my proposed tutorial, that migrations are schema-less, and I have experimented with @peerreynders’ suggestions, and written working queries producing input for update_all and insert_all.

@peerreynders, your JOIN, I just tried and managed to write straight into Elixir, for example (but without any where clause):

from(a in "accession",
  join: p in "plant",
  on: p.accession_id==a.id,
  select: [a.code, p.code])

which works just fine. I have not yet integrated them in the text, but I’m trying to keep track, adding todo markers.

I also have not yet experimented with subqueries, which I guess will solve some of the remaining doubts I am exposing in this section.

backing up and summarizing: I had opened that pull request and published this post here, in the hope of getting text reviews, style, content, remarks, whatever, to make that document more useful to the community.

I am very grateful for your comments, which went in a direction which I had not foreseen, and which allowed me to learn other aspects I had missed.

on the pull request, I gave up, because my document became much more of a tutorial than a how-to as I initially thought, and there doesn’t seem to be a place for it in the site. too bad. I’ll keep pushing to my patch-1 branch, and I’m open for hints on how to make this work more visible.

p.s.: (did not try this one)

q = from(p in "plant",
  join: a in "accession",
  on: a.code == fragment(~S"substring(? from '^\d+\.\d+')", p.name),
  update: [set: [accession_id: a.id]])
Botany.Repo.update_all(q, [])

tried, works, and translates to:

UPDATE "plant" AS p0 
  SET "accession_id" = a1."id" 
    FROM "accession" AS a1 
    WHERE (a1."code" = substring(p0."name" from '^\d+\.\d+'))
1 Like