Rails3 and Arel to select using IN and subselect

Question

I have a table called translations. (And a compatible ActiveRecord class). This table contains the following fields id, key and value

I would like to select all translations in which the key matches the given request + all translations that do not match the request, but share the key with the translation that matches the request.

As a result, SQL might look something like this:

SELECT * FROM TRANSLATIONS where key in
    (select key from Translations where value like '%some search%')

I have tried several things, but I cannot figure it out. Any ideas on how to express this in Arela?

Answer 1

Something like this should work:

t = Table(:translations)
c = t.where(t[:value].matches('%some search%')).project(:key)
t.where(t[:key].in(c))

Answer 2

@valodzka, "t [....]" :

t = Table(:translations)
c = t.where(t[:value].matches('%some search%')).project(t[:key])
t.where(t[:key].in(c))