Есть две модели, как реализовать на Django ORM конвертацию IP в число и приджойнить расположение из второй модели?
Модели:
class Event(models.Model):
id = models.AutoField(db_column='ID', primary_key=True)
username = models.CharField(max_length=30, blank=True, null=True)
netbiosname = models.CharField(max_length=30, blank=True, null=True)
date = models.DateTimeField()
ip = models.CharField( max_length=20, blank=True, null=True)
class IpSubnet(models.Model):
id_ip_subnet = models.AutoField(primary_key=True)
start_ip_int = models.BigIntegerField(blank=True, null=True)
end_ip_int = models.BigIntegerField(blank=True, null=True)
location = models.CharField(max_length=150, blank=True, null=True)
Сейчас реализовано через вью на стороне SQL:
with tabl as (
SELECT
ID
, UserName
, NetBiosName
, Date
, IP
, case
when
charindex('.', IP) > 0
then convert(bigint, parsename(IP, 4)) * 16777216
+ convert(bigint, parsename(IP, 3)) * 65536
+ convert(bigint, parsename(IP, 2)) * 256
+ convert(bigint, parsename(IP, 1))
else
0
end as IPINT
from Event with (nolock)
)
select
tabl.ID
, UserName
, NetBiosName
, Date
, IP
, Location
from
tabl
left join (
select
location
,start_ip_int
,end_ip_int
from
ip_subnet with (nolock)
) loc
on IPINT between start_ip_int and end_ip_int
