Combine two PostgreSQL statements into one for Rails 3 app

Виконано Опубліковано %project.relative_time Оплачується при отриманні
Виконано Оплачується при отриманні

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.

Техніка MySQL PHP Управління проектом Ruby on Rails Архітектура ПЗ Тестування ПЗ Веб-хостинг Управління веб-сайтами Тестування сайтів

ID Проекту: #3706914

Про проект

3 заявок(-ки) Дистанційний проект Остання активність Nov 20, 2011

Доручено:

taro

See private message.

$45 USD за 3 дні(-в)
(43 відгуків(-и))
7.1

3 фрілансерів(-и) готові виконати цю роботу у середньому за $29

customizedata

See private message.

$35.7 USD за 3 дні(-в)
(210 відгуків(и))
7.5
tranvuongtrung

See private message.

$7 USD за 3 дні(-в)
(11 відгуків(и))
2.0