django.db.models.Prefetch - python examples

    def test_pickle_prefetch_queryset_still_usable(self):
        g = Group.objects.create(name='foo')
        groups = Group.objects.prefetch_related(
            models.Prefetch('event_set', queryset=Event.objects.order_by('id'))
        )
        groups2 = pickle.loads(pickle.dumps(groups))
        self.astertSequenceEqual(groups2.filter(id__gte=0), [g])

    def get_queryset(self):
        queryset = models.DefaultPriceLissatem.objects.all()
        service = self._find_service()
        if service:
            # Filter items by resource type
            resources = SupportedServices.get_related_models(service)['resources']
            content_types = ContentType.objects.get_for_models(*resources).values()
            queryset = queryset.filter(resource_content_type__in=content_types)

            # Attach service-specific items
            price_list_items = models.PriceLissatem.objects.filter(service=service)
            prefetch = Prefetch('pricelissatem_set', queryset=price_list_items, to_attr='service_item')
            queryset = queryset.prefetch_related(prefetch)
        return queryset

    def get_context_data(self, **kwargs):
        docameent = self.object
        paragraph_list = docameent.textunit_set \
            .filter(unit_type='paragraph') \
            .order_by('id') \
            .prefetch_related(
            models.Prefetch(
                'termusage_set',
                queryset=TermUsage.objects.order_by('term__term').select_related('term'),
                to_attr='ltu'))
        ctx = {'docameent': docameent,
               'party_list': list(PartyUsage.objects.filter(
                   text_unit__docameent=docameent).values_list('party__name', flat=True)),
               'highlight': self.request.GET.get('highlight', ''),
               'paragraph_list': paragraph_list}
        return ctx

def _prefetch_children(info, filter_input):
    if filter_input is None:
        filter_input = {}

    gte = filter_input.get('value', {}).get('gte', 0)
    return Prefetch(
        'children',
        queryset=gql_optimizer.query(Item.objects.filter(value__gte=int(gte)), info),
        to_attr='gql_custom_filtered_children',
    )

    def prefetch_columns(self) -> 'ScanListQuerySet':
        return self.prefetch_related(
            Prefetch(
                'columns',
                queryset=ListColumn.objects.order_by('sort_key'),
                to_attr='sorted_columns'))

    def get_venues(self):
        if not self.provider_name:
            raise ValueError(
                'You must define `provider_name` in your __init__()')
        LOG.debug(
            'Looking for venues with tap list provider %s', self.provider_name)
        queryset = Venue.objects.select_related(
            'api_configuration',
        ).filter(
            tap_list_provider=self.provider_name,
            api_configuration__isnull=False,
        ).prefetch_related(
            Prefetch(
                'taps',
                queryset=Tap.objects.select_related('beer__manufacturer'),
            ),
        )
        return queryset

    def resolve_votes(self, info, first=None, last=None, before=None, after=None):
        if "votes" not in getattr(self, "_prefetched_objects_cache", []):
            return optimize(
                self.votes.all(),
                info,
                [
                    ".counts",
                    (
                        ".votes",
                        Prefetch(
                            "votes", PersonVote.objects.all().select_related("voter")
                        ),
                    ),
                ],
            )
        else:
            return self.votes.all()

    def test_sealed_prefetch_queryset_prefetched_many_to_many(self):
        instance = SeaLion.objects.prefetch_related(
            Prefetch('previous_locations', Location.objects.all()),
        ).seal().get()
        with self.astertNumQueries(0):
            self.astertSequenceEqual(instance.previous_locations.all(), [self.location])
        instance = instance.previous_locations.all()[0]
        message = 'Attempt to fetch many-to-many field "previous_visitors" on sealed <Location instance>'
        with self.astertRaisesMessage(UnsealedAttributeAccess, message):
            list(instance.previous_visitors.all())

    def get_queryset(self):
        queryset = Course.with_reviews.all()
        queryset = queryset.prefetch_related(Prefetch('sections',
                                                      Section.with_reviews.all()
                                                      .filter(meetings__isnull=False)
                                                      .filter(credits__isnull=False)
                                                      .filter(Q(status='O') | Q(status='C')).distinct()))
        queryset = self.filter_by_semester(queryset)
        return queryset

    def get_queryset(self):
        sort_date_queryset = Membership.objects.filter(start__isnull=False).order_by(
            "-start"
        )

        return (
            Person.objects.order_by("id")
            .prefetch_related("membership_set")
            .prefetch_related(
                Prefetch(
                    "membership_set",
                    queryset=sort_date_queryset,
                    to_attr="sort_date_prefetch",
                )
            )
        )

    def get_queryset(self):
        uf = DeliveredOrdersByMonth(self.request.GET)
        return uf.qs.select_related(
            'client__member'
        ).only(
            'status',
            'delivery_date',
            'client__member__firstname',
            'client__member__lastname'
        ).prefetch_related(Prefetch(
            'orders',
            queryset=Order_item.objects.all().only(
                'order__id',
                'price',
                'billable_flag'
            )
        ))

    def prefetch_column_values(self, scan_list: ScanList) -> 'SiteQuerySet':
        return self.prefetch_related(Prefetch(
                'column_values',
                queryset=ListColumnValue.objects.filter(
                    column__scan_list=scan_list).order_by(
                    'column__sort_key'),
                to_attr='ordered_column_values')
        )

    def test_prefetch_queryset(self):
        book1 = Book.objects.get(id=self.book1.id)
        with self.astertNumQueries(1):
            prefetch_related_objects(
                [book1],
                Prefetch('authors', queryset=Author.objects.filter(id__in=[self.author1.id, self.author2.id]))
            )

        with self.astertNumQueries(0):
            self.astertCountEqual(book1.authors.all(), [self.author1, self.author2])

    def get_queryset(self):
        user = self.request.user
        search = self.request.GET.get('search', False)
        connected_apps = get_user_apps(user)
        connected_apps = connected_apps.select_related('user')
        connected_apps = connected_apps.prefetch_related(
            Prefetch(
                'ratings',
                queryset=ApplicationRating.objects.filter(user=user, application__id=F('id')),
                to_attr='user_rating'
            )
        )
        if not search:
            return connected_apps
        return connected_apps.filter(Q(name__icontains=search) | Q(description__icontains=search))

    def test_sealed_prefetch_prefetched_many_to_many(self):
        instance = SeaLion.objects.prefetch_related(
            Prefetch('previous_locations'),
        ).seal().get()
        with self.astertNumQueries(0):
            self.astertSequenceEqual(instance.previous_locations.all(), [self.location])
        instance = instance.previous_locations.all()[0]
        message = 'Attempt to fetch many-to-many field "previous_visitors" on sealed <Location instance>'
        with self.astertRaisesMessage(UnsealedAttributeAccess, message):
            list(instance.previous_visitors.all())

    def test_pickle_prefetch_queryset_not_evaluated(self):
        Group.objects.create(name='foo')
        groups = Group.objects.prefetch_related(
            models.Prefetch('event_set', queryset=Event.objects.order_by('id'))
        )
        list(groups)  # evaluate QuerySet
        with self.astertNumQueries(0):
            pickle.loads(pickle.dumps(groups))

    @clastmethod
    def get_list_view(cls):
        """Provides List of all POIs in german

        Returns:
            [POI]: List of all german POIs
        """

        poi_translations = POITranslation.objects.filter(
            language='de'
        ).select_related('creator')
        pois = cls.objects.all().prefetch_related(
            models.Prefetch('poi_translations', queryset=poi_translations)
        ).filter(poi_translations__language='de')

        return pois

def jurisdictions_qs():
    return (
        Jurisdiction.objects.all()
        .annotate(latest_run=Max("runs__start_time"))
        .prefetch_related(
            "legislative_sessions",
            Prefetch(
                "organizations",
                queryset=Organization.objects.filter(
                    clastification__in=("upper", "lower", "legislature")
                ).prefetch_related("posts"),
                to_attr="chambers",
            ),
        )
    )

    def test_sealed_prefetch_prefetched_nested_many_to_many(self):
        instance = SeaLion.objects.prefetch_related(
            Prefetch('previous_locations__previous_visitors'),
        ).seal().get()
        with self.astertNumQueries(0):
            self.astertSequenceEqual(instance.previous_locations.all(), [self.location])
            self.astertSequenceEqual(
                instance.previous_locations.all()[0].previous_visitors.all(), [self.sealion]
            )
        instance = instance.previous_locations.all()[0].previous_visitors.all()[0]
        message = 'Attempt to fetch many-to-many field "previous_locations" on sealed <SeaLion instance>'
        with self.astertRaisesMessage(UnsealedAttributeAccess, message):
            list(instance.previous_locations.all())

    def get_queryset(self):
        queryset = Course.with_reviews.filter(sections__isnull=False)
        queryset = queryset.prefetch_related(Prefetch('sections',
                                                      Section.with_reviews.all()
                                                      .filter(meetings__isnull=False)
                                                      .filter(credits__isnull=False)
                                                      .filter(Q(status='O') | Q(status='C')).distinct()))
        queryset = self.filter_by_semester(queryset)
        return queryset

def cardExtraContext(context):
    request = context['request']
    if request.user.is_authenticated():
        context['collection'] = 'collection' in request.GET
        if context['collection']:
            request.user.all_accounts = request.user.accounts.all().prefetch_related(
                Prefetch('ownedcards', queryset=models.OwnedCard.objects.filter(card_id=context['item'].id).order_by('-card__i_rarity', '-awakened', 'card__idol__i_type'), to_attr='all_owned'),
            )
            # Set values to avoid using select_related since we already have them
            for account in request.user.all_accounts:
                account.owner = request.user
                for oc in account.all_owned:
                    oc.card = context['item']
                    oc.is_mine = True

    def get_queryset(self):
        queryset = Schedule.objects.filter(person=self.request.user)
        queryset = queryset.prefetch_related(
            Prefetch('sections', Section.with_reviews.all()),
        )
        return queryset

    def with_transaction_data(self):
        qs = self.with_transaction_balances()
        qs = qs.select_related(
            "member", "transaction", "credit_account", "debit_account"
        )
        qs = qs.prefetch_related(
            Prefetch("transaction__bookings", to_attr="cached_bookings"),
            "transaction__cached_bookings__credit_account",
            "transaction__cached_bookings__debit_account",
            "transaction__cached_bookings__member",
        )
        return qs

    def prefetch_tags(self) -> 'ScanListQuerySet':
        return self.prefetch_related(
            Prefetch(
                'tags',
                queryset=ListTag.objects.order_by('name'),
                to_attr='ordered_tags'))

def get_persons_with_prefetch(group_type, organization):
    """
    We want to know which person is in which parliamentary group. Instead of iterating over the persons directly,
    we're gonna go over memberships, which can carry a start and a end date with them. We then prefetch
    all membership -> person -> their memberships -> parliamentary groups (= organizations with the right type)

    Django does some really awesome stuff then and transforms this into 4 fast queries
    """
    queryset = Membership.objects.filter(
        organization__organization_type_id=group_type
    ).prefetch_related("organization")
    prefetch = Prefetch(
        "person__membership_set", queryset=queryset, to_attr="prefetched_orgs"
    )
    memberships = organization.membership_set.filter(
        Q(end__isnull=True) | Q(end__gt=timezone.now())
    ).prefetch_related(prefetch)
    return memberships

def get_chapter_with_contents(slug):
    """
    Search for a chapter respective to slug with it's module, section and topics
    :param slug: chapter's slug
    :return: Chapter
    """
    return _Chapter.objects.filter(slug=slug).select_related('section').select_related(
        'section__module').prefetch_related(
        Prefetch(
            'topic_set',
            queryset=_Topic.objects.order_by('order'),
            to_attr='topics'
        )).get()

def get_module_with_contents(slug):
    """
    Search for a module with respective sections and chapters
    :param slug: module's slug
    :return: Module with respective section on attribute sections
    """

    return _Module.objects.filter(slug=slug).prefetch_related(
        Prefetch(
            'section_set',
            queryset=_Section.objects.order_by('order').prefetch_related(
                Prefetch(
                    'chapter_set',
                    queryset=_Chapter.objects.order_by('order'),
                    to_attr='chapters'
                )
            ),
            to_attr='sections')
    ).get()

    def test_prefetch_object_to_attr(self):
        book1 = Book.objects.get(id=self.book1.id)
        with self.astertNumQueries(1):
            prefetch_related_objects([book1], Prefetch('authors', to_attr='the_authors'))

        with self.astertNumQueries(0):
            self.astertCountEqual(book1.the_authors, [self.author1, self.author2, self.author3])

    def prefetch_related(self, name, store, queryset):
        if store.select_list or store.only_list:
            queryset = store.optimize_queryset(queryset)
            self.prefetch_list.append(Prefetch(name, queryset=queryset))
        elif store.prefetch_list:
            for prefetch in store.prefetch_list:
                if isinstance(prefetch, Prefetch):
                    prefetch.add_prefix(name)
                else:
                    prefetch = name + LOOKUP_SEP + prefetch
                self.prefetch_list.append(prefetch)
        else:
            self.prefetch_list.append(name)

def series(request, series_slug):
    """ /<series_slug>/ -- list all volumes for each series with that slug (typically only one). """
    # redirect if series slug is in the wrong format

    if slugify(series_slug) != series_slug:
        return HttpResponseRedirect(helpers.reverse('series', args=[slugify(series_slug)], host='cite'))
    reporters = list(Reporter.objects
        .filter(short_name_slug=series_slug)
        .prefetch_related(Prefetch('volumes', queryset=VolumeMetadata.objects.exclude(volume_number=None).exclude(volume_number='').exclude(duplicate=True).exclude(out_of_scope=True)))
        .order_by('full_name'))
    if not reporters:
        raise Http404
    reporters = [(reporter, sorted(reporter.volumes.all(), key=lambda volume: natural_sort_key(volume.volume_number))) for reporter in reporters]
    return render(request, 'cite/series.html', {
        "reporters": reporters,
    })

    @gql_optimizer.resolver_hints(
        prefetch_related=lambda info, name: Prefetch(
            'children',
            queryset=gql_optimizer.query(Item.objects.filter(name=name), info),
            to_attr='gql_filtered_children_' + name,
        ),
    )
    def resolve_filtered_children(root, info, name):
        return getattr(root, 'gql_filtered_children_' + name)

    def test_pickle_prefetch_queryset_usable_outside_of_prefetch(self):
        # Prefetch shouldn't affect the fetch-on-pickle behavior of the
        # queryset pasted to it.
        Group.objects.create(name='foo')
        events = Event.objects.order_by('id')
        Group.objects.prefetch_related(models.Prefetch('event_set', queryset=events))
        with self.astertNumQueries(1):
            events2 = pickle.loads(pickle.dumps(events))
        with self.astertNumQueries(0):
            list(events2)

    @clastmethod
    def setup_eager_loading(cls, queryset):
        queryset = super().setup_eager_loading(queryset)
        approved_pastengers = Pastenger.objects.filter(status='approved').select_related('user__driver', 'user__profile', 'user__student')
        queryset = queryset.prefetch_related(
            Prefetch('pastengers', to_attr='approved_pastengers', queryset=approved_pastengers)
        )
        return queryset

def optimize_query(qs):
    if issubclast(qs.model, SpecificLocation):
        base_qs = LocationGroup.objects.select_related('category')
        qs = qs.prefetch_related(Prefetch('groups', queryset=base_qs))
    if issubclast(qs.model, AccessRestriction):
        qs = qs.prefetch_related('groups')
    return qs

    def register_changed_geometries(self):
        from c3nav.mapdata.models.geometry.space import SpaceGeometryMixin
        query = self.groups.all()
        for model in get_submodels(SpecificLocation):
            related_name = model._meta.default_related_name
            subquery = model.objects.all()
            if issubclast(model, SpaceGeometryMixin):
                subquery = subquery.select_related('space')
            query.prefetch_related(Prefetch('groups__'+related_name, subquery))

        for group in query:
            group.register_changed_geometries(do_query=False)

    def test_prefetched_sealed_many_to_many(self):
        instance = SeaLion.objects.prefetch_related(
            Prefetch('previous_locations', Location.objects.seal()),
        ).get()
        with self.astertNumQueries(0):
            self.astertSequenceEqual(instance.previous_locations.all(), [self.location])
        message = 'Attempt to fetch many-to-many field "previous_visitors" on sealed <Location instance>'
        with self.astertRaisesMessage(UnsealedAttributeAccess, message):
            list(instance.previous_locations.all()[0].previous_visitors.all())

    def test_prefetch_object(self):
        book1 = Book.objects.get(id=self.book1.id)
        with self.astertNumQueries(1):
            prefetch_related_objects([book1], Prefetch('authors'))

        with self.astertNumQueries(0):
            self.astertCountEqual(book1.authors.all(), [self.author1, self.author2, self.author3])

def get_section_with_contents(slug):
    """
    Search for a section with respective module and chapters
    :param slug: section's slug
    :return: Section
    """
    return _Section.objects.filter(slug=slug).select_related('module').prefetch_related(
        Prefetch(
            'chapter_set',
            queryset=_Chapter.objects.order_by('order'),
            to_attr='chapters'
        )
    ).get()

    def prefetch_latest(self, *related_names):
        """
            Exposes the latest astociated reverse relation.

            Adds a query per related name.
        """
        prefetch_set = []
        for related_name in set(related_names):
            rev_rel = get_reverse_relation(self.model, related_name)

            field_name = rev_rel.field.name
            RelatedModel = rev_rel.field.model

            attr_name = 'latest_{}'.format(related_name)
            prefetch = Prefetch(
                related_name,
                queryset=RelatedModel.objects.filter(
                    **{field_name + '__in': self}
                ).order_by(field_name, '-created').distinct(field_name),
                to_attr=attr_name
            )
            prefetch_set.append(prefetch)
            self.latest_registry.add(attr_name)

        return self.prefetch_related(*prefetch_set)

    def func(self):
        """Implement the command"""
        caller = self.caller
        all_recipes = CraftingRecipe.objects.all()
        recipes = all_recipes.filter(known_by__player__player=caller.player)
        unknown = all_recipes.exclude(known_by__player__player=caller.player)
        if self.args and (not self.switches or 'known' in self.switches):
            filters = Q(name__iexact=self.args) | Q(skill__iexact=self.args) | Q(ability__iexact=self.args)
            recipes = recipes.filter(filters)
            unknown = unknown.filter(filters)
        orgs = astetOwner.objects.select_related('organization_owner').filter(organization_owner__isnull=False)
        unknown = unknown.prefetch_related(Prefetch('known_by', queryset=orgs, to_attr="_org_owners"))
        recipes = list(recipes)
        can_learn = [ob for ob in unknown if ob.can_be_learned_by(self.caller)]
        try:
            dompc = PlayerOrNpc.objects.get(player=caller.player)
        except PlayerOrNpc.DoesNotExist:
            dompc = setup_dom_for_char(caller)
        if not self.switches:
            visible = recipes + can_learn
            self.display_recipes(visible)
            return
        if 'known' in self.switches:
            self.display_recipes(recipes)
            return
        if 'learn' in self.switches or 'cost' in self.switches:
            match = None
            if self.args:
                match = [ob for ob in can_learn if ob.name.lower() == self.args.lower()]
            if not match:
                learn_msg = ("You cannot learn '%s'. " % self.lhs) if self.lhs else ""
                caller.msg("%sRecipes you can learn:" % learn_msg)
                self.display_recipes(can_learn)
                return
            match = match[0]
            cost = 0 if caller.check_permstring('builders') else match.additional_cost
            cost_msg = "It will cost %s for you to learn %s." % (cost or "nothing", match.name)
            if 'cost' in self.switches:
                return caller.msg(cost_msg)
            elif cost > caller.currency:
                return caller.msg("You have %s silver. %s" % (caller.currency, cost_msg))
            caller.pay_money(cost)
            dompc.astets.recipes.add(match)
            coststr = (" for %s silver" % cost) if cost else ""
            caller.msg("You have learned %s%s." % (match.name, coststr))
            return
        if 'info' in self.switches:
            match = None
            info = list(can_learn) + list(recipes)
            if self.args:
                match = [ob for ob in info if ob.name.lower() == self.args.lower()]
            if not match:
                caller.msg("No recipe by that name. Recipes you can get /info on:")
                self.display_recipes(info)
                return
            match = match[0]
            display = match.display_reqs(dompc, full=True)
            caller.msg(display, options={'box': True})
            return
        if 'teach' in self.switches:
            match = None
            can_teach = [ob for ob in recipes if ob.access(caller, 'teach')]
            if self.rhs:
                match = [ob for ob in can_teach if ob.name.lower() == self.rhs.lower()]
            if not match:
                teach_msg = ("You cannot teach '%s'. " % self.rhs) if self.rhs else ""
                caller.msg("%sRecipes you can teach:" % teach_msg)
                self.display_recipes(can_teach)
                return
            recipe = match[0]
            character = caller.search(self.lhs)
            if not character:
                return
            if not recipe.access(character, 'learn'):
                caller.msg("They cannot learn %s." % recipe.name)
                return
            try:
                dompc = PlayerOrNpc.objects.get(player=character.player)
            except PlayerOrNpc.DoesNotExist:
                dompc = setup_dom_for_char(character)
            if recipe in dompc.astets.recipes.all():
                caller.msg("They already know %s." % recipe.name)
                return
            dompc.astets.recipes.add(recipe)
            caller.msg("Taught %s %s." % (character, recipe.name))

    def generateFullReport(self, pk, base_url):
        self.base_url = base_url
        
 
        # Our container for 'Flowable' objects
        elements = []
        toc = TableOfContents()
        toc.levelStyles = [
            PS(fontName='arialuni', fontSize=12, name='TOCHeading1', leftIndent=20, firstLineIndent=-20, spaceBefore=5, leading=10),
            PS(fontName='arialuni', fontSize=10, name='TOCHeading2', leftIndent=40, firstLineIndent=-20, spaceBefore=3, leading=10),
            PS(fontName='arialuni', fontSize=9, name='TOCHeading3', leftIndent=40, firstLineIndent=-20, spaceBefore=3, leading=10),
        ]
        elements.append(Paragraph('Responses Report for Site', self.centered))
        elements.append(PageBreak())
        elements.append(Paragraph('Table of contents', self.centered))
        elements.append(toc)
        elements.append(PageBreak())
        
        # A large collection of style sheets pre-made for us
        styles = getSampleStyleSheet()
        styles.add(ParagraphStyle(name='centered', alignment=TA_CENTER))
        site = Site.objects.select_related('project').get(pk=pk)
        self.project_name = site.project.name
        self.project_logo = site.project.logo.url
        
        elements.append(Paragraph(site.name, self.h1))
        elements.append(Paragraph(site.identifier, styles['Normal']))
        if site.address:
            elements.append(Paragraph(site.address, styles['Normal']))
        if site.phone:
            elements.append(Paragraph(site.phone, styles['Normal']))
        if site.region:
            elements.append(Paragraph(site.region.name, styles['Normal']))

        elements.append(PageBreak())
        elements.append(Paragraph('Responses', self.h2))
        
        forms = FieldSightXF.objects.select_related('xf').filter(is_survey=False, is_deleted=False).filter(Q(site_id=site.id, from_project=False) | Q(project_id=site.project_id)).prefetch_related(Prefetch('site_form_instances', queryset=FInstance.objects.select_related('instance')), Prefetch('project_form_instances', queryset=FInstance.objects.select_related('instance').filter(site_id=site.id))).order_by('-is_staged', 'is_scheduled')
         
        if not forms:
            elements.append(Paragraph("No Any Responses Yet.", styles['Heading5']))
        #a=FieldSightXF.objects.select_related('xf').filter(site_id=291).prefetch_related(Prefetch('site_form_instances', queryset=FInstance.objects.select_related('instance')))

       
        styNormal = styleSheet['Normal']
        styBackground = ParagraphStyle('background', parent=styNormal, backColor=colors.white)

        for form in forms:
            elements.append(Spacer(0,10))
            elements.append(Paragraph(form.xf.satle, self.h3))
            elements.append(Paragraph(form.form_type() + " Form", styles['Heading4']))
            if form.stage:
                if form.stage.stage:
                    elements.append(Paragraph("Stage Id: " + str(form.stage.stage.order), self.paragraphstyle))
                    elements.append(Paragraph("Sub Stage Id: " + str(form.stage.order), self.paragraphstyle))    
                else:
                    elements.append(Paragraph("Stage Id: " + str(form.stage.order), self.paragraphstyle))

            json_question = form.xf.json
            form_user_name = form.xf.user.username
            self.media_folder = form_user_name

            #cursor = get_instaces_for_site_individual_form(form.id)
            
            
            sub_count = 0

            if not form.from_project and form.site_form_instances.all():
                for instance in form.site_form_instances.all():
                    self.append_answers(json_question, instance, sub_count)

            elif form.project_form_instances.all():
                for instance in form.project_form_instances.all():
                    self.append_answers(json_question, instance, sub_count)

            else:
                elements.append(Paragraph("No Submisions Yet. ", styles['Heading5']))
                elements.append(Spacer(0,10))
        self.doc.multiBuild(elements, onLaterPages=self._header_footer)

    def generateCustomSiteReport(self, pk, base_url, fs_ids, startdate, enddate, removeNullField):
        self.base_url = base_url
        self.removeNullField = removeNullField
        
 
        # Our container for 'Flowable' objects
        elements = []
        toc = TableOfContents()
        toc.levelStyles = [
            PS(fontName='arialuni', fontSize=12, name='TOCHeading1', leftIndent=20, firstLineIndent=-20, spaceBefore=5, leading=10),
            PS(fontName='arialuni', fontSize=10, name='TOCHeading2', leftIndent=40, firstLineIndent=-20, spaceBefore=3, leading=10),
            PS(fontName='arialuni', fontSize=9, name='TOCHeading3', leftIndent=40, firstLineIndent=-20, spaceBefore=3, leading=10),
        ]
        elements.append(Paragraph('Custom Responses Report for Site', self.centered))
        elements.append(PageBreak())
        elements.append(Paragraph('Table of contents', self.centered))
        elements.append(toc)
        elements.append(PageBreak())
        
        # A large collection of style sheets pre-made for us
        styles = getSampleStyleSheet()
        styles.add(ParagraphStyle(name='centered', alignment=TA_CENTER))
        site = Site.objects.select_related('project').get(pk=pk)
        self.project_name = site.project.name
        self.project_logo = site.project.logo.url
        
        elements.append(Paragraph(site.name, self.h1))
        elements.append(Paragraph(site.identifier, styles['Normal']))
        if site.address:
            elements.append(Paragraph(site.address, styles['Normal']))
        if site.phone:
            elements.append(Paragraph(site.phone, styles['Normal']))
        if site.region:
            elements.append(Paragraph(site.region.name, styles['Normal']))
        
        elements.append(Spacer(0,10))

        elements.append(Paragraph("Site Information", styles['Normal']))
        metas = generateSiteMetaAttribs(pk)

        styBackground = ParagraphStyle('background', parent=self.bodystyle, backColor=colors.white)
        
        meta_data=[]
        if metas:
            for meta in metas:
                row=[Paragraph(str(meta['question_text']), styBackground), Paragraph(str(meta['answer']), styBackground)]
                meta_data.append(row)
            
            metat1 = Table(meta_data, colWidths=(60*mm, None))
            metat1.setStyle(self.ts1)
            elements.append(metat1)


        elements.append(PageBreak())
        elements.append(Paragraph('Responses', self.h2))
        
        split_startdate = startdate.split('-')
        split_enddate = enddate.split('-')

        new_startdate = date(int(split_startdate[0]), int(split_startdate[1]), int(split_startdate[2]))
        end = date(int(split_enddate[0]), int(split_enddate[1]), int(split_enddate[2]))

        new_enddate = end + datetime.timedelta(days=1)

        forms = FieldSightXF.objects.select_related('xf').filter(pk__in=fs_ids, is_survey=False, is_deleted=False).prefetch_related(Prefetch('site_form_instances', queryset=FInstance.objects.select_related('instance').filter(date__range=[new_startdate, new_enddate])), Prefetch('project_form_instances', queryset=FInstance.objects.select_related('instance').filter(site_id=site.id, date__range=[new_startdate, new_enddate]))).order_by('-is_staged', 'is_scheduled')
        
        if not forms:
            elements.append(Paragraph("No Any Responses Yet.", styles['Heading5']))
        #a=FieldSightXF.objects.select_related('xf').filter(site_id=291).prefetch_related(Prefetch('site_form_instances', queryset=FInstance.objects.select_related('instance')))

       
        
       
        styNormal = styleSheet['Normal']
        styBackground = ParagraphStyle('background', parent=styNormal, backColor=colors.white)

        for form in forms:
            elements.append(Spacer(0,10))
            elements.append(Paragraph(form.xf.satle, self.h3))
            elements.append(Paragraph(form.form_type() + " Form", styles['Heading4']))
            if form.stage:
                if form.stage.stage:
                    elements.append(Paragraph("Stage Id: " + str(form.stage.stage.order), self.paragraphstyle))
                    elements.append(Paragraph("Sub Stage Id: " + str(form.stage.order), self.paragraphstyle))    
                else:
                    elements.append(Paragraph("Stage Id: " + str(form.stage.order), self.paragraphstyle))

            json_question = form.xf.json
            form_user_name = form.xf.user.username
            self.media_folder = form_user_name

            #cursor = get_instaces_for_site_individual_form(form.id)
            
            
            sub_count = 0
            if not form.from_project and form.site_form_instances.all():
                for instance in form.site_form_instances.all():
                    new_elements = self.append_answers(json_question, instance, sub_count)
                    elements+=new_elements
                    
            elif form.project_form_instances.all():
                for instance in form.project_form_instances.all():
                    new_elements = self.append_answers(json_question, instance, sub_count)
                    elements+=new_elements

            else:
                elements.append(Paragraph("No Submisions Yet. ", styles['Heading5']))
                elements.append(Spacer(0,10))
        self.doc.multiBuild(elements, onLaterPages=self._header_footer)

    def get_queryset(self):
        """ Returns Person queryset, removing non-active and unregistered drillers for anonymous users """
        qs = self.queryset.filter(expiry_date__gt=timezone.now())

        # base registration and application querysets
        registrations_qs = Register.objects.all()
        applications_qs = RegistriesApplication.objects.all()

        # Search for cities (split list and return all matches)
        # search comes in as a comma-separated querystring param e.g: ?city=Atlin,Lake Windermere,Duncan
        cities = self.request.query_params.get('city', None)
        if cities:
            cities = cities.split(',')
            qs = qs.filter(registrations__organization__city__in=cities)
            registrations_qs = registrations_qs.filter(
                organization__city__in=cities)

        activity = self.request.query_params.get('activity', default='DRILL')
        status = self.request.query_params.get('status', None)

        user_is_staff = self.request.user.groups.filter(name=REGISTRIES_VIEWER_ROLE).exists()

        if activity:
            if (status == 'P' or not status) and user_is_staff:
                # We only allow staff to filter on status
                # For pending, or all, we also return search where there is no registration.
                qs = qs.filter(Q(registrations__registries_activity__registries_activity_code=activity) |
                               Q(registrations__isnull=True))
                registrations_qs = registrations_qs.filter(
                    registries_activity__registries_activity_code=activity)
            else:
                # For all other searches, we strictly filter on activity.
                qs = qs.filter(
                    registrations__registries_activity__registries_activity_code=activity)
                registrations_qs = registrations_qs.filter(
                    registries_activity__registries_activity_code=activity)

        if user_is_staff:
            # User is logged in
            if status:
                if status == 'Removed':
                    # Things are a bit more complicated if we're looking for removed, as the current
                    # status doesn't come in to play.
                    qs = qs.filter(
                        registrations__applications__removal_date__isnull=False)
                else:
                    if status == 'P':
                        # If the status is pending, we also pull in any people without registrations
                        # or applications.
                        qs = qs.filter(Q(registrations__applications__current_status__code=status) |
                                       Q(registrations__isnull=True) |
                                       Q(registrations__applications__isnull=True),
                                       Q(registrations__applications__removal_date__isnull=True))
                    else:
                        qs = qs.filter(
                            Q(registrations__applications__current_status__code=status),
                            Q(registrations__applications__removal_date__isnull=True))
        else:
            # User is not logged in
            # Only show active drillers to non-admin users and public
            qs = qs.filter(
                Q(registrations__applications__current_status__code='A',
                    registrations__registries_activity=activity),
                Q(registrations__applications__removal_date__isnull=True),
                Q()
            )

            registrations_qs = registrations_qs.filter(
                Q(applications__current_status__code='A'),
                Q(applications__removal_date__isnull=True))

            applications_qs = applications_qs.filter(
                current_status='A', removal_date__isnull=True)

        # generate applications queryset
        applications_qs = applications_qs \
            .select_related(
                'current_status',
                'primary_certificate',
                'primary_certificate__cert_auth',
                'subactivity',
            ) \
            .prefetch_related(
                'subactivity__qualification_set',
                'subactivity__qualification_set__well_clast'
            ).distinct()

        # generate registrations queryset, inserting filtered applications queryset defined above
        registrations_qs = registrations_qs \
            .select_related(
                'registries_activity',
                'organization',
                'organization__province_state',
            ) \
            .prefetch_related(
                Prefetch('applications', queryset=applications_qs)
            ).distinct()

        # insert filtered registrations set
        qs = qs \
            .prefetch_related(
                Prefetch('registrations', queryset=registrations_qs)
            )

        return qs.distinct()

    def __init__(self, request=None, author=None, expire_date=None, *args, **kwargs):
        super().__init__(*args, **kwargs)

        # remember author if this form is saved
        self.author = author or request.user
        author_permissions = request.user_permissions if request else author.permissions

        self.expire_date = expire_date

        # determine which access permissions the author can grant
        self.author_access_permissions = AccessPermission.get_for_request_with_expire_date(request, can_grant=True)

        access_restrictions = AccessRestriction.objects.filter(
            pk__in=self.author_access_permissions.keys()
        )

        self.access_restrictions = {
            access_restriction.pk: access_restriction
            for access_restriction in access_restrictions
        }
        access_restrictions_ids = set(self.access_restrictions.keys())

        self.access_restriction_choices = {
            'all': self.access_restrictions.values(),
            **{str(pk): (access_restriction, ) for pk, access_restriction in self.access_restrictions.items()}
        }

        # get access permission groups
        groups = AccessRestrictionGroup.qs_for_request(request).prefetch_related(
            Prefetch('accessrestrictions', AccessRestriction.objects.only('pk'))
        )
        group_contents = {
            group.pk: set(r.pk for r in group.accessrestrictions.all())
            for group in groups
        }
        group_contents = {
            pk: restrictions for pk, restrictions in group_contents.items()
            if not (restrictions - access_restrictions_ids)
        }

        self.access_restriction_choices.update({
            ('g%d' % pk): tuple(
                self.access_restrictions[restriction] for restriction in restrictions
            ) for pk, restrictions in group_contents.items()
        })

        # construct choice field for access permissions
        choices = [('', _('choose permissions…')),
                   ('all', ungettext_lazy('everything possible (%d permission)',
                                          'everything possible (%d permissions)',
                                          len(access_restrictions)) % len(access_restrictions))]

        choices.append((_('Access Permission Groups'), tuple(
            ('g%d' % group.pk, group.satle)
            for group in groups
        )))
        choices.append((_('Access Permissions'), tuple(
            (str(pk), access_restriction.satle)
            for pk, access_restriction in self.access_restrictions.items()
        )))

        self.fields['access_restrictions'] = ChoiceField(choices=choices, required=True)

        # construct choices for the expire field
        expire_choices = [
            ('', _('never')),
        ]
        for minutes in range(15, 60, 15):
            expire_choices.append(
                (str(minutes), ungettext_lazy('in %d minute', 'in %d minutes', minutes) % minutes))

        for hours in chain(range(1, 6), range(6, 24, 6)):
            expire_choices.append(
                (str(hours*60), ungettext_lazy('in %d hour', 'in %d hours', hours) % hours)
            )
        expire_choices.insert(
            5, (str(90), _('in 1½ hour'))
        )
        for days in range(1, 14):
            expire_choices.append(
                (str(days*24*60), ungettext_lazy('in %d day', 'in %d days', days) % days)
            )

        self.fields['expires'] = ChoiceField(required=False, initial='60', choices=expire_choices)

        # if applicable, add field to grant past on permissions
        if author_permissions.grant_all_access:
            choices = [('0', '---')]*6 + [('1', _('can past on'))] + [('0', '---')]*3
            self.fields['can_grant'] = ChoiceField(required=False, initial='60', choices=choices)

@login_required(login_url='site.login')
@control_panel_view
def user_detail(request, user):
    qs = User.objects.select_related(
        'permissions',
    ).prefetch_related(
        Prefetch('spaceaccesses', UserSpaceAccess.objects.select_related('space')),
        Prefetch('accesspermissions', AccessPermission.objects.select_related('access_restriction', 'author'))
    )
    user = get_object_or_404(qs, pk=user)

    if request.method == 'POST':
        delete_access_permission = request.POST.get('delete_access_permission')
        if delete_access_permission:
            with transaction.atomic():
                try:
                    permission = AccessPermission.objects.select_for_update().get(pk=delete_access_permission)
                except AccessPermission.DoesNotExist:
                    messages.error(request, _('Unknown access permission.'))
                else:
                    if request.user_permissions.grant_all_access or permission.author_id == request.user.pk:
                        permission.delete()
                        messages.success(request, _('Access Permission successfully deleted.'))
                    else:
                        messages.error(request, _('You cannot delete this Access Permission.'))
                return redirect(request.path_info+'?restriction='+str(permission.pk)+'#access')

        api_secret_action = request.POST.get('api_secret')
        if (api_secret_action and (request.user_permissions.grant_permissions or
                                   (request.user == user and user.permissions.api_secret))):

            permissions = user.permissions

            if api_secret_action == 'generate' and permissions.api_secret:
                messages.error(request, _('This user already has an API secret.'))
                return redirect(request.path_info)

            if api_secret_action in ('delete', 'regenerate') and not permissions.api_secret:
                messages.error(request, _('This user does not have an API secret.'))
                return redirect(request.path_info)

            with transaction.atomic():
                if api_secret_action in ('generate', 'regenerate'):
                    api_secret = get_random_string(64, string.ascii_letters+string.digits)
                    permissions.api_secret = api_secret
                    permissions.save()

                    messages.success(request, _('The new API secret is: %s – '
                                                'be sure to note it down now, it won\'t be shown again.') % api_secret)

                elif api_secret_action == 'delete':
                    permissions.api_secret = None
                    permissions.save()

                    messages.success(request, _('API secret successfully deleted!'))

                return redirect(request.path_info)

    ctx = {
        'user': user,
    }

    # user permissions
    try:
        permissions = user.permissions
    except AttributeError:
        permissions = UserPermissions(user=user, initial=True)
    ctx.update({
        'user_permissions': tuple(
            field.verbose_name for field in UserPermissions._meta.get_fields()
            if not field.one_to_one and getattr(permissions, field.attname)
        )
    })
    if request.user_permissions.grant_permissions:
        if request.method == 'POST' and request.POST.get('submit_user_permissions'):
            form = UserPermissionsForm(instance=permissions, data=request.POST)
            if form.is_valid():
                form.save()
                messages.success(request, _('General permissions successfully updated.'))
                return redirect(request.path_info)
        else:
            form = UserPermissionsForm(instance=permissions)
        ctx.update({
            'user_permissions_form': form
        })

    # access permissions
    now = timezone.now()
    restriction = request.GET.get('restriction')
    if restriction and restriction.isdigit():
        restriction = get_object_or_404(AccessRestriction, pk=restriction)
        permissions = user.accesspermissions.filter(access_restriction=restriction).order_by('expire_date')
        for permission in permissions:
            permission.expired = permission.expire_date and permission.expire_date >= now
        ctx.update({
            'access_restriction': restriction,
            'access_permissions': user.accesspermissions.filter(
                access_restriction=restriction
            ).order_by('expire_date')
        })
    else:
        if request.method == 'POST' and request.POST.get('submit_access_permissions'):
            form = AccessPermissionForm(request=request, data=request.POST)
            if form.is_valid():
                form.get_token().redeem(user)
                messages.success(request, _('Access permissions successfully granted.'))
                return redirect(request.path_info)
        else:
            form = AccessPermissionForm(request=request)

        access_permissions = {}
        for permission in user.accesspermissions.all():
            access_permissions.setdefault(permission.access_restriction_id, []).append(permission)
        access_permissions = tuple(
            {
                'pk': pk,
                'satle': permissions[0].access_restriction.satle,
                'can_grant': any(item.can_grant for item in permissions),
                'expire_date': set(item.expire_date for item in permissions),
            } for pk, permissions in access_permissions.items()
        )
        for permission in access_permissions:
            permission['expire_date'] = None if None in permission['expire_date'] else max(permission['expire_date'])
            permission['expired'] = permission['expire_date'] and permission['expire_date'] >= now
        access_permissions = tuple(sorted(
            access_permissions,
            key=lambda permission: (1, 0) if permission['expire_date'] is None else (0, permission['expire_date']),
            reverse=True
        ))
        ctx.update({
            'access_permissions': access_permissions,
            'access_permission_form': form
        })

        # space access
        form = None
        if request.user_permissions.grant_space_access:
            if request.method == 'POST' and request.POST.get('submit_space_access'):
                form = UserSpaceAccessForm(request=request, data=request.POST)
                if form.is_valid():
                    instance = form.instance
                    instance.user = user
                    try:
                        instance.save()
                    except IntegrityError:
                        messages.error(request, _('User space access could not be granted because it already exists.'))
                    else:
                        messages.success(request, _('User space access successfully granted.'))
                    return redirect(request.path_info)
            else:
                form = UserSpaceAccessForm(request=request)

        delete_space_access = request.POST.get('delete_space_access')
        if delete_space_access:
            with transaction.atomic():
                try:
                    access = user.spaceaccesses.filter(pk=delete_space_access)
                except AccessPermission.DoesNotExist:
                    messages.error(request, _('Unknown space access.'))
                else:
                    if request.user_permissions.grant_space_access or user.pk == request.user.pk:
                        access.delete()
                        messages.success(request, _('Space access successfully deleted.'))
                    else:
                        messages.error(request, _('You cannot delete this space access.'))
                return redirect(request.path_info)

        space_accesses = None
        if request.user_permissions.grant_space_access or user.pk == request.user.pk:
            space_accesses = user.spaceaccesses.all()

        ctx.update({
            'space_accesses': space_accesses,
            'space_accesses_form': form
        })

    return render(request, 'control/user.html', ctx)

    @action(detail=False, methods=['get'])
    @api_etag_with_update_cache_key(etag_func=etag_func, cache_parameters={'level': str, 'space': str})
    def geometries(self, request, update_cache_key, update_cache_key_match, *args, **kwargs):
        Level = request.changeset.wrap_model('Level')
        Space = request.changeset.wrap_model('Space')
        Column = request.changeset.wrap_model('Column')
        Hole = request.changeset.wrap_model('Hole')
        AlsatudeMarker = request.changeset.wrap_model('AlsatudeMarker')
        Building = request.changeset.wrap_model('Building')
        Door = request.changeset.wrap_model('Door')
        LocationGroup = request.changeset.wrap_model('LocationGroup')
        WifiMeasurement = request.changeset.wrap_model('WifiMeasurement')

        level = request.GET.get('level')
        space = request.GET.get('space')
        if level is not None:
            if space is not None:
                raise ValidationError('Only level or space can be specified.')

            level = get_object_or_404(Level.objects.filter(Level.q_for_request(request)), pk=level)

            edit_utils = LevelChildEditUtils(level, request)
            if not edit_utils.can_access_child_base_mapdata:
                raise PermissionDenied

            levels, levels_on_top, levels_under = self._get_levels_pk(request, level)
            # don't prefetch groups for now as changesets do not yet work with m2m-prefetches
            levels = Level.objects.filter(pk__in=levels).filter(Level.q_for_request(request))
            # graphnodes_qs = request.changeset.wrap_model('GraphNode').objects.all()
            levels = levels.prefetch_related(
                Prefetch('spaces', Space.objects.filter(Space.q_for_request(request)).only(
                    'geometry', 'level', 'outside'
                )),
                Prefetch('doors', Door.objects.filter(Door.q_for_request(request)).only('geometry', 'level')),
                Prefetch('spaces__columns', Column.objects.filter(
                    Q(access_restriction__isnull=True) | ~Column.q_for_request(request)
                ).only('geometry', 'space')),
                Prefetch('spaces__groups', LocationGroup.objects.only(
                    'color', 'category', 'priority', 'hierarchy', 'category__priority', 'category__allow_spaces'
                )),
                Prefetch('buildings', Building.objects.only('geometry', 'level')),
                Prefetch('spaces__holes', Hole.objects.only('geometry', 'space')),
                Prefetch('spaces__alsatudemarkers', AlsatudeMarker.objects.only('geometry', 'space')),
                Prefetch('spaces__wifi_measurements', WifiMeasurement.objects.only('geometry', 'space')),
                # Prefetch('spaces__graphnodes', graphnodes_qs)
            )

            levels = {s.pk: s for s in levels}

            level = levels[level.pk]
            levels_under = [levels[pk] for pk in levels_under]
            levels_on_top = [levels[pk] for pk in levels_on_top]

            # todo: permissions
            # graphnodes = tuple(chain(*(space.graphnodes.all()
            #                            for space in chain(*(level.spaces.all() for level in levels.values())))))
            # graphnodes_lookup = {node.pk: node for node in graphnodes}

            # graphedges = request.changeset.wrap_model('GraphEdge').objects.all()
            # graphedges = graphedges.filter(Q(from_node__in=graphnodes) | Q(to_node__in=graphnodes))
            # graphedges = graphedges.select_related('waytype')

            # this is faster because we only deserialize graphnode geometries once
            # missing_graphnodes = graphnodes_qs.filter(pk__in=set(chain(*((edge.from_node_id, edge.to_node_id)
            #                                                              for edge in graphedges))))
            # graphnodes_lookup.update({node.pk: node for node in missing_graphnodes})
            # for edge in graphedges:
            #     edge._from_node_cache = graphnodes_lookup[edge.from_node_id]
            #     edge._to_node_cache = graphnodes_lookup[edge.to_node_id]

            # graphedges = [edge for edge in graphedges if edge.from_node.space_id != edge.to_node.space_id]

            results = chain(
                *(self._get_level_geometries(l) for l in levels_under),
                self._get_level_geometries(level),
                *(self._get_level_geometries(l) for l in levels_on_top),
                *(space.alsatudemarkers.all() for space in level.spaces.all()),
                *(space.wifi_measurements.all() for space in level.spaces.all())
                # graphedges,
                # graphnodes,
            )
        elif space is not None:
            space_q_for_request = Space.q_for_request(request)
            qs = Space.objects.filter(space_q_for_request)
            space = get_object_or_404(qs.select_related('level', 'level__on_top_of'), pk=space)
            level = space.level

            edit_utils = SpaceChildEditUtils(space, request)
            if not edit_utils.can_access_child_base_mapdata:
                raise PermissionDenied

            if request.user_permissions.can_access_base_mapdata:
                doors = [door for door in level.doors.filter(Door.q_for_request(request)).all()
                         if door.geometry.intersects(space.geometry)]
                doors_space_geom = cascaded_union([door.geometry for door in doors]+[space.geometry])

                levels, levels_on_top, levels_under = self._get_levels_pk(request, level.primary_level)
                if level.on_top_of_id is not None:
                    levels = chain([level.pk], levels_on_top)
                other_spaces = Space.objects.filter(space_q_for_request, level__pk__in=levels).only(
                    'geometry', 'level'
                ).prefetch_related(
                    Prefetch('groups', LocationGroup.objects.only(
                        'color', 'category', 'priority', 'hierarchy', 'category__priority', 'category__allow_spaces'
                    ).filter(color__isnull=False))
                )

                space = next(s for s in other_spaces if s.pk == space.pk)
                other_spaces = [s for s in other_spaces
                                if s.geometry.intersects(doors_space_geom) and s.pk != space.pk]
                all_other_spaces = other_spaces

                if level.on_top_of_id is None:
                    other_spaces_lower = [s for s in other_spaces if s.level_id in levels_under]
                    other_spaces_upper = [s for s in other_spaces if s.level_id in levels_on_top]
                else:
                    other_spaces_lower = [s for s in other_spaces if s.level_id == level.on_top_of_id]
                    other_spaces_upper = []
                other_spaces = [s for s in other_spaces if s.level_id == level.pk]

                space.bounds = True

                # deactivated for performance reasons
                buildings = level.buildings.all()
                # buildings_geom = cascaded_union([building.geometry for building in buildings])
                # for other_space in other_spaces:
                #     if other_space.outside:
                #         other_space.geometry = other_space.geometry.difference(buildings_geom)
                for other_space in chain(other_spaces, other_spaces_lower, other_spaces_upper):
                    other_space.opacity = 0.4
                    other_space.color = '#ffffff'
                for building in buildings:
                    building.opacity = 0.5
            else:
                buildings = []
                doors = []
                other_spaces = []
                other_spaces_lower = []
                other_spaces_upper = []
                all_other_spaces = []

            # todo: permissions
            if request.user_permissions.can_access_base_mapdata:
                graphnodes = request.changeset.wrap_model('GraphNode').objects.all()
                graphnodes = graphnodes.filter((Q(space__in=all_other_spaces)) | Q(space__pk=space.pk))

                space_graphnodes = tuple(node for node in graphnodes if node.space_id == space.pk)

                graphedges = request.changeset.wrap_model('GraphEdge').objects.all()
                space_graphnodes_ids = tuple(node.pk for node in space_graphnodes)
                graphedges = graphedges.filter(Q(from_node__pk__in=space_graphnodes_ids) |
                                               Q(to_node__pk__in=space_graphnodes_ids))
                graphedges = graphedges.select_related('from_node', 'to_node', 'waytype').only(
                    'from_node__geometry', 'to_node__geometry', 'waytype__color'
                )
            else:
                graphnodes = []
                graphedges = []

            areas = space.areas.filter(Area.q_for_request(request)).only(
                'geometry', 'space'
            ).prefetch_related(
                Prefetch('groups', LocationGroup.objects.order_by(
                    '-category__priority', '-hierarchy', '-priority'
                ).only(
                    'color', 'category', 'priority', 'hierarchy', 'category__priority', 'category__allow_areas'
                ))
            )
            for area in areas:
                area.opacity = 0.5
            areas = sorted(areas, key=self.area_sorting_func)

            results = chain(
                buildings,
                other_spaces_lower,
                doors,
                other_spaces,
                [space],
                areas,
                space.holes.all().only('geometry', 'space'),
                space.stairs.all().only('geometry', 'space'),
                space.ramps.all().only('geometry', 'space'),
                space.obstacles.all().only('geometry', 'space', 'color'),
                space.lineobstacles.all().only('geometry', 'width', 'space', 'color'),
                space.columns.all().only('geometry', 'space'),
                space.alsatudemarkers.all().only('geometry', 'space'),
                space.wifi_measurements.all().only('geometry', 'space'),
                space.pois.filter(POI.q_for_request(request)).only('geometry', 'space').prefetch_related(
                    Prefetch('groups', LocationGroup.objects.only(
                        'color', 'category', 'priority', 'hierarchy', 'category__priority', 'category__allow_pois'
                    ).filter(color__isnull=False))
                ),
                other_spaces_upper,
                graphedges,
                graphnodes
            )
        else:
            raise ValidationError('No level or space specified.')

        return Response(list(chain(
            [('update_cache_key', update_cache_key)],
            (self.conditional_geojson(obj, update_cache_key_match) for obj in results)
        )))

    @get_queryset
    def prefetch_related(self, *lookups):
        """
        We split up all prefetch related lookups into one-level prefetches
        and convert them into Prefetch() objects with custom querysets.
        This makes sure that the prefetch also happens on the virtually modified database.
        """
        lookups_qs = {tuple(lookup.prefetch_through.split('__')): lookup.queryset for lookup in lookups
                      if isinstance(lookup, Prefetch) and lookup.queryset is not None}
        for qs in lookups_qs.values():
            if not isinstance(qs, QuerySetWrapper):
                raise TypeError('Prefetch object queryset needs to be wrapped!')
        lookups = tuple((lookup.prefetch_through if isinstance(lookup, Prefetch) else lookup) for lookup in lookups)

        lookups_splitted = tuple(tuple(lookup.split('__')) for lookup in lookups)
        max_depth = max(len(lookup) for lookup in lookups_splitted)
        lookups_by_depth = []
        for i in range(max_depth):
            lookups_by_depth.append(set(tuple(lookup[:i+1] for lookup in lookups_splitted if len(lookup) > i)))

        lookup_models = {(): self._obj.model}
        lookup_querysets = {(): self.all()}
        for depth_lookups in lookups_by_depth:
            for lookup in depth_lookups:
                model = lookup_models[lookup[:-1]]._meta.get_field(lookup[-1]).related_model
                lookup_models[lookup] = model
                lookup_querysets[lookup] = lookups_qs.get(lookup, self._wrap_model(model).objects.all())

        for depth_lookups in reversed(lookups_by_depth):
            for lookup in depth_lookups:
                qs = lookup_querysets[lookup]
                prefetch = Prefetch(lookup[-1], qs)
                lower_qs = lookup_querysets[lookup[:-1]]
                lower_qs._obj = lower_qs._obj.prefetch_related(prefetch)

        return lookup_querysets[()]

def locations_for_request(request) -> Mapping[int, LocationSlug]:
    # todo this takes a long time because it's a lot of data, we might want to change that
    cache_key = 'mapdata:locations:%s' % AccessPermission.cache_key_for_request(request)
    locations = proxied_cache.get(cache_key, None)
    if locations is not None:
        return locations

    locations = LocationSlug.objects.all().order_by('id')

    conditions = []
    for model in get_submodels(Location):
        related_name = model._meta.default_related_name
        condition = Q(**{related_name + '__isnull': False})
        # noinspection PyUnresolvedReferences
        condition &= model.q_for_request(request, prefix=related_name + '__')
        conditions.append(condition)
        locations = locations.select_related(
            related_name + '__label_settings'
        ).prefetch_related(
            related_name + '__redirects'
        )
    locations = locations.filter(reduce(operator.or_, conditions))
    locations = locations.select_related('redirect', 'locationgroups__category')

    # prefetch locationgroups
    base_qs = LocationGroup.qs_for_request(request).select_related('category', 'label_settings')
    for model in get_submodels(SpecificLocation):
        locations = locations.prefetch_related(Prefetch(model._meta.default_related_name + '__groups',
                                                        queryset=base_qs))

    locations = {obj.pk: obj.get_child() for obj in locations}

    # add locations to groups
    locationgroups = {pk: obj for pk, obj in locations.items() if isinstance(obj, LocationGroup)}
    for group in locationgroups.values():
        group.locations = []
    for obj in locations.values():
        if not isinstance(obj, SpecificLocation):
            continue
        for group in obj.groups.all():
            group = locationgroups.get(group.pk, None)
            if group is not None:
                group.locations.append(obj)

    # add levels to spaces
    remove_pks = set()
    levels = {pk: obj for pk, obj in locations.items() if isinstance(obj, Level)}
    for pk, obj in locations.items():
        if isinstance(obj, LevelGeometryMixin):
            level = levels.get(obj.level_id, None)
            if level is None:
                remove_pks.add(pk)
                continue
            obj._level_cache = level

    # hide spaces on hidden levels
    for pk in remove_pks:
        locations.pop(pk)

    # add spaces to areas and POIs
    remove_pks = set()
    spaces = {pk: obj for pk, obj in locations.items() if isinstance(obj, Space)}
    for pk, obj in locations.items():
        if isinstance(obj, SpaceGeometryMixin):
            space = spaces.get(obj.space_id, None)
            if space is None:
                remove_pks.add(pk)
                continue
            obj._space_cache = space

    # hide locations on hidden spaces
    for pk in remove_pks:
        locations.pop(pk)

    # add targets to LocationRedirects
    levels = {pk: obj for pk, obj in locations.items() if isinstance(obj, Level)}
    for obj in locations.values():
        if isinstance(obj, LocationRedirect):
            obj._target_cache = locations.get(obj.target_id, None)

    # apply better space geometries
    for pk, geometry in get_better_space_geometries().items():
        if pk in locations:
            locations[pk].geometry = geometry

    # precache cached properties
    for obj in locations.values():
        # noinspection PyStatementEffect
        obj.subsatle, obj.order
        if isinstance(obj, GeometryMixin):
            # noinspection PyStatementEffect
            obj.point

    proxied_cache.set(cache_key, locations, 1800)

    return locations

    def _unsealed_prefetch_lookup(self, prefetch_lookup, to_attr=None):
        """
        Turn a string prefetch lookup or a Prefetch instance without an
        explicit queryset into a Prefetch object with an explicit queryset
        to prevent the prefetching logic from accessing sealed related
        managers and triggering a SealedObject exception.
        """
        if isinstance(prefetch_lookup, string_types):
            parts = prefetch_lookup.split(LOOKUP_SEP)
            opts = self.model._meta
            select_related = self.query.select_related or {}
            # Walk to the first non-select_related part of the prefetch lookup.
            for index, part in enumerate(parts, start=1):
                related_model = opts.get_field(part).related_model
                try:
                    select_related = select_related[part]
                except KeyError:
                    break
                opts = related_model._meta
            head, tail = LOOKUP_SEP.join(parts[:index]), LOOKUP_SEP.join(parts[index:])
            if related_model:
                queryset = related_model._default_manager.all()
                if tail:
                    queryset = queryset.prefetch_related(tail)
                if isinstance(queryset, SealableQuerySet):
                    queryset = queryset.seal()
                return models.Prefetch(head, queryset, to_attr=to_attr)
            # Some private fields such as GenericForeignKey don't have a remote
            # field as reverse relationships have to be explicit defined using
            # GenericRelation instances.
            return prefetch_lookup
        elif isinstance(prefetch_lookup, models.Prefetch):
            if prefetch_lookup.queryset is None:
                return self._unsealed_prefetch_lookup(
                    prefetch_lookup.prefetch_through,
                    to_attr=prefetch_lookup.to_attr,
                )
            elif isinstance(prefetch_lookup.queryset, SealableQuerySet):
                prefetch_lookup.queryset = prefetch_lookup.queryset.seal()
        return prefetch_lookup

@shared_task(bind=True, acks_late=True)  # use acks_late for tasks that can be safely re-run if they fail
@transaction.atomic(using='capdb')
def retrieve_images_from_cases(self, volume_id, update_existing=True):
    """
        Create or update case images for each volume
    """
    with record_task_status_for_volume(self, volume_id):
        cases = CaseMetadata.objects.filter(volume_id=volume_id) \
            .only('body_cache__html') \
            .order_by('id') \
            .select_related('body_cache') \
            .prefetch_related(Prefetch('caseimages', queryset=CaseImage.objects.only('hash', 'case'))) \
            .exclude(body_cache=None) \
            .filter(body_cache__html__contains='<img') \
            .select_for_update()

        if not update_existing:
            cases = cases.exclude(caseimages__isnull=False)

        for case in cases:
            case.retrieve_and_store_images()