many_to_many builds wrong query

wdiechmann commented 7 years ago

Environment

Elixir version (elixir -v): Erlang/OTP 19 [erts-8.3] [64-bit] [smp:8:8] - Elixir 1.4.2
Database and version (PostgreSQL 9.4, MongoDB 3.2, etc.): 5.5.39-MariaDB Linux (x86_64)
Ecto version (mix deps): 2.1.4 (ecto) d1ba9328
Database adapter and version (mix deps): 0.8.2 (mariaex) a9ee64a0
Operating system: macOS Sierra 10.12.5

Current behavior

In this thread https://elixirforum.com/t/many-to-many-associations-in-phoenix-and-ecto/1043/10 I detail the schemas ('jobbers', 'jobs', and the many_to_many relationship 'jobbers_jobs')

Whether I say

job = Repo.get_by(Job, id: 1) |> Repo.preload :jobbers

or I say

def index(conn, _params) do
  jobs = Repo.all from job in Job, preload: :jobbers
  render(conn, "index.json", jobs: jobs)
end

each job is blessed with TWO sets of jobbers 😦

Using string primary keys may be the culprit (guestimate) as a broad range of many_to_many tests are readily at hand I should say.

The SQL produced (referring to the first statement above) is:

[debug] QUERY OK source="jobs" db=0.7ms
SELECT j0.`id`, j0.`jobs_id`, j0.`teaser`, j0.`name`, j0.`job_when_name`, j0.`committee_name`, j0.`max_qty_jobbere`, j0.`min_qty_jobbere`, j0.`wanted_qty_jobbere`, j0.`inserted_at`, j0.`updated_at` FROM `jobs` AS j0 WHERE (j0.`id` = ?) [1]
[debug] QUERY OK source="jobbers" db=22.9ms decode=0.1ms
SELECT j0.`id`, j0.`jobbers_id`, j0.`name`, j0.`contact_address`, j0.`email`, j0.`mobile_clean`, j0.`workas_jobber`, j0.`state`, j0.`jobfunc`, j0.`create_date`, j0.`write_date`, j0.`birthdate`, j0.`country_code`, j0.`inserted_at`, j0.`updated_at`, j1.`jobs_id` FROM `jobbers` AS j0 INNER JOIN `jobs` AS j1 ON j1.`jobs_id` IN (?) INNER JOIN `jobbers_jobs` AS j2 ON j2.`jobs_id` = j1.`jobs_id` WHERE (j2.`jobbers_id` = j0.`jobbers_id`) ORDER BY j1.`jobs_id` ["__export__.campos_job_92"]

Expected behavior

SELECT DISTINCT j0.`id`, j0.`jobbers_id`, j0.`name`, j0.`contact_address`, j0.`email`, j0.`mobile_clean`, j0.`workas_jobber`, j0.`state`, j0.`jobfunc`, j0.`create_date`, j0.`write_date`, j0.`birthdate`, j0.`country_code`, j0.`inserted_at`, j0.`updated_at`, j1.`jobs_id` FROM `jobbers` AS j0 INNER JOIN `jobs` AS j1 ON j1.`jobs_id` IN (?) INNER JOIN `jobbers_jobs` AS j2 ON j2.`jobs_id` = j1.`jobs_id` WHERE (j2.`jobbers_id` = j0.`jobbers_id`) ORDER BY j1.`jobs_id` ["__export__.campos_job_92"]

or (at least feasible on the single row query) bypass the product (j0 x j1) alltogether with

SELECT  j0.`id`, j0.`jobbers_id`, j0.`name`, j0.`contact_address`, j0.`email`, j0.`mobile_clean`, j0.`workas_jobber`, j0.`state`, j0.`jobfunc`, j0.`create_date`, j0.`write_date`, j0.`birthdate`, j0.`country_code`, j0.`inserted_at`, j0.`updated_at`, j2.`jobs_id` FROM`jobbers` AS j0, `jobbers_jobs` AS j2 WHERE j2.`jobbers_id`=j0.`jobbers_id` AND j2.`jobs_id` in ("__export__.campos_job_92")

josevalim commented 7 years ago

can you please post the results you get back?

Also, what does Repo.all "jobbers_jobs" returns? Do you have duplicate entries by any chance? --

José Valim www.plataformatec.com.br Skype: jv.ptec Founder and Director of R&D

wdiechmann commented 7 years ago

dammit - José - my bad!

Battled this for nearly 8 days! Verified jobbers, jobbers_jobs - and jobs - every time it matched - when I used DISTINCT

This is no help - if you already has TWO records in the table (which I did have - for one lousy record almost at the bottom of the table of 262 posts - at which time you start to decide that everything is OK!!)

  def build_job([head|tail],current_jobs) do
    changeset = Job.changeset(%Job{},head)
    case Map.fetch(current_jobs,head.jobs_id) do
      {:ok, job} -> Job.changeset(job, head) |> Repo.update
      :error -> Repo.insert(changeset)
    end   
    build_job(tail,current_jobs)
  end

apologies Walther

elixir-ecto / ecto