Rails activerecord find parent given child model id, when parent has a field which is array of child ids

Question

some help please. I have an Activerecord object called Estate. Inside Estate, there is a field which is an array of ids for another Activerecord object called House. So it looks something like this; Estate ... houses: [an_array_of_house_ids] while House id:value .... House is its own thing with NO ASSOCIATION to Estate. Now if I have an arbitrary house_id, how can I query activerecord to get the Estate in which this house belongs to or if it does not belong to any Estate in my database?

For example lets say the DB looks like this;

Estate
_____________________________________________
| estate_id   |    houses      |other_fileds|
---------------------------------------------
|       1     | [1, 2, 3, 4]   |      .     |
---------------------------------------------
|       2     | [5, 15, 3, 4] |      .     |
---------------------------------------------
|       3     | [6, 7, 8, 4] |      .     |
---------------------------------------------

# The houses fields has arrays of house_ids from the table below

House
_____________________________________________
| house_id    | other_fileds|
---------------------------------------------
|       1     |       .     |
---------------------------------------------
|       2     |       .     |
---------------------------------------------
|       3     |       .     |
---------------------------------------------

Given I have the house_id, let's say house_id=3, what would be the most optimal way to get the estate_id(s) for this house?

Do you have the option of changing the design? An array of ids seems like not the greatest idea. — jvillian, May 21 '19 at 00:27
Which database and is `houses` a real array or some `serialize`d YAML mess? — mu is too short, May 21 '19 at 01:14
https://stackoverflow.com/questions/19925641/check-if-a-postgres-json-array-contains-a-string — max pleaner, May 21 '19 at 02:44
If you're using `serialize :houses` then you're in for some pain, if the `houses` column is an `integer[]` then there are some [array functions and operators](https://www.postgresql.org/docs/current/functions-array.html) that you can use. — mu is too short, May 21 '19 at 03:16

Zaid Annas · Answer 1 · 2019-05-21T10:47:37.883

0

In House class you specify belongs_to association with lambda like this

class House < ApplicationRecord
...
belongs_to :estate, ->(house) { Estate.where("?=ANY(houses)", house.id).first }
...
end

edited May 21 '19 at 10:47

answered May 21 '19 at 10:37

Zaid Annas

87
3

score 0 · Accepted Answer · answered May 21 '19 at 11:11

0

Estate.where("?= ANY(houses)", house_id).ids

Note: where house_id can be your Search Criteria

answered May 21 '19 at 11:11

Ketan Mangukiya

360
1
7

Estate.where("?= ANY(houses)", house.id) worked for me. Thank you much Ketan Mangukiya. – Sylvance May 22 '19 at 01:06
This resource was also helpful [https://edgeguides.rubyonrails.org/active_record_postgresql.html#array](https://edgeguides.rubyonrails.org/active_record_postgresql.html#array) – Sylvance May 22 '19 at 01:08

Rails activerecord find parent given child model id, when parent has a field which is array of child ids

2 Answers2