Cannot join same table multiple times in new version #1144

kaspernj · 2020-08-14T10:01:03Z

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))

The text was updated successfully, but these errors were encountered:

scarroll32 · 2020-11-27T19:12:28Z

That's a pretty extreme query!

kaspernj · 2020-11-28T09:15:37Z

This might be the same issue as #1151 ?

sebaas · 2020-12-01T20:53:14Z

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

kaspernj · 2020-12-01T20:57:03Z

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

sebaas · 2020-12-01T23:50:36Z

@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 · 2020-12-13T13:24:24Z

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 · 2020-12-23T14:44:52Z

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 · 2020-12-23T15:26:18Z

After upgrading Rails version to 6.1.0 it all works well

👍

tbr00 · 2020-12-23T15:39:08Z

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 · 2020-12-23T17:10:00Z

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 · 2020-12-25T21:16:30Z

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 · 2021-01-27T15:14:31Z

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 · 2021-02-18T08:52:35Z

This one is still haunting me :'-(

deivid-rodriguez · 2023-10-23T14:36:03Z

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

sebaas mentioned this issue Dec 2, 2020

Ransack throws an error when doing a query that joins twice to the same table. #1182

Closed

ozzyaaron mentioned this issue Mar 29, 2021

Incorrect references to table aliases in Rails 6.1 #1217

Closed

oneiros mentioned this issue Oct 18, 2023

Fix wrong table aliases (#1217) #1447

Merged

deivid-rodriguez closed this as completed Oct 23, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Cannot join same table multiple times in new version #1144

Cannot join same table multiple times in new version #1144

kaspernj commented Aug 14, 2020

scarroll32 commented Nov 27, 2020

kaspernj commented Nov 28, 2020

sebaas commented Dec 1, 2020

kaspernj commented Dec 1, 2020

sebaas commented Dec 1, 2020 •

edited

Loading

kaspernj commented Dec 13, 2020

prsanjay commented Dec 23, 2020

scarroll32 commented Dec 23, 2020

tbr00 commented Dec 23, 2020 •

edited

Loading

tbr00 commented Dec 23, 2020 •

edited

Loading

tbr00 commented Dec 25, 2020

kaspernj commented Jan 27, 2021 •

edited

Loading

kaspernj commented Feb 18, 2021

deivid-rodriguez commented Oct 23, 2023 •

edited

Loading

Cannot join same table multiple times in new version #1144

Cannot join same table multiple times in new version #1144

Comments

kaspernj commented Aug 14, 2020

scarroll32 commented Nov 27, 2020

kaspernj commented Nov 28, 2020

sebaas commented Dec 1, 2020

kaspernj commented Dec 1, 2020

sebaas commented Dec 1, 2020 • edited Loading

kaspernj commented Dec 13, 2020

prsanjay commented Dec 23, 2020

scarroll32 commented Dec 23, 2020

tbr00 commented Dec 23, 2020 • edited Loading

tbr00 commented Dec 23, 2020 • edited Loading

tbr00 commented Dec 25, 2020

kaspernj commented Jan 27, 2021 • edited Loading

kaspernj commented Feb 18, 2021

deivid-rodriguez commented Oct 23, 2023 • edited Loading

sebaas commented Dec 1, 2020 •

edited

Loading

tbr00 commented Dec 23, 2020 •

edited

Loading

tbr00 commented Dec 23, 2020 •

edited

Loading

kaspernj commented Jan 27, 2021 •

edited

Loading

deivid-rodriguez commented Oct 23, 2023 •

edited

Loading