Forum OpenACS Q&A: Bug in neighbor-to-neighbor

Collapse
Posted by Roberto Mello on

When you delete a neighbor-to-neighbor posting, the users still can see and post to it when they are about to post a new story.

The query that shows which stories are available to the users should prevent the user from seeing postings not approved.

Here's how the query is right now:

select about
from neighbor_to_neighbor
where subcategory_id = $subcategory_id
and about is not null
group by about
order by upper(about)

I know that I have to add a approved_p='t' there but I got confused with the group by so if someone can help me figure out this one, I'd be thankful.

Collapse
Posted by Don Baccus on
Without digging into it, you should be able to add the check for approved.

Here's the general SQL standard execution paradigm:

((select ... from ... where ... )
group by ... )
order by;

In other words, all the selects are done, and the results of that are grouped together, then the groups that have been grouped are orded.

Postgres actually blows it for UNIONs, but I've not bothered telling anybody (it's largely cosmetic in the ACS realm and nothing can be done about it until the planned massive rewrite of front-end postgres stuff happens, so I didn't see any real point).

Collapse
Posted by Don Baccus on
Oh ... also, is that a 3.2 version?  The ACS has supposed to have moved to a model where approval state is three-valued, so you should see something like "approved_state - 'approved'".  It may be that neighbor, not of tremendous commercial importance, hasn't been rewritten but thought I'd ask.
Collapse
Posted by Roberto Mello on

Yes, this is 3.2. After your nice SQL explanation, I changed the query to:

select about,approved_p
from neighbor_to_neighbor
where subcategory_id = 1
and approved_p='t'
and about is not null
group by about,approved_p
order by upper(about)

and it yielded the correct results (e.g. only approved posts are shown).

I'll modify the other similar n_to_n queries (like in new-stuff.tcl) and send them to the module maintainer at aD. Maybe he's changing it to the new model that you described (where did you find that out anyway?).

Would we want to go with this version or wait for the official aD release ?