Combine two PostgreSQL statements into one for Rails 3 app

Fertiggestellt Veröffentlicht Nov 20, 2011 Bezahlt bei Lieferung
Fertiggestellt 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.

Ingenieurwesen MySQL PHP Projektmanagement Ruby on Rails Softwarearchitektur Testen von Software Web Hosting Website-Management Testen von Webseiten

Projekt-ID: #3706914

Über das Projekt

3 Vorschläge Remote Projekt Aktiv Nov 20, 2011

Vergeben an:

taro

See private message.

$45 USD in 3 Tagen
(43 Bewertungen)
7.1

3 Freelancer bieten im Durchschnitt $29 für diesen Job

customizedata

See private message.

$35.7 USD in 3 Tagen
(210 Bewertungen)
7.5
tranvuongtrung

See private message.

$7 USD in 3 Tagen
(11 Bewertungen)
2.0