3

So, I have a problem. I have a query which returns ids from one table (say table1) and I have to pass those ids to another query which uses table2. (Writing inner selects or joins is not an option due to some certain reasons).

Query:

client = Mysql2::Client.new(:host => "localhost", :username => "", :password => "", :database =>"test")
query1 = %Q{select id from table1 where code='ABC123'}
ids = client.query(query1)
query2 = %Q{select * from table2 where `table2`.`table1_id` IN (#{ids}) and status="rejected"}
table2_data = client.query(query2)

ids is Mysql2::Result type Also, when I do ids.to_a, the resulting array has data something like this: [{"id"=>1}, {"id"=>2}] I need some feasible way to pass ids to the second query. I tried ids.to_a, but it gives error due to the braces [ ]. I have also tried concatenating, say the MySQL result is:

array = ids.to_a  # [1,2,3]
id_new = "("+#{array.join(',')}+")"

id_new becomes "(1,2,3)" which is a string and hence IN doesn't work.

Can anyone please suggest something how to pass ids array in the raw MySQL query? I have banged my head finding the answer, but couldn't find an appropriate one.

Edit: I can use Active Record only for query1 and if that is the case and ids is an Active Record object, can anyone suggest how to pass it to query2 in the IN clause which is supposed to be a raw SQL query?

Edit2: I can't use Active Record (for query2) or join because it's making the query heavy and taking long time (>10s) to fetch the result (indices are present). So, I am using raw query to optimise it.

Improve this question
4
  • 1
    When using ActiveRecord you can use the pluck method to get an array of ids instead of the whole models: ModelForTable1.where(:code => 'ABC123').pluck(:id) will result in something like [23,92,1,2]. Commented Nov 12, 2015 at 7:44
  • Also, could you give more information about your situation? Your question is hard to answer when you just exclude the standard RoR way (using ActiveRecord) but don't tell us, why. Commented Nov 12, 2015 at 7:47
  • Why don't you use a join? With a join, you could get all those ids in one query? Commented Nov 12, 2015 at 8:51
  • Hi, edited the question why I can't use them. I think I have figured out some solution. Will update in sometime. Commented Nov 12, 2015 at 9:26

4 Answers 4

Reset to default
3

When I ran similar queries to try to mimic your problem I saw that I'm getting an array of array for ids, like [["1"], ["2"], ["3"]].

If this is also what you're getting then you should call ids.flatten before calling join: 

query2 = %Q{select * from table2 where `table2`.`table1_id` IN (#{ids.flatten.join(',')}) and status="rejected"}

array.flatten removes extra braces, so:

[[1], [2], [3]].flatten
# => [1,2,3]

[[1], [2], [3]].flatten.join(',')
# => "1,2,3"

EDIT

Since you reported you are receiving a Mysql2::Result object, do this: ids.to_a.map(&:values).flatten.join(',')

The to_a first converts the Mysql2::Result to an array of hashes that looks like this:

[{"id"=>"1"}, {"id"=>"2"}]

Then using map(&:values) we convert it to an array that looks like this:

[["1"], ["2"]]

This array is similar to the above (before the edit), so running flatten.join(',') converts it to the string you are looking for.

Note that instead of doing map(&:values).flatten you could use the common shortcut flat_map(&:values) which results in the same thing.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Hi, please read the comment to Sarwan Kumar's answer. My result is different. Since, ids is a Mysql2::Result type, flatten and join don't work on that.
2

Are you sure it doesn't work because it is a string. I think it doesn't work because of duplicate brackets. Please try this:

array = ids.flat_map(&:values).join(',')
query2 = %Q{select * from table2 where `table2`.`table1_id` IN (#{array}) and status="rejected"}

I suggest to use a ORM (object-relational mapping) like the ActiveRecord or Sequel gems - especially because building database queries manually by string concatination is error prone and leads to vulnerabilities like sql injections.

Improve this answer

2 Comments

I can use that for query1 but, anyhow, I am not getting how to pass the ids to query2. Your solution also won't work. When I did what you suggested, I get result like this: "{\"id\"=>1},{\"id\"=>2}" while I need just (1,2)
@VJ_03 : Sorry, I missed that ids isn't an array. I updated my answer and it should work now.
0

If the main reason you posted was to learn how to extract data from an array of hashes, then you can ignore this answer.

However, if you wanted the best way to get the data from the database, I'd suggest you use ActiveRecord to do the donkey work for you:

class Table1 < ActiveRecord::Base
  self.table_name = :table1
  has_many :table2s
end

class Table2 < ActiveRecord::Base
  self.table_name = :table2
  belongs_to :table1
end

table2_data = Table2.joins(:table1).where(table1: {code: 'ABC123'}, status: 'rejected')

A key point is that a SQL join, will effectively do the processing of the IDs for you. You could code up the SQL join yourself, but ActiveRecord will do that for you, and allow you to add the additional queries, such that you can gather the data you want in one query.

Improve this answer

Comments

-2

You can join array with comma, like following code. 

ids = ids.to_a.map{|h| h['id']}
query2 = %Q{select * from table2 where `table2`.`table1_id` IN (#{ids.join(',')}) and status="rejected"}  
table2_data = client.query(query2)

It will work fine.

Improve this answer

2 Comments

But ids is a Mysql2::Result type not an array and join() doesn't work on it. Also, when I do ids.to_a, the resulting array has data something like this: [{"id"=>1}, {"id"=>2}]. And to pass to the second query, I only need (1,2) not as a string/array but like we pass in mysql query in general. Let me know if I am not clear. Thanks.
I have updated my answer. Now, you can first change MySQL array to Ruby array as: [{"id"=>1}, {"id"=>2}].to_a.map{|h| h['id']} will return [1,2], and then you can join [1,2].join(',') will return (1,2)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.