Cannot join same table multiple times in new version

[kaspernj]

I use Ransack like this:

Contact.ransack(organization_commune_id_or_person_school_commune_id_eq_any: [id_of_school])

In the gem version from January the generated query looks like this (and works):

SELECT 
  "contacts"."contact_type", "contacts"."deleted_at", "contacts"."id", "contacts"."name", "contacts"."organization_id", "contacts"."person_id", "contacts"."state", "contacts"."student_council_id", "contacts"."updated_at" 
  
FROM 
  "contacts" 
  
LEFT OUTER JOIN "organizations" ON 
  "organizations"."id" = "contacts"."organization_id" 
  
LEFT OUTER JOIN "people" ON 
  "people"."id" = "contacts"."person_id" 
  
LEFT OUTER JOIN "organizations" "organizations_contacts" ON 
  "organizations_contacts"."id" = "contacts"."organization_id" 
  
LEFT OUTER JOIN "contacts" "contacts_organizations_join" ON 
  "contacts_organizations_join"."organization_id" = "organizations"."id" 
  
LEFT OUTER JOIN "contact_relationships" ON 
  "contact_relationships"."relationship_type" = $1 AND 
  "contact_relationships"."child_id" = "contacts_organizations_join"."id" 
  
LEFT OUTER JOIN "contacts" "commune_contacts_organizations_join" ON 
  "commune_contacts_organizations_join"."id" = "contact_relationships"."parent_id" 
  
LEFT OUTER JOIN "organizations" "communes_organizations" ON 
  "communes_organizations"."id" = "commune_contacts_organizations_join"."organization_id" 
  
LEFT OUTER JOIN "people" "people_contacts" ON 
  "people_contacts"."id" = "contacts"."person_id" 
  
LEFT OUTER JOIN "organizations" "schools_people" ON 
  "schools_people"."id" = "people"."school_id" 
  
LEFT OUTER JOIN "contacts" "contacts_organizations_join_2" ON 
  "contacts_organizations_join_2"."organization_id" = "schools_people"."id" 
  
LEFT OUTER JOIN "contact_relationships" "commune_relationships_organizations_join" ON 
  "commune_relationships_organizations_join"."relationship_type" = $2 AND 
  "commune_relationships_organizations_join"."child_id" = "contacts_organizations_join_2"."id" 
  
LEFT OUTER JOIN "contacts" "commune_contacts_organizations_join_2" ON 
  "commune_contacts_organizations_join_2"."id" = "commune_relationships_organizations_join"."parent_id" 
  
LEFT OUTER JOIN "organizations" "communes_organizations_2" ON 
  "communes_organizations_2"."id" = "commune_contacts_organizations_join_2"."organization_id" 
  
WHERE 
  (("communes_organizations"."id" = 1045) OR ("communes_organizations_2"."id" = 1045)) 
  
GROUP BY 
  "contacts"."id", contacts.contact_type, contacts.deleted_at, contacts.id, contacts.name, contacts.organization_id, contacts.person_id, contacts.state, contacts.student_council_id, contacts.updated_at 
  
LIMIT $3 
OFFSET $4

Notice the extra joins and how this line looks: (("communes_organizations"."id" = 1045) OR ("communes_organizations_2"."id" = 1045))

Here is how it looks under the current master (and doesn't work):

SELECT 
  "contacts"."contact_type", "contacts"."deleted_at", "contacts"."id", "contacts"."name", "contacts"."organization_id", "contacts"."person_id", "contacts"."state", "contacts"."student_council_id", "contacts"."updated_at" 
  
FROM 
  "contacts" 
  
LEFT OUTER JOIN "organizations" ON 
  "organizations"."id" = "contacts"."organization_id" 
  
LEFT OUTER JOIN "people" ON 
  "people"."id" = "contacts"."person_id" 
  
LEFT OUTER JOIN "organizations" "organizations_contacts" ON 
  "organizations_contacts"."id" = "contacts"."organization_id" 
  
LEFT OUTER JOIN "contacts" "contacts_organizations_join" ON 
  "contacts_organizations_join"."organization_id" = "organizations"."id" 
  
LEFT OUTER JOIN "contact_relationships" ON 
  "contact_relationships"."relationship_type" = $1 AND 
  "contact_relationships"."child_id" = "contacts_organizations_join"."id" 
  
LEFT OUTER JOIN "contacts" "commune_contacts_organizations_join" ON 
  "commune_contacts_organizations_join"."id" = "contact_relationships"."parent_id" 
  
LEFT OUTER JOIN "organizations" "communes_organizations" ON 
  "communes_organizations"."id" = "commune_contacts_organizations_join"."organization_id" 
  
LEFT OUTER JOIN "people" "people_contacts" ON 
  "people_contacts"."id" = "contacts"."person_id" 
  
LEFT OUTER JOIN "organizations" "schools_people" ON 
  "schools_people"."id" = "people"."school_id" 
  
WHERE 
  (("communes_organizations"."id" = 1029) OR ("communes_organizations"."id" = 1029)) 
  
GROUP BY 
  "contacts"."id", contacts.contact_type, contacts.deleted_at, contacts.id, contacts.name, contacts.organization_id, contacts.person_id, contacts.state, contacts.student_council_id, contacts.updated_at 
  
LIMIT $2 
OFFSET $3

Notice the missing extra joins and the same line which now uses the same relation instead of two different ones: (("communes_organizations"."id" = 1029) OR ("communes_organizations"."id" = 1029))

[scarroll32]

That's a pretty extreme query!

[kaspernj]

This might be the same issue as #1151 ?

[sebaas]

I am experiencing the same issue Is there a workaround for this? It would be much appreciated.

[kaspernj]

@sebaas It works for me in version 2.3.2 :-)

[sebaas]

@kaspernj The 2.3.2 and the 2.40 both fail. It fails, both, by not calling the correct table name when a the same table is joined twice, the first join has not an alias but the alias it is written in the where clause of the query, while the second join has the alias and it is well referenced by the where clause built in the query. Again if I switch the order of the params, the first join name get "guessed" wrong.

[kaspernj]

Here is another one. Notice in the last join it suddenly starts calling "feedback_elements" for "element_feedbacks_feedback_elements".

FeedbackElement.ransack("step_id_eq"=>148, "s"=>"element_feedback_element_position").result.to_sql

SELECT
  "feedback_elements".*

FROM
  "feedback_elements"

LEFT OUTER JOIN "element_feedbacks" ON
  "element_feedbacks"."id" = "feedback_elements"."element_feedback_id"

LEFT OUTER JOIN "elements" ON
  "elements"."id" = "element_feedbacks"."element_id"

LEFT OUTER JOIN "steps" ON
  "steps"."id" = "elements"."step_id"

LEFT OUTER JOIN "elements" "elements_element_feedbacks" ON
  "elements_element_feedbacks"."id" = "element_feedbacks_feedback_elements"."element_id"

WHERE
  "steps"."id" = 148 

ORDER BY
  "elements_element_feedbacks"."position" ASC

Here is an ActiveRecord version that works:

FeedbackElement.left_outer_joins(:step, element_feedback: :element).where(steps: {id: 148}).order("elements_element_feedbacks.position").to_sql

SELECT
  "feedback_elements".*

FROM
  "feedback_elements"

LEFT OUTER JOIN "element_feedbacks" ON
  "element_feedbacks"."id" = "feedback_elements"."element_feedback_id"

LEFT OUTER JOIN "elements" ON
  "elements"."id" = "element_feedbacks"."element_id"

LEFT OUTER JOIN "steps" ON
  "steps"."id" = "elements"."step_id"

LEFT OUTER JOIN "elements" "elements_element_feedbacks" ON
  "elements_element_feedbacks"."id" = "element_feedbacks"."element_id" WHERE "steps"."id" = 148

ORDER BY
  elements_element_feedbacks.position

[prsanjay]

I have faced the same issue with Rails 6.0.2 and Ransack 2.4.0

After upgrading Rails version to 6.1.0 it all works well

[scarroll32]

After upgrading Rails version to 6.1.0 it all works well

👍

[tbr00]

In my case after upgrading to 6.1.0 I no longer get an error, but I still also do not get the expected result - the search just comes back empty, so there is still an issue.
A sample query generated with 6.1.0 looks like:

SELECT "sources".* FROM "sources" LEFT OUTER JOIN "sourcetags" ON "sourcetags"."source_id" = "sources"."id" LEFT OUTER JOIN "tags" ON "tags"."id" = "sourcetags"."tag_id" LEFT OUTER JOIN "tags" "tag2s_sources" ON "tag2s_sources"."id" = "sourcetags"."tag_id" WHERE ("sources"."title" ILIKE '%a/%' AND "tags"."tag" ILIKE '%plan%' AND "tag2s_sources"."tag" ILIKE '%elevation%') ORDER BY "sources"."title" ASC LIMIT $1 OFFSET $2 [["LIMIT", 10], ["OFFSET", 0]]

6.0.0 ransack 2.4.0 works as desired/expected and the same query generates:

SELECT "sources".* FROM "sources" LEFT OUTER JOIN "sourcetags" ON "sourcetags"."source_id" = "sources"."id" LEFT OUTER JOIN "tags" ON "tags"."id" = "sourcetags"."tag_id" LEFT OUTER JOIN "sourcetags" "sourcetags_sources_join" ON "sourcetags_sources_join"."source_id" = "sources"."id" LEFT OUTER JOIN "tags" "tag2s_sources" ON "tag2s_sources"."id" = "sourcetags_sources_join"."tag_id" WHERE ("sources"."title" ILIKE '%a/%' AND "tags"."tag" ILIKE '%plan%' AND "tag2s_sources"."tag" ILIKE '%elevation%') ORDER BY "sources"."title" ASC LIMIT $1 OFFSET $2 [["LIMIT", 10], ["OFFSET", 0]]

So 6.1.0 is using one less JOIN which actually looks OK. I don't see what's wrong with the 6.1.0 query, but it definitely does not do what I expect.

[tbr00]

Having thought about this some more it is clear what the problem is here. I have

class Source < ApplicationRecord has_many :sourcetags, dependent: :destroy, inverse_of: :source has_many :tags, through: :sourcetags has_many :tag2s, foreign_key: 'tag_id', through: :sourcetags, source: :tag

If the 6.1.0 query only joins with the sourcetags table once it will never generate a row with two distinct tags. So the additional JOIN in the 6.0.0 case is both correct and necessary.

[tbr00]

I have a solution working with 6.1.0. I changed the model file to have:

class Source < ApplicationRecord has_many :sourcetags, dependent: :destroy, inverse_of: :source has_many :tags, through: :sourcetags < ApplicationRecord has_many :sourcetag2s, class_name: 'Sourcetag', foreign_key: 'source_id', inverse_of: :source has_many :tag2s, foreign_key: 'tag_id', through: :sourcetag2s, source: :tag

and now the query is:

SELECT "sources".* FROM "sources" LEFT OUTER JOIN "sourcetags" ON "sourcetags"."source_id" = "sources"."id" LEFT OUTER JOIN "tags" ON "tags"."id" = "sourcetags"."tag_id" LEFT OUTER JOIN "sourcetags" "sourcetag2s_sources_join" ON "sourcetag2s_sources_join"."source_id" = "sources"."id" LEFT OUTER JOIN "tags" "tag2s_sources" ON "tag2s_sources"."id" = "sourcetag2s_sources_join"."tag_id" WHERE ("sources"."title" ILIKE '%a/%' AND "tags"."tag" ILIKE '%plan%' AND "tag2s_sources"."tag" ILIKE '%elevation%') ORDER BY "sources"."title" ASC LIMIT $1 OFFSET $2 [["LIMIT", 10], ["OFFSET", 0]]

which returns the same result I had with 6.0.0.

[kaspernj]

I still have issues running against the master version here on Github (currently Ransack 2.4.2) and Rails 6.1.1:

PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "element_feedbacks_feedback_elements"
LINE 1: ..._id" = $7 AND "elements_element_feedbacks"."id" = "element_f...

SELECT
  feedback_elements.*

FROM
  "feedback_elements"

LEFT OUTER JOIN "feedback_steps" ON
  "feedback_steps"."tenant_id" = $1 AND 
  "feedback_steps"."id" = "feedback_elements"."feedback_step_id"

LEFT OUTER JOIN "feedback_surveys" ON
  "feedback_surveys"."tenant_id" = $2 AND
  "feedback_surveys"."id" = "feedback_steps"."feedback_survey_id"

LEFT OUTER JOIN "feedback_assignments" ON
  "feedback_assignments"."tenant_id" = $3 AND
  "feedback_assignments"."id" = "feedback_surveys"."feedback_assignment_id"

LEFT OUTER JOIN "element_feedbacks" ON
  "element_feedbacks"."tenant_id" = $4 AND
  "element_feedbacks"."id" = "feedback_elements"."element_feedback_id"

LEFT OUTER JOIN "elements" ON
  "elements"."tenant_id" = $5 AND
  "elements"."id" = "element_feedbacks"."element_id"

LEFT OUTER JOIN "steps" ON
  "steps"."tenant_id" = $6 AND
  "steps"."id" = "elements"."step_id"

LEFT OUTER JOIN "elements" "elements_element_feedbacks" ON
  "elements_element_feedbacks"."tenant_id" = $7 AND
  "elements_element_feedbacks"."id" = "element_feedbacks_feedback_elements"."element_id"

WHERE
  "feedback_elements"."tenant_id" = $8 AND
  (
    "feedback_assignments"."team_survey_to_feedback_id" = 'c37bb95e-a5e7-4953-b4ba-31577b9563f9' AND
    "steps"."id" = '04f3ca6f-c1bb-45bb-b760-dc68a2df2df7'
  )

ORDER BY
  "elements_element_feedbacks"."position" ASC

Notice that element_feedbacks_feedback_elements isn't joined anywhere.

[kaspernj]

This one is still haunting me :'-(

[deivid-rodriguez]

#1447 is likely to have fixed this, feel free to try next version!