Combine two PostgreSQL statements into one for Rails 3 app
$30-45 USD
Bezahlt bei Lieferung
I have two PostgreSQL statement that I need to combine into one SQL statement for my Rails 3 application.
The combined SQL statement will need to be as optimal as possible. Greater consideration will be given to workers who are able to generate this SQL statement using Ruby on Rails ActiveRecord statement rather than pure SQL.
The two SQL statements are:
SELECT link_id, count(*) as counter
FROM "totals"
WHERE "totals"."tag_id" IN (6, 8)
AND (score > 0)
GROUP BY link_id
HAVING count(*)=2
The ActiveRecord version of this SQL statement is:
Total.find_all_by_tag_id(@tag_list, :conditions => ["score > 0"], :select => "link_id, count(*) as counter", :having => "count(*)=#{@[login to view URL]}", :group => "link_id")
The second SQL statement is:
SELECT s1.link_id
FROM totals AS s1
, (SELECT link_id
, MAX(score) AS maxscore
FROM totals
GROUP BY link_id) as s2
WHERE s2.link_id = s1.link_id
and [login to view URL] = [login to view URL]
AND [login to view URL] > 0 AND s1.tag_id = 6
More information will be provided in the details section.
## Deliverables
I have two PostgreSQL statement that I need to combine into one SQL statement for my Rails 3 application.
The combined SQL statement will need to be as optimal as possible. Greater consideration will be given to workers who are able to generate this SQL statement using Ruby on Rails ActiveRecord statement rather than pure SQL.
The first SQL statement returns a link where the link contains two specific tag_ids:
SELECT link_id, count(*) as counter FROM "totals" WHERE "totals"."tag_id" IN (6, 8) AND (score > 0) GROUP BY link_id HAVING count(*)=2
The ActiveRecord version of this SQL statement is:
Total.find_all_by_tag_id(@tag_list, :conditions => ["score > 0"], :select => "link_id, count(*) as counter", :having => "count(*)=#{@[login to view URL]}", :group => "link_id")
The second SQL statement returns the links that have the highest score of a specific tag_id:
SELECT s1.link_id FROM totals AS s1 , (SELECT link_id , MAX(score) AS maxscore FROM totals GROUP BY link_id) as s2 WHERE s2.link_id = s1.link_id and [login to view URL] = [login to view URL] AND [login to view URL] > 0 AND s1.tag_id = 6
Below is the 'totals' table that SQL statements will be working on. Please work on this table to provide me the SQL or ActiveRecord statement:
totals:
link_id : integer
tag_id : integer
score : integer
=============================
| link_id | tag_id | score |
=============================
| 1 | 6 | 5 |
| 1 | 8 | 2 |
| 1 | 3 | 1 |
| 2 | 6 | 6 |
| 2 | 4 | 2 |
| 2 | 8 | 6 |
| 3 | 6 | 5 |
| 3 | 2 | 4 |
| 4 | 2 | 4 |
| 4 | 6 | 1 |
| 4 | 8 | 2 |
=============================
The first SQL statement would return the `link_ids` `1, 2 and 4` and the second SQL statement would return `link_ids` `1, 2 and 3`.
The combined statement should return the `link_ids` `1 and 2`.
All I need is the SQL or ActiveRecord statement and I will run this code on my application.
Feel free to ask me any questions for clarification.
Thanks.
Projekt-ID: #3706914