In Progress

Combine two PostgreSQL statements into one for Rails 3 app

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(*)=#{@[url removed, login to view]}", :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 [url removed, login to view] = [url removed, login to view]

AND [url removed, login to view] > 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(*)=#{@[url removed, login to view]}", :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 [url removed, login to view] = [url removed, login to view] AND [url removed, login to view] > 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.

Skills: Engineering, MySQL, PHP, Project Management, Ruby on Rails, Software Architecture, Software Testing, Web Hosting, Website Management, Website Testing

See more: ruby on rails version, ruby on rails free, return ruby on rails, free ruby on rails, rails any, postgresql rails, postgresql any, ruby on rails 5, statements, postgresql c++, combine, app count, rails group, using ruby rails, rails php, score app, rails max, ruby sql, postgresql sql, Postgresql php, postgresql table, combine link, php rails, ruby app, combine project

About the Employer:
( 3 reviews ) Australia

Project ID: #3706914

Awarded to:

taro

See private message.

$45 USD in 3 days
(43 Reviews)
7.0

4 freelancers are bidding on average $31 for this job

customizedata

See private message.

$35.7 USD in 3 days
(139 Reviews)
6.3
MSHA

See private message.

$35.7 USD in 3 days
(17 Reviews)
5.3
tranvuongtrung

See private message.

$7 USD in 3 days
(11 Reviews)
2.1