CODEWITHSUNDEEP
djangopostgresql
Diemuzi
Diemuzi

Reputation: 3527

Query between two numbers

Django Version 1.9.5

What I'm essentially after is this query:

SELECT *
FROM "ipaddress_setup"
WHERE '167837954' BETWEEN "start_ipaddress" AND "end_ipaddress"

In the query there may be additional WHERE statements, here is an example of what I've got so far:

from django.core.paginator import Paginator
from database.models import IpaddressSetup
from django.db.models import Q
import ipaddress

class ServiceSearch:
    def __init__(self, request, get):
        self.request = request

        self.get = get

    def search(self):
        args = ()
        context = {}

        if 'name' in self.get and self.get['name'] is not None:
            context['name__icontains'] = self.get['name']

        if 'pool' in self.get and self.get['pool'] is not None:
            try:
                ip = ipaddress.ip_address(self.get['pool'])

                args = (Q(start_ipaddress__gte=int(ip)) | Q(end_ipaddress__lte=int(ip)),)
            except ValueError:
                pass

        if 'ipaddress_type' in self.get and self.get['ipaddress_type'] is not None:
            context['ipaddress_type__exact'] = self.get['ipaddress_type']

        if 'assigned' in self.get and self.get['assigned'] is not None:
            context['assigned__exact'] = self.get['assigned']

        if 'status' in self.get and self.get['status'] is not None:
            context['status__exact'] = self.get['status']

        result = IpaddressSetup.objects.all().filter(*args, **context).order_by('name')

        return Paginator(result, self.request.user.max_search)

This is used in a search feature for finding IP Addresses in an allotted pool. I store the IP's as INT's for starting ipaddress / ending ipaddress in two different columns named start_ipaddress and end_ipaddress

But as you can see, I'm also allowing the ability to search for the pool name, type of IP (ipv4/ipv6), assigned (public/reserved), and status (enabled/disabled)

The only problem I am having right now is trying to get the BETWEEN query working on the start_ipaddress / end_ipaddress.

I've tried using GTE/LTE queries, but then it returns other IP Pools that may also fall within the search'd params, but I'm after more of a concrete way of finding IP's between a pool.

enter image description here

Based on this image and my search params, my hopes is to only return 1 record instead of the 3 listed here.

I'd be happy to supply any other details.

Upvotes: 0

Views: 963

Answers (2)

RickyA
RickyA

Reputation: 16029

In the line

args = (Q(start_ipaddress__gte=int(ip)) | Q(end_ipaddress__lte=int(ip)),)

you are ORing the subqueries:

start_ip >= ip OR end_ip <= ip

That will yield everything.

AND them:

args = (Q(start_ipaddress__gte=int(ip)) & Q(end_ipaddress__lte=int(ip)),)

Upvotes: 1

C14L
C14L

Reputation: 12548

This means OR

args = (Q(start_ipaddress__gte=int(ip)) | Q(end_ipaddress__lte=int(ip)),)

but you want AND

... WHERE "start_ipaddress" <= 167837954 AND "end_ipaddress" => 167837954

or in Django's 

context['start_ipaddress__lte'] = int(ip)
context['end_ipaddress__gte'] = int(ip)

Upvotes: 1

Related Questions