SQL JOIN query and read results in array (rails)

Question

can anyone help me in solving this issue? I'm trying to read the results in array from JOIN select in PG.

i=0;

sql = "
    SELECT partners.id, routes_rates.id_veh_class, routes_rates.min_price, routes_rates.km_rate, routes_rates.start, routes.id FROM routes
    JOIN partners ON routes.id_partner = partners.id
    JOIN routes_rates ON routes.id = routes_rates.id_route
    WHERE st_contains(routes.polygon, ST_GeomFromText('#{dvLoc}', 4326)) AND routes_rates.active = '1'"

    sear = ActiveRecord::Base.connection.execute(sql)
    sear.each do |sear|

How can I read in my model the values from array: partners.id, routes_rates.id_veh_class, routes_rates.min_price, routes_rates.km_rate, routes_rates.start, routes.id

?

I was trying meny options:

partner_id = sear.values[i]['partners.id'];
partner_id = sear.values[@i][0];
partner_id = sear.values['@i']['partners.id'];
partner_id = sear.values['#{@i}']["partners.id"];
partner_id = sear.values['#{i}'][0];
partner_id = sear.values["#{@i}"][0];

many thanks, T

Kevin P · Accepted Answer · 2016-09-10 20:29:20Z

You probably would be better approaching it from the "Rails way" of handing joins unless you have super huge tables and memory use is a major concern.

From your query, it looks like you've got 3 models, Partner, RoutesRate, Route. With Partner having many Routes which has many RoutesRate (the wording of that is weird, I'm guessing legacy data judging by that and the column names).

Assuming you've declared the appropriate belongs_to and has_many in those models, you should be able to change your code to use the ActiveRecord associations.

rate_routes = RoutesRate.includes(:route).where("st_contains(routes.polygon, ST_GeomFromText('#{dvLoc}', 4326)) AND routes_rates.active = '1'")

#includes will make an appropriate choice about join or a second query most of the time. From what I see, you're not using the partner table at all other than for the id, which is the same as id_partner.

Of course, this is all just going by inference from your query so your models may be different. Adjust as necessary. Access the route info by taking an rate_route instance and calling .route.

theDrifter · Accepted Answer · 2016-09-10 20:41:37Z

This will return a Array with Partner objects and the selected fields.

def method_name
      return Partner.find_by_sql(" "
          SELECT partners.id, routes_rates.id_veh_class,  routes_rates.min_price, routes_rates.km_rate, routes_rates.start, routes.id    
          FROM routes
          JOIN partners ON routes.id_partner = partners.id
          JOIN routes_rates ON routes.id = routes_rates.id_route
          WHERE st_contains(routes.polygon, ST_GeomFromText('#{dvLoc}',4326)) 
          AND routes_rates.active = '1'"
end

You can find more info here. Assuming the method lies in app/model/partner.rb and you call Partner.method_name.class you will get the array. Assuming the array is not nil, Partner.method_name[0] will give you the person object with the SELECTed fields.

Tom Hen · Accepted Answer · 2016-09-10 20:54:47Z

0

Thanks to both of you Guys! I finally fix the problem to make an alias the last column and call the values by order how they are listed.

      partner_id = sear.values[0];
      id_veh_class = sear.values[1];
      min_price = sear.values[2];
      km_rate = sear.values[3];
      start = sear.values[4];
      id_route = sear.values[5];

The problem was with the same name (id) of column in two tables: partners and routes and the second (routes.id) overwrite the value the first one.

T

answered Sep 10, 2016 at 20:54

Tom Hen

558 bronze badges

Collectives™ on Stack Overflow

SQL JOIN query and read results in array (rails)

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related