Re: join models on Timestamp field without foreign keys

[Melvyn Sopacua]

On 14-8-2012 6:58, Jeff Dickens wrote:

> class Md_model(models.Model):
> Timestamp =
> models.DateTimeField(auto_now_add=True,unique=True,db_index=True)
> def __unicode__(self):
> return self.Timestamp.strftime('%Y %m %d %H:%M')
> def as_dict(self):
> return(model_to_dict(self))
> class Meta:
> abstract = True
>
> class md_10_1(Md_model):
> name='md_10_1'
> dataSource = 'http://192.168.0.10/auto/001'
> n1 = models.DecimalField(max_digits=10,decimal_places=3)
> n2 = models.DecimalField(max_digits=10,decimal_places=3)
> n3 = models.DecimalField(max_digits=10,decimal_places=3)
>
> class md_10_2(Md_model):
> name='md_10_2'
> dataSource = 'http://192.168.0.10/auto/002'
> n4 = models.DecimalField(max_digits=10,decimal_places=3)
> n5 = models.DecimalField(max_digits=10,decimal_places=3)

I'm wondering if this design isn't better:
class DataBatch(models.Model) :
name = models.CharField(max_length=10)
url = models.URLField(max_length=200)
time_stamp = models.DateTimeField(auto_now_add=True, db_index=True)

class DataType(models.Model) :
"""Different data types, n1 through n5 in your example"""
name = models.CharField(max_length=10)

class CollectedData(models.Model) :
batch = models.ForeignKey(DataBatch, related_name='data')
data_type = models.ForeignKey(DataType)
data = models.DecimalField(max_digits=10,decimal_places=3)


> Using a foreign key does't make sense to me since I can't predict that any
> particular model will be in every query.

If you need your own design, then you can actually set a foreign key to
NULL and use hasattr(Md_model, related_name) to test if the foreign key
is available.

--
Melvyn Sopacua

[Jeff Dickens]

On Tuesday, August 14, 2012 1:43:18 PM UTC-4, Dennis Lee Bieber wrote:

On Mon, 13 Aug 2012 21:58:59 -0700 (PDT), Jeff Dickens
<drea...@gmail.com> declaimed the following in
gmane.comp.python.django.user:

> Hi all.  I have a number of models, each of which is based on an abstract
> class that includes a Timestamp field.  The model definitions look like
> this:

>
> class Md_model(models.Model):
>     Timestamp =
> models.DateTimeField(auto_now_add=True,unique=True,db_index=True)
>     def __unicode__(self):
>         return self.Timestamp.strftime('%Y %m %d %H:%M')
>     def as_dict(self):
>         return(model_to_dict(self))
>     class Meta:
>         abstract = True
>
> class md_10_1(Md_model):
>     name='md_10_1'
>     dataSource = 'http://192.168.0.10/auto/001'
>     n1 = models.DecimalField(max_digits=10,decimal_places=3)
>     n2 = models.DecimalField(max_digits=10,decimal_places=3)
>     n3 = models.DecimalField(max_digits=10,decimal_places=3)
>
> class md_10_2(Md_model):
>     name='md_10_2'
>     dataSource = 'http://192.168.0.10/auto/002'
>     n4 = models.DecimalField(max_digits=10,decimal_places=3)
>     n5 = models.DecimalField(max_digits=10,decimal_places=3)
>

        This example makes me think the design is faulty... Especially if
you mean you have more tables than just the two...

It's not.  Read on...

        Though I think better in SQL -- are "name" and "dataSource" items
that would be stored in the database? Right now, all they seem to do is
duplicate information as to which "model" the record represents.

No, they are not items that are stored in the database.  They exist because the script that populates the table (every minute) is generalized to handle an unlimited number of data models. It uses introspection for everything - as DRY as it can possibly be.  Otherwise it would very quickly become unmanageable.

        Given just your example I'd probably have created just one table
containing all of n1..n5, with option to permit Null values, and coded
to do INSERT if the timestamp didn't exist, and UPDATE if the timestamp
was already in the table.

There are now 8 data models and I have to be able to add more over time without having to reprocess the (voluminous) data that has been logged into the existing models.
 

        If you do mean you have half a dozen or more all similar I'd try to
isolate back to the form that would support the most... If all the data
fields are identical (all 10.3 decimal) say... (Again, I'm going to use
pseudo SQL)

create table Sources
(
        ID        integer auto increment primary key,
        timestamp        datetime,
        source        char(80),        #whatever length you need
        model        char(20)        #ditto
);

create table Fields
(
        ID integer auto increment primary key,
        sourceID integer foreign key Source (ID),
        fieldID integer,
        fieldValue        decimal
)

        These wouldn't fit as regular Django form without a lot of logic...
Instead of /n/ tables looking like

timestamp        n1        n2        n3 ...

you'd have

1        timestamp        http://...
2        timestamp        http://...

and

1        1        1        value
2        1        2        value
3        1        3        value
4        2        1        value
5        2        2        value
...

        This schema allows for any number of identical datatype values per
"record", but putting them /into/ record order requires looping over the
result set.



>
> What I want to do is join them all (actually some programatically defined
> subset of them) into a single query set or some other data structure that I
> can pass into the template.  This set would have only one Timestamp field,
> and all of the other fields from all of the other models that are to be
> joined.  She thre result would have Timestamp, n1, n2, n3, n4, n5 in the
> example above,

        INNER, LEFT OUTER, or RIGHT OUTER; if you needed both OUTER
conditions in one result set you'd have to run a UNION wrapped by a
SELECT DISTINCT

        Raw SQL might look like

select distinct * from
        (select md_10_1.timestamp as tstamp, n1, ..., n5 from md_10_1
                left join md_10_2 on md_10_1.timestamp = md_10_2.timestamp
        union
         select md_10_2.timestamp as tstamp, n1, ..., n5 from md_10_1
                right join md_10_2 on md_10_1.timestamp = md_10_2.timestamp)
where tstamp > starttime and tstamp < endtime
order by tstamp

{If the DBMS doesn't implement RIGHT JOIN make it a LEFT and swap the
table order around the clause}
--
        Wulfraed                 Dennis Lee Bieber         AF6VN
        wlf...@ix.netcom.com    HTTP://wlfraed.home.netcom.com/