summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorCiaranG <ciaran@ciarang.com>2009-03-04 15:32:26 +0000
committerCiaranG <ciaran@ciarang.com>2009-03-04 15:32:26 +0000
commit115519a5e7e84e57656c653918efb39ab4107fe9 (patch)
tree2c66758d4df65f8d9b76cc17a6893c662debdf16
parentc02a2f189139bd3588b04e557239e243805aaeac (diff)
PostgreSQL - made all 'weight' calculating SQL expressions compatible with both databases, and made some GROUP BY queries more explicit about the fields they are selecting, for the same reason.
-rw-r--r--actions/favorited.php8
-rw-r--r--lib/groupsbymemberssection.php2
-rw-r--r--lib/groupsbypostssection.php2
-rw-r--r--lib/grouptagcloudsection.php8
-rw-r--r--lib/personaltagcloudsection.php10
-rw-r--r--lib/popularnoticesection.php12
6 files changed, 33 insertions, 9 deletions
diff --git a/actions/favorited.php b/actions/favorited.php
index fd5ff413c..5082f4a4e 100644
--- a/actions/favorited.php
+++ b/actions/favorited.php
@@ -169,8 +169,14 @@ class FavoritedAction extends Action
function showContent()
{
+ if (common_config('db', 'type') == 'pgsql') {
+ $weightexpr='sum(exp(-extract(epoch from (now() - fave.modified)) / %s))';
+ } else {
+ $weightexpr='sum(exp(-(now() - fave.modified) / %s))';
+ }
+
$qry = 'SELECT notice.*, '.
- 'sum(exp(-(now() - fave.modified) / %s)) as weight ' .
+ $weightexpr . ' as weight ' .
'FROM notice JOIN fave ON notice.id = fave.notice_id ' .
'GROUP BY fave.notice_id ' .
'ORDER BY weight DESC';
diff --git a/lib/groupsbymemberssection.php b/lib/groupsbymemberssection.php
index 4fa07a244..5f26c6626 100644
--- a/lib/groupsbymemberssection.php
+++ b/lib/groupsbymemberssection.php
@@ -45,7 +45,7 @@ class GroupsByMembersSection extends GroupSection
{
function getGroups()
{
- $qry = 'SELECT user_group.*, count(*) as value ' .
+ $qry = 'SELECT user_group.id, count(*) as value ' .
'FROM user_group JOIN group_member '.
'ON user_group.id = group_member.group_id ' .
'GROUP BY user_group.id ' .
diff --git a/lib/groupsbypostssection.php b/lib/groupsbypostssection.php
index a5e33a93d..1a60ddb4f 100644
--- a/lib/groupsbypostssection.php
+++ b/lib/groupsbypostssection.php
@@ -45,7 +45,7 @@ class GroupsByPostsSection extends GroupSection
{
function getGroups()
{
- $qry = 'SELECT user_group.*, count(*) as value ' .
+ $qry = 'SELECT user_group.id, count(*) as value ' .
'FROM user_group JOIN group_inbox '.
'ON user_group.id = group_inbox.group_id ' .
'GROUP BY user_group.id ' .
diff --git a/lib/grouptagcloudsection.php b/lib/grouptagcloudsection.php
index f05be85cb..5d68af28b 100644
--- a/lib/grouptagcloudsection.php
+++ b/lib/grouptagcloudsection.php
@@ -58,8 +58,14 @@ class GroupTagCloudSection extends TagCloudSection
function getTags()
{
+ if (common_config('db', 'type') == 'pgsql') {
+ $weightexpr='sum(exp(-extract(epoch from (now() - notice_tag.created)) / %s))';
+ } else {
+ $weightexpr='sum(exp(-(now() - notice_tag.created) / %s))';
+ }
+
$qry = 'SELECT notice_tag.tag, '.
- 'sum(exp(-(now() - notice_tag.created)/%s)) as weight ' .
+ $weightexpr . ' as weight ' .
'FROM notice_tag JOIN notice ' .
'ON notice_tag.notice_id = notice.id ' .
'JOIN group_inbox on group_inbox.notice_id = notice.id ' .
diff --git a/lib/personaltagcloudsection.php b/lib/personaltagcloudsection.php
index 0882822db..978153a84 100644
--- a/lib/personaltagcloudsection.php
+++ b/lib/personaltagcloudsection.php
@@ -58,8 +58,14 @@ class PersonalTagCloudSection extends TagCloudSection
function getTags()
{
- $qry = 'SELECT notice_tag.tag, '.
- 'sum(exp(-(now() - notice_tag.created)/%s)) as weight ' .
+ if (common_config('db', 'type') == 'pgsql') {
+ $weightexpr='sum(exp(-extract(epoch from (now() - notice_tag.created)) / %s))';
+ } else {
+ $weightexpr='sum(exp(-(now() - notice_tag.created) / %s))';
+ }
+
+ $qry = 'SELECT notice_tag.tag, '.
+ $weightexpr . ' as weight ' .
'FROM notice_tag JOIN notice ' .
'ON notice_tag.notice_id = notice.id ' .
'WHERE notice.profile_id = %d ' .
diff --git a/lib/popularnoticesection.php b/lib/popularnoticesection.php
index c7c7f0215..f7fb93554 100644
--- a/lib/popularnoticesection.php
+++ b/lib/popularnoticesection.php
@@ -48,10 +48,16 @@ class PopularNoticeSection extends NoticeSection
{
function getNotices()
{
- $qry = 'SELECT notice.*, '.
- 'sum(exp(-(now() - fave.modified) / %s)) as weight ' .
+ if (common_config('db', 'type') == 'pgsql') {
+ $weightexpr='sum(exp(-extract(epoch from (now() - fave.modified)) / %s))';
+ } else {
+ $weightexpr='sum(exp(-(now() - fave.modified) / %s))';
+ }
+
+ $qry = 'SELECT notice.id, '.
+ $weightexpr . ' as weight ' .
'FROM notice JOIN fave ON notice.id = fave.notice_id ' .
- 'GROUP BY fave.notice_id ' .
+ 'GROUP BY notice.id ' .
'ORDER BY weight DESC';
$offset = 0;