I think this is perhaps straight forward but I can't seem to figure this out.
I have a Model that has the following associations:
has_and_belongs_to_many :locations, join_table: :model_locations
belongs_to :location_from, class_name: "Location", foreign_key: "location_from_id"
belongs_to :location_to, class_name: "Location", foreign_key: "location_to_id"
So model.locations can return 0, 1 or multiple records and model.location_from and model.location_to always present and single records.
What I am looking for is a combined result of all of these. I know there is a convoluted SQL query to do this but it would be nice to have a simple Active Record statement. I have looked at merge() and << but none of these seem to work.
For side reference the SQL out put from the has_and_belongs_to_many:
Location Load (0.6ms) SELECT "locations".* FROM "locations" INNER JOIN "model_locations" ON "locations"."id" = "model_locations"."location_id" WHERE "model_locations"."model_id" = $1 [["model_id", 17]]
The preferred answer is via Active Record but a raw SQL will do the trick too.
UPDATE
Progress added to an answer below - will still accept answers that compliment my answer.
I am half way there (sort of). I added an answer here as to not make the question too long.
Here is my data:
irb(main):169:0> Location.find_by_sql('SELECT "model_locations".* FROM "model_locations"')
Location Load (0.5ms) SELECT "model_locations".* FROM "model_locations"
+----+---------------------+-------------+
| id | model_id | location_id |
+----+---------------------+-------------+
| | 17 | 50 |
| | 17 | 51 |
| | 10 | 24 |
| | 19 | 11 |
| | 19 | 5 |
| | 19 | 51 |
+----+---------------------+-------------+
6 rows in set
irb(main):174:0> Model.select(:id, :location_from_id, :location_to_id)
Model Load (0.7ms) SELECT "models"."id", "models"."location_from_id", "models"."location_to_id" FROM "models"
+----+------------------+----------------+
| id | location_from_id | location_to_id |
+----+------------------+----------------+
| 17 | 1 | 5 |
| 18 | 50 | 24 |
| 10 | 3 | 8 |
| 1 | 50 | 11 |
| 19 | 1 | 5 |
| 20 | 1 | 11 |
| 21 | 11 | 5 |
+----+------------------+----------------+
7 rows in set
So for example:
Model 17 has Locations 50, 51 AND 1 ,5
Location 11 has Models 19, 1 AND 20, 21
So I can find the Model Locations:
'SELECT "locations".* FROM "locations" LEFT JOIN "model_locations" ON "locations"."id" = "model_locations"."location_id" WHERE "model_locations"."model_id" = 17 OR "locations"."id" IN (1,5)'
This works great - I get my 4 locations however I can't get the reverse to work:
'SELECT "models".* FROM "models" WHERE "models"."location_from_id" = 11 OR "models"."location_to_id" = 11 INNER JOIN "model_locations" ON "model_locations"."model_id" = "models"."id" WHERE "models_locations"."location_id" = 11'
This fails at the INNER JOIN:
PG::SyntaxError: ERROR: syntax error at or near "INNER"