0

I have two tables one trip and one txns having one to many relationship in oracle db. When i fire the below query 

Trip.where("upper(trips.domain) = 'FOOBAR.COM'  and txns.user_id = 50001 and txns.txn_type = '1'").includes([:txns]).order('trips.created_at desc').limit(10).offset(0)

For this there were two queries in the sql log.

    Trip Load (4920.4ms)   SELECT * FROM (
    SELECT raw_sql_.*, rownum raw_rnum_
    FROM (SELECT DISTINCT "TM"."TRIPS".id, FIRST_VALUE(trips.created_at)
 OVER (PARTITION BY "TM"."TRIPS".id ORDER BY trips.created_at desc) AS alias_0__ FROM "TM"."TRIPS" LEFT OUTER JOIN "TXNS" ON "TXNS"."TRIP_ID" = "TM"."TRIPS"."ID"
 WHERE (upper(trips.domain) = 'FOOBAR.COM' and txns.txn_type = '1') 
ORDER BY alias_0__ DESC) raw_sql_
    )
    WHERE raw_rnum_ between 1 and 10

    SQL (186.2ms)  SELECT "TM"."TRIPS"."ID" AS t0_r0, "TM"."TRIPS"."TRIP_REF" AS t0_r1, "TM"."TRIPS"."TRIP_NAME" AS t0_r2, 
"TM"."TRIPS"."START_DATE_TIME" AS t0_r3, "TM"."TRIPS"."END_DATE_TIME" AS t0_r4, "TM"."TRIPS"."AMOUNT" AS t0_r5, "TM"."TRIPS"."CREATED_AT" AS t0_r6,
 "TM"."TRIPS"."UPDATED_AT" AS t0_r7, "TM"."TRIPS"."USER_ID" AS t0_r8, 
"TM"."TRIPS"."BOOKING_STATUS" AS t0_r9, "TM"."TRIPS"."TRAVELLERS" AS t0_r10, 
"TM"."TRIPS"."USER_TRIP_NAME" AS t0_r11, "TM"."TRIPS"."CONTACT_DETAIL_ID" AS t0_r12, 
"TM"."TRIPS"."AIR" AS t0_r13, "TM"."TRIPS"."HOTEL" AS t0_r14, "TM"."TRIPS"."DOMAIN" AS t0_r15, "TM"."TRIPS"."TRAIN" AS t0_r16, "TM"."TRIPS"."TAGS" AS t0_r17, "TM"."TRIPS"."CURRENCY" AS t0_r18, "TM"."TRIPS"."CUR_INR_VALUE" AS t0_r19, 
"TM"."TRIPS"."COMPANY_ID" AS t0_r20, "TXNS"."ID" AS t1_r0, "TXNS"."TRIP_ID" AS t1_r1,
 "TXNS"."USER_ID" AS t1_r2, "TXNS"."TXN_TYPE" AS t1_r3, "TXNS"."STATUS" AS t1_r4, "TXNS"."SOURCE_TYPE" AS t1_r5, "TXNS"."SOURCE_ID" AS t1_r6, "TXNS"."EXTERNAL_REFS" AS t1_r7, "TXNS"."CREATED_AT" AS t1_r8, "TXNS"."IP_NUMBER" AS t1_r9, "TXNS"."MISC" AS t1_r10 FROM "TM"."TRIPS" LEFT OUTER JOIN "TXNS" ON "TXNS"."TRIP_ID" = "TM"."TRIPS"."ID" WHERE "TM"."TRIPS"."ID" IN (11620660, 11620651, 11620649, 11620647, 11620646, 11620645, 11620644, 11620642, 11620641, 11620636) AND (upper(trips.domain) = 'FOOBAR.COM' and txns.txn_type = '1') ORDER BY trips.created_at desc

I want to know why does active record fire two queries

Rima
  • 199
  • 1
  • 7

2 Answers2

1

The reason you've got two queries are that you're using a joins based include (because your where clauses references columns from the txns association) and because you've got a limit.

Because you're joining a has many rails can't just stick a limit 10 on your query - to take an extreme example if you had 1 trip with 10 associated txns then adding limit 10 to the query rails would normally run could return only that 1 trip (joined with each of the 10 matching txns) rather than 10 distinct trips

To get around this, rails first finds what the top 10 Trip rows matching your criteria are and then fires a second query to load those trip rows with the associations you want included.

Frederick Cheung
  • 83,189
  • 8
  • 152
  • 174
0

The answer can be seen in this SO question and answer about the different between a Rails include vs a Rails joins.

Community
  • 1
  • 1
MrDanA
  • 11,489
  • 2
  • 36
  • 47