Forum OpenACS Q&A: sql difficulties

Collapse
Posted by David Kuczek on
I have some silly sql difficulties and I just don't get it...

The query looks like this:

select count(gc.comment_id), gc.comment_type
(select avg(wert) from general_bewertung where comment_id in (select comment_id from general_comments where user_id = gc.user_id and comment_type = gc.comment_type)) as durchschnitt_bewertung,
(select count(gelesen_id) from general_gelesen where on_what_id in (select comment_id from general_comments where user_id = gc.user_id and comment_type = gc.comment_type)) as x_gelesen
from general_comments gc
where gc.user_id = $user_id
group by gc.comment_type


The output just won't show any result for the two subselects. The subselects don't get the "where user_id = gc.user_id and comment_type = gc.comment_type" right...

Any suggestions?

Is there a way to make this query a little more efficient, as I have to select the same stuff twice inside the two subqueries.

Thanks
Collapse
Posted by Dan Wickstrom on
Well my version of general_comments doesn't have a comment_type attribute.  What version of openacs are you using?
Collapse
Posted by David Kuczek on
Hello Dan,

ooops I forgot to mention that I enhanced general_comments with comment_type. It is 1 for an opinion, 2 for a comment 3 for an advice and 4 for an article...

general_bewertung (=how valued) and general_gelesen (=how many times read) don't belong to openacs either.

What information would you additionally need?

Is it normal that the subselect can't find the gc.user_id and gc.comment_type from general_comments above?

Collapse
Posted by David Kuczek on
Is there an aggregate function that would list the comment_ids, similiar to counting them.

I could imagine a
list(comment_id)
which I could use instead of the
(select comment_id from general_comments where user_id = gc.user_id and comment_type = gc.comment_type)) as x_gelesen
Collapse
Posted by David Kuczek on
The strange thing about it is, that the gc.comment_type is being recognized when I exchange the subquery

(select avg(wert) from general_bewertung where comment_id in (select comment_id from general_comments where user_id = gc.user_id and comment_type = gc.comment_type)) as durchschnitt_bewertung,

to

(select max(comment_id) from general_comments where user_id = gc.user_id and comment_type = gc.comment_type) as durchschnitt_bewertung,

Is this possibly a postgres bug???
Collapse
Posted by Dan Wickstrom on
Usually posting a simplified test case with table definitions makes these sorts of questions easier to figure out. I'm guessing from what you've posted so far that you want to have the comments summarized by comment_type and a count of how many times each question type has been viewed along with some type of weighting. If my assumptions are correct, then something like the following would work:

select count(gc.comment_id) as n_comments, 
       gc.comment_type,
       coalesce((select avg(wert) 
          from general_bewertung b
         where exists (select 1
                         from general_comments 
                        where comment_type = gc.comment_type
                          and b.comment_id = comment_id
                          and user_id = $user_id)),0) as durchschnitt_bewertung,
       (select count(gelesen_id) 
          from general_gelesen g
         where exists (select 1
                         from general_comments 
                        where comment_type = gc.comment_type
                          and g.on_what_id = comment_id
                          and user_id = $user_id)) as x_gelesen
       from general_comments gc
      where gc.user_id = $user_id
   group by gc.comment_type;

Collapse
Posted by David Kuczek on
Hello Dan,

you are my hero 😊

I almost lost my head to this query. I will post an example the next time I ask such a question.

Thanks