I have a query that looks in the USER table to find the top 9 affiliates based on the frequency of their affiliate code.
affs = User.group(:affiliate).limit(9).count.sort_by{|i,j|-j}
(0.6ms) SELECT COUNT(*) AS count_all, affiliate AS affiliate FROM "users" GROUP BY affiliate LIMIT 9
#=> [["ACE5", 5], ["2YY", 3], ["MB7", 2], ["GB2", 2], ["GHCYM", 1], ["L6EOC", 1], ["15F2", 1], ["R0TK", 1], ["DSB", 1]]
Now I take this result and I enumerate over it to get the row corresponding to the user.
affs.map do |aff|
user = User.find_by(qcode: aff.first)
[user.avatar.url(:thumb), user.name.match(/\w*/)[0], aff.first, aff.last]
end
#LOG OUTPUT
User Load (0.3ms) SELECT "users".* FROM "users" WHERE "users"."qcode" = 'ACE5' LIMIT 1
User Load (0.3ms) SELECT "users".* FROM "users" WHERE "users"."qcode" = '2YY' LIMIT 1
User Load (0.3ms) SELECT "users".* FROM "users" WHERE "users"."qcode" = 'GB2' LIMIT 1
User Load (0.3ms) SELECT "users".* FROM "users" WHERE "users"."qcode" = 'MB7' LIMIT 1
User Load (0.3ms) SELECT "users".* FROM "users" WHERE "users"."qcode" = 'L6EOC' LIMIT 1
User Load (0.3ms) SELECT "users".* FROM "users" WHERE "users"."qcode" = '15F2' LIMIT 1
User Load (0.3ms) SELECT "users".* FROM "users" WHERE "users"."qcode" = 'R0TK' LIMIT 1
User Load (0.3ms) SELECT "users".* FROM "users" WHERE "users"."qcode" = 'DSB' LIMIT 1
Running this query 9 times seems very redundant. How can I optimize this into 1 or 2 database queries?
Example USERS table DB Schema:
id | email | name | qcode | affiliate
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
25 | [email protected] | Ken Lu | DSB | L6EOC
26 | [email protected] | Brains Bruk | AJ8U | DSB
27 | [email protected] | Joan Hu | K9UE | DSB
From the above example. "Ken Lu" is affiliate DSB and he is responsible for signing up ID 26 and ID 27. So the above aff query would produce:
#=> [["DSB", 2], ...]
To get the user table information for all 9 affiliates at once, you can drop down into raw SQL.
From what I can infer of your schema from the code above, something like this should work (you may need to tweak it a bit if your schema differs from what I inferred; not tested since I don't have your data nor a Rails setup handy):
WITH top_affiliates AS
(
SELECT COUNT(*) AS count_all, affiliate AS affiliate
FROM users
GROUP BY affiliate
ORDER BY count_all
LIMIT 9
)
SELECT *
FROM top_affiliates AS t
JOIN users AS u ON (t.affiliate = u.qcode)
ORDER BY count_all
Note that the JOIN assumes that t.affiliate = u.qcode will provide a unique mapping between the tables (based on the resulting SQL from when you were iterating over the map, this seemed like a fair assumption). If it's not unique, though, you would need to add additional parameters in the JOIN such that the mapping is unique, else you would get additional rows in the result that you wouldn't want.
If you're using ActiveRecord, you can use connection.execute on the model containing this information, and pass the above SQL in as a string (or assign it to a variable first and pass that in).
Edit in response to comment from OP:
@f1f5: The WITH query defines what's called a CTE, which is essentially a temp table scoped to the query itself as opposed to the connection. I can then use that CTE in queries underneath the WITH, which is what I'm doing above. Basically, the WITH query is taking the place of your User.group(:affiliate).limit(9).count.sort_by{|i,j|-j} code and then using the results inline to pull the relevant user records.
Edit in response to schema info from OP:
So, yes, I think my code above will do what you want. Here's an sqlfiddle I created with the schema data, query, and result.
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다