@nciemniak Maybe work on this one?

@thisismeonmounteverest I'll check it out 😎

@thisismeonmounteverest and me talked about this in merge request #339.

The current query looks like this:

        return $this->createQueryBuilder('a')
            ->join('App:ActivityAttendee', 'aa', Join::WITH, 'aa.activity = a and aa.organizer = 1')
            ->join('App:Member', 'm', Join::WITH, 'aa.attendee = m')
            ->where("m.status = 'Banned'")
            ->orWhere('DATEDIFF(a.ends, a.starts) > 1')
            ->orderBy('a.id', 'desc')
            ->getQuery();

and includes a strange handling of only showing activities with a day difference of at least +1:
->orWhere('DATEDIFF(a.ends, a.starts) > 1')

@thisismeonmounteverest Your suggestion is to remove this special handling completely.

Can you or somebody else check the production database for weird activities with negative duration or does anyone have knowledge on why this was implemented?

My current fix would still exclude all activities with negative duration using a positive minute comparison by implementing TIMESTAMPDIFF:

        return $this->createQueryBuilder('a')
            ->join('App:ActivityAttendee', 'aa', Join::WITH, 'aa.activity = a and aa.organizer = 1')
            ->join('App:Member', 'm', Join::WITH, 'aa.attendee = m')
            ->where("m.status = 'Banned'")
            ->orWhere('TIMESTAMPDIFF(MINUTE, a.starts, a.ends) >= 0')
            ->orderBy('a.id', 'desc')
            ->getQuery();

@PLP-GTR Interestingly the production database has two entries for activities with negative time span. But the two members who created them aren't banned, so the query for problematic activities wouldn't pick them up.

The main issue we had on BeWelcome with activities were activities that lasted for months, therefore the focus was on being easily able to spot them. 'Regular' activities of a few hours would vanish quickly enough anyway.

@thisismeonmounteverest just to make sure:

If I remove the orWhere the "spam" activities are only those, created by already banned members.

Long-duration activities of active members would not show up anymore. But now as I write this I realize that my fix would just show all activities overall and not only spam ones 😖

So - what is the definition of a Spam-Activity?

I'm coming to the conclusion that this issue is not an issue at all and the administration of the activities it is about (not banned + duration < 1 day) should not be done at the spam section.

@PLP-GTR Right, removing the orWhere (I always read that as an andWhere) would only show activities of banned members and no others. That's not what we want.

Instead show all activities that are still going to happen? Pseudocode: ->orWhere('a.starts > Now()') This would allow a spam checker to look through all activities and determine if one is spam and delete it.

As the one who opened this issue, maybe I can help clarify the intent. The best would indeed be for the list to show all upcoming activities. No need to check if a member is banned or to check the activity's duration. There are no automatic criteria to decide if an activity is spam, moderators are judging in each case (though long-duration ones and ones from banned membes are more likely to be).
What we do is basically check the list of upcoming activities and, if we see a spam one, go to the admin tool and delete it. Presently, if we see one that doesn't have a long duration, we must first ban the member (so that the activity appears in the list) and then delete the activity. This can be an issue, because publishing a 'spam' activity is not automatically reason for a ban (then we have to un-ban the member). Some people can post hosting offers as activities: it's not appropriate so we delete these activities, but we just inform the members, we don't ban them.