# Trying to join an association with a view, but the view query disappears. What am I doing wrong?

**URL:** <https://discourse.rom-rb.org/t/trying-to-join-an-association-with-a-view-but-the-view-query-disappears-what-am-i-doing-wrong/325>\
**Category:** Issues\
**Created:** [June 26, 2019, 7:54pm UTC](https://discourse.rom-rb.org/t/trying-to-join-an-association-with-a-view-but-the-view-query-disappears-what-am-i-doing-wrong/325 "2019-06-26T19:54:12Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![art-solopov](https://avatars.discourse-cdn.com/v4/letter/a/8e8cbc/32.png) [@art-solopov](https://discourse.rom-rb.org/u/art-solopov)\
**Post date:** [June 26, 2019, 7:54pm UTC](https://discourse.rom-rb.org/t/trying-to-join-an-association-with-a-view-but-the-view-query-disappears-what-am-i-doing-wrong/325/1 "2019-06-26T19:54:12Z")

</div>

Here are my relations:

```ruby
class Clients < ROM::Relation[:sql]
  schema(infer: true) do
    associations do
      has_many :orders
      has_one :order, as: :cart, view: :carts_for_clients, override: true
    end
  end
end

class Orders < ROM::Relation[:sql]
  STATUSES = {
    cart: 0,
    started: 1
  }.freeze

  schema(infer: true) do
    associations do
      belongs_to :client
    end
  end

  def carts
    where(status: STATUSES[:cart])
  end

  def carts_for_clients(_assoc, clients)
    carts.where(client_id: clients.map { |c| c[:id] })
  end
end

```

When I combine them, everything seems hunky-dory. The client gets their cart fetched.

But when I try and join them, I see this:

```auto
 pry(main)> clients.join(:cart)
=> #<Clients name=ROM::Relation::Name(clients) dataset=#<Sequel::Postgres::Dataset: "SELECT \"clients\".\"id\", \"clients\".\"login\", \"clients\".\"display_name\" FROM \"clients\" INNER JOIN \"orders\" ON (\"clients\".\"id\" = \"orders\".\"client_id\") ORDER BY \"clients\".\"id\"">>

```

As you can see, there’s no reference of filtering orders by their status.

And also, if I do `clients.join(:cart).to_a`, I get clients that currently have no carts (but have other orders).

Is there something I’m doing wrong?

---

<div class="post-metadata">

**Author:** ![solnic](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.rom-rb.org/solnic/32/279_2.png) [@solnic](https://discourse.rom-rb.org/u/solnic)\
**Post date:** [June 26, 2019, 8:57pm UTC](https://discourse.rom-rb.org/t/trying-to-join-an-association-with-a-view-but-the-view-query-disappears-what-am-i-doing-wrong/325/2 "2019-06-26T20:57:56Z")

</div>

`Relation#join` does not use association views, these are only used by `Relation#combine`.

---

<div class="post-metadata">

**Author:** ![art-solopov](https://avatars.discourse-cdn.com/v4/letter/a/8e8cbc/32.png) [@art-solopov](https://discourse.rom-rb.org/u/art-solopov)\
**Post date:** [June 26, 2019, 10:18pm UTC](https://discourse.rom-rb.org/t/trying-to-join-an-association-with-a-view-but-the-view-query-disappears-what-am-i-doing-wrong/325/3 "2019-06-26T22:18:26Z")

</div>

Is there any way to join a relation while using the relation view?

---

<div class="post-metadata">

**Author:** ![solnic](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.rom-rb.org/solnic/32/279_2.png) [@solnic](https://discourse.rom-rb.org/u/solnic)\
**Post date:** [June 27, 2019, 9:08am UTC](https://discourse.rom-rb.org/t/trying-to-join-an-association-with-a-view-but-the-view-query-disappears-what-am-i-doing-wrong/325/4 "2019-06-27T09:08:00Z")

</div>

`join` and your view are just methods defined on your relation object, just chain them ie `your_rel.join(:something).some_other_view`.

---

<div class="post-metadata">

**Author:** ![art-solopov](https://avatars.discourse-cdn.com/v4/letter/a/8e8cbc/32.png) [@art-solopov](https://discourse.rom-rb.org/u/art-solopov)\
**Post date:** [June 27, 2019, 10:20am UTC](https://discourse.rom-rb.org/t/trying-to-join-an-association-with-a-view-but-the-view-query-disappears-what-am-i-doing-wrong/325/5 "2019-06-27T10:20:57Z")

</div>

Yeah but this would only work if you don’t need access to the relations that you join to your current relation. For instance, if I do `clients.join(:orders)`, I don’t have access to any `Orders` views.

---

<div class="post-metadata">

**Author:** ![solnic](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.rom-rb.org/solnic/32/279_2.png) [@solnic](https://discourse.rom-rb.org/u/solnic)\
**Post date:** [June 27, 2019, 10:42am UTC](https://discourse.rom-rb.org/t/trying-to-join-an-association-with-a-view-but-the-view-query-disappears-what-am-i-doing-wrong/325/6 "2019-06-27T10:42:05Z")

</div>

Can you explain what are you trying to do exactly?

---

<div class="post-metadata">

**Author:** ![art-solopov](https://avatars.discourse-cdn.com/v4/letter/a/8e8cbc/32.png) [@art-solopov](https://discourse.rom-rb.org/u/art-solopov)\
**Post date:** [June 27, 2019, 11:25am UTC](https://discourse.rom-rb.org/t/trying-to-join-an-association-with-a-view-but-the-view-query-disappears-what-am-i-doing-wrong/325/7 "2019-06-27T11:25:24Z")

</div>

I want something like this query:

```sql
SELECT * FROM clients
INNER JOIN orders
  ON orders.client_id = clients.id AND orders.status = 0

```

But I want to start it from the `clients` relation instead of the `orders` relation because I might want to filter clients as well. And also I’d like to use the view I defined in the `orders` relationship for DRYness.

---

<div class="post-metadata">

**Author:** ![solnic](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.rom-rb.org/solnic/32/279_2.png) [@solnic](https://discourse.rom-rb.org/u/solnic)\
**Post date:** [July 2, 2019, 8:26am UTC](https://discourse.rom-rb.org/t/trying-to-join-an-association-with-a-view-but-the-view-query-disappears-what-am-i-doing-wrong/325/8 "2019-07-02T08:26:36Z")

</div>

OK that’s just a method:

```ruby
clients.join(:orders).where(orders[:status].is(0))

```