Forum OpenACS Q&A: sql difficulties

1: sql difficulties

Posted by David Kuczek on 04/18/01 07:24 PM

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

2: Response to sql difficulties (response to 1)

Posted by Dan Wickstrom on 04/18/01 07:55 PM

Well my version of general_comments doesn't have a comment_type attribute. What version of openacs are you using?

3: Response to sql difficulties (response to 1)

Posted by David Kuczek on 04/18/01 08:05 PM

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?

4: Response to sql difficulties (response to 1)

Posted by David Kuczek on 04/18/01 08:12 PM

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

5: Response to sql difficulties (response to 1)

Posted by David Kuczek on 04/18/01 09:33 PM

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,


(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???

6: Response to sql difficulties (response to 1)

Posted by Dan Wickstrom on 04/18/01 09:57 PM

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;

7: Response to sql difficulties (response to 1)

Posted by David Kuczek on 04/18/01 10:34 PM

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