Limit and offset on join

Question

I have a scheduled task that synchronize data from external source (Db Oracle 11g).

Here is the query i'm successfully executing.

   Partner.select("addresses.*, partners.*").joins(:address).where('existing=1)

Now, I would like to split this request by set a limit and an offset:

   Partner.select("addresses.*, partners.*").joins(:address).where('existing=1').limit(100).offset(100)

here is the query generated by active record and the error:

   SELECT * FROM (
     SELECT raw_sql_.*, rownum raw_rnum_
     FROM (
      SELECT  ADDRESSES.*, PARTNERS.* 
      FROM PARTNERS INNER JOIN ADDRESSES 
      ON ADDRESSES.ID = PARTNERS.ID  
      WHERE (EXISTING=1)
     ) raw_sql_
     WHERE rownum <= 200
    )
    WHERE raw_rnum_ > 100

ActiveRecord::StatementInvalid: Java::JavaSql::SQLSyntaxErrorException: ORA-00918: column ambiguously defined

Note: exactly the same error using kaminari (obviously):

   Partner.select("addresses.*, partners.*").joins(:address).where('existing=1').page(1)

score 0 · Answer 1 · answered Nov 25 '14 at 09:34

0

This error is due to the duplication of column names in the select clause. You must specify table_name.column_name in where clause like

Partner.select("addresses.*, partners.*").joins(:address).where('table_name.existing=1').page(1)

answered Nov 25 '14 at 09:34

Sachin Kadam

265
2
12

The "existing" column is only in the partners table. If I remove the "where" clausole I still have the problem – Irukandji Nov 25 '14 at 09:43

score 0 · Answer 2 · edited May 23 '17 at 10:26

I think there is problem with oracle adapter connection with ActiveRecord.

please check below file configuration

File : lib/active_record/connection_adapters/oracle_enhanced_adapter.rb

    def explain(arel, binds = [])
      sql = "EXPLAIN PLAN FOR #{to_sql(arel)}"
      return if sql =~ /FROM all_/
      exec_query(sql, 'EXPLAIN')
      if ORACLE_ENHANCED_CONNECTION == :jdbc
       exec_query(sql, 'EXPLAIN', binds)
     else
       exec_query(sql, 'EXPLAIN')
     end
     select_values("SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)", 'EXPLAIN').join("\n")
    end

If there is no changes , your file have same configuration as above there should be problem with ActiveRecored queries.

You can check reference Ambiguous column query

Another solution Rails / Active Record find with join, limit and offset

Hope this will help you to resolve your Problem :)

Aaditi Jain · Accepted Answer · 2014-11-26T06:30:20.793

0

You will have to specify the table to which column 'existing' belongs.

Also, you do not need to specify partners.*. The :joins method by default selects all columns from the partners table.

I am not sure why you added addresses.* there, because joins does not fetch the columns of the joined table(ie addresses here) into memory.

Nevertheless, assuming that the existing column belongs to the 'partners' table, this should work:

Partner.joins(:address).where("partners.existing = 1").limit(100).offset(100)

Hope this helps :)

edited Nov 26 '14 at 06:30

answered Nov 25 '14 at 10:41

Aaditi Jain

6,969
2
24
26

You are right, if I remove the "useless" select(blah blah) it works. Anyway is not needed to specify the table for the "existig" colums. Thanks :) – Irukandji Nov 26 '14 at 21:50
Glad the answer helped. :) Its always good to specify the table to which the column belongs. Incase you add a similar column (ie a column name 'existing' in this case ) to the joined table (addresses in this case), the code will throw a error, if the table name is not specified in the where clause. Try `Partner.joins(:address).where("id = 1")`. You will see it throws a error. If you instead try `Partner.joins(:address).where("partners.id = 1")` it will work! – Aaditi Jain Nov 26 '14 at 21:59
PS : Mark this as the answer if it solved your issue, so that others with similar issue know it too! :) – Aaditi Jain Nov 26 '14 at 22:01

Limit and offset on join

3 Answers3