0

I have the following query that I was trying get using the ActiveRecord Query Interface:

SELECT *, null, null, null FROM model A
    WHERE A.column IN ( 2, 3 )
    UNION SELECT * FROM model A2 INNER JOIN other_model B ON B.other_column = A2.id
    WHERE B.column IN ( 2, 3 );

The null in the select is because of the different number of columns in the two tables.

I want to get all the records from model plus the records from model joined with other_model even if the record is already in the first query.

I tried to use only AR with Model.joins() but It returned me only the second part of the query, plus I'd like to do only one query. I've tried using Arel too, but I'm not much familiar with It and not worked.

I ended up with Model.find_by_sql('raw_query') but I'm sure that's a Rails way to achieve the same result. Could some one help me?

Alexandre Bonicelli
  • 115
  • 1
  • 7
  • Take a look at http://stackoverflow.com/questions/6686920/activerecord-query-union . It's an elegant way to create a `union_scope` method that will work to join multiple queries on the same model. I have used it for my projects. For you is `model` the same in both queries? For the `union_scope` to work, the models need to be the same. Since you are adding `nulls` it seems like they are different. – Derek Apr 29 '15 at 05:24
  • Thanks! I'll take a look. And yes, `model` is the same in both queries, but the number of columns returned by the second `select` is different because of the `join`. Hence the `nulls`. – Alexandre Bonicelli Apr 29 '15 at 14:01
  • ok, it will work fine then. If you use AR it should by default just do a `select * from table_for_model` and the columns should match. If you need the extra columns, you will need to make them match and they should have the same column names. You may need something like `select('table.*, null as "name1", null as "name2")` – Derek Apr 29 '15 at 14:21

0 Answers0