Joins with multiple tables/constraints OR has_one_through with secondary constraint

Question

I apologise for the contrived example

A person has ONE journey but is not directly connected to that journey, instead, they are connected via their house and office.

A person belongs_to a house, a house has_many people

A person belongs_to a office, a office has_many people

A journey belongs_to a house, a house has many journeys

A journey belongs_to a office, a office has many journeys

                         +---------+
       +---Belongs-To---->         <----Belongs-To--+
       |                 |  House  |                |
       |  +-Has-Many-----+         +-----Has-Many-+ |
       |  |              +---------+              | |
       |  |                                       | |
+------+--v+                                     +v-+--------+
|          |                                     |           |
|  Person  |                                     |  Journey  |
|          |                                     |           |
+------+--^+                                     +^-+--------+
       |  |                                       | |
       |  |              +----------+             | |
       |  +-Has-Many-----+          +----Has-Many-+ |
       |                 |  Office  |               |
       +----Belongs-To--->          <---Belongs-To--+
                         +----------+

Following that contrived example, what's the best way to allow the following:

person.journey

OR baring that a Join query with multiple tables (using ruby hashes) or using a has_one_through with extra table constraints.

We do have a sql query but we'd rather avoid using raw sql if we can, but it looks like this:

Person
.joins('INNER JOIN journeys
        ON journeys.office_id = person.office_id
        AND journeys.house_id = person.house_id')

A `person` belongs to an `office`, and `office` has many `journeys`. So, it looks to me that a `person` can have more than one `journeys`. It shouldn't be `person.journey`, but more like `person.journeys`. Even the association through `house` confirms this. — Jagdeep Singh, Jul 06 '18 at 11:23
When done through either `office` or `house` you're right, but when done through both there's only one `journey` per `person`. — Thermatix, Jul 06 '18 at 11:45

iGian · Accepted Answer · 2018-07-12T18:16:32.913

1

I'm sorry I must use an answer to get more info. Is it possible to have this case?

| Person                      |   | Journey                     |
|----+-----------+------------|   |----+-----------+------------|
| id | office_id | house_id   |   | id | office_id | house_id   |
|----+-----------+------------|   |----+-----------+------------|
| 1  | 1         | 1          |   | 1  | 1         | 1          |
| 2  | 1         | 1          |   | 2  | 1         | 1          |
| 3  | 1         | 1          |   | 3  | 1         | 1          |
| 4  | 1         | 1          |   | 4  | 1         | 1          |

How can you find the journey of a specific person on the data of this case?

If you consider person = Person.find(1) and use person.office_id and person.house_id as keys to look up in the table Journey, you will fetch ids 1,2,3 and 4. Not just one journey.

Following your replies, this case never happens because validation filters doe not allow. So, what's required is to access the Journey table with a double foreign key: office_id and house_id.

The best solution would be

class Person < ActiveRecord::Base
  belongs_to :office
  belongs_to :house

  has_one :journey, foreign_keys: [:house_id, :office_id]

end

but multiple foreign key is not yet supported by rails.

One possible workaround could be defining the instance method journey for the Person class:

class Person < ActiveRecord::Base
  belongs_to :office
  belongs_to :house

  def journey
    Journey.where(office_id: office_id, house_id: house_id).last
  end

end

So you can call person.journey.

edited Jul 12 '18 at 18:16

answered Jul 08 '18 at 16:09

iGian

11,023
3
21
36

A `person` has one `journey` becuase a `person` and `journey` can only have both one `office_id` and `house_id` each, by matching a `person` to a `journey` by the `office_id` and `house_id` that they both share, you can match a `journey` to a `person`. – Thermatix Jul 11 '18 at 08:25
I'll rename the examples if it helps but in this case a person and journey can never be assigned to a office or house more then once, I realise this is a contrived example (as I said) but this is how this particular system is designed. – Thermatix Jul 11 '18 at 08:34
Maybe I'm missing something.. But if you look at my case: **-1-** no person has more than one office; **-2-** no person has more than one house; **-3-** no journey has more than one office; **-4-** no journey has more than one house. How can you find the journey of a specific person the data of my case? (I edited my post). – iGian Jul 12 '18 at 05:12
That case will never happen, there is a uniqueness validation, I did say this is a contrived example but the basics are the same, I guess I should have used other words for the example. – Thermatix Jul 12 '18 at 09:48
Well, if you want to paste my the tables to your original post, changing the `office_id` and `house_id` values according to possible real data and also adding the expected result of the query, maybe somebody or myself can try to help ;) – iGian Jul 12 '18 at 12:15
Edited with a possible solution. Sorry if I've been stubborn, but I had to settle my mind. – iGian Jul 12 '18 at 18:15

Joins with multiple tables/constraints OR has_one_through with secondary constraint

1 Answers1