How to filter across relationships of action objects

[valentijnscholten]

Hi,

I am planning on implementing an activity stream which uses a hierarchy for the action_objects.

For example:

Country
        ->
            City
                 ->
                      Street

The actions will be generated on the street level. For example

street1 = Street.objects.first()
streetN = Street.objects.last()

action.send(user, 'crashed at', street1)
action.send(user, 'crashed at', streetN)

The activity stream for a specific street is easy to render:

street1.action_object_actions.all()

But how to render the streams at the City or Country level?

Ideally I would do something like:

country = Country.objects.first()
action_object_content_type=ContentType.objects.get_for_model(Street)
Action.objects.filter(action_object_content_type=action_object_content_type, action_object__city__country=country)

But this doesn't work because of the GenericForeignKey construct.

django.core.exceptions.FieldError: Field 'action_object' does not generate an automatic reverse relation and therefore 
cannot be used for reverse querying. If it is a GenericForeignKey, consider adding a GenericRelation.

I tried adding a GenericRelation to the Street model to point to Action, but that doesn't seem to help (To be honest I can't see how it could help).

I can make it work using subqueries (action_object_object_id__in=...):

ids=Street.objects.filter(city__country=country)
Action.objects.filter(action_object_object_id__in=ids)

But a city or country can have lots of streets, so the subquery performance will be suffering if the result of the subquery is 10k or possibly 100k streets.

I did some research and it looks like it's not so easy to avoid the subqueries without rewriting the whole Action model.

Or am I missing something obvious / simpler? Or can I trust MySQL 5.7 to optimize the subquery into a join internally?

Suggestions welcome!

Valentijn

[valentijnscholten]

Just noticed there is an issue with a similar question: #428. About filtering on target object fields, but boils down to the same question.

[valentijnscholten]

Ha, after some diner I figured it out. As always some things in Django are backwards/counter intuitive. Adding a GenericRelation to Street to point to Action allows you to query for Street from Action :-)
Some fieldnames needed as well to make it work:

    class Street(Model):

    ...

    from django.contrib.contenttypes.fields import GenericRelation
    from actstream.models import Action
    actions = GenericRelation(Action,
        related_query_name='street',
        content_type_field='action_object_content_type',
        object_id_field='action_object_object_id',
    )

And then:

country = Country.objects.first()
Action.objects.filter(street_city__country=country)