Re: merge data from multiple models

[Kurtis Mullins]

Try building this query set in your view and use the .filter() method.

For example,

relevant_data = ModelA.objects.filter(unique_field__in=ModelB.objects.all().values('unique_field'))

https://docs.djangoproject.com/en/dev/ref/models/querysets/#in

On Jul 30, 2012 7:27 AM, "Joris" <joris.b...@gmail.com> wrote:

Dear list,

Apologies in advance if this is a FAQ. I did extensive searching through this list and the django docs but have not been able to find a solution.

I'm trying to display data from a postgres backend into a HTML table. Most of this data comes from a single table. Some columns however come from a highly complex raw SQL query that is very CPU-expensive to run. The two datasets cannot be combined into a single model for performance reasons. Both models do however have an identical unique field .  

 

As templates cannot do lookups into a dictionary or DB object, I now resort to doing a nested 'for' loop, where the template cycles through the whole dataset of model 1 for every record of model 2. Then if the unique field obtained from model 2 is found in model 1, the other valeus from model 1 are displayed. Below is the current code I use.  

This sounds hopelessly inefficient. Would someone be able to point me to a method  to make this more logical?

Many thanks

Joris

  {% for GROUP in GROUPS %}
<tr>
    <td>{{ GROUP.grnummer}}</td>
     {% for FIN in FINISH %}
           {% if FIN.grnummer = GROUP.grnummer %}<td>{{FIN.synthesis_finish}}</td>{% endif %}
     {% endfor %}

</tr>
   

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To view this discussion on the web visit https://groups.google.com/d/msg/django-users/-/tqaZqXWURo0J.
To post to this group, send email to django...@googlegroups.com.
To unsubscribe from this group, send email to django-users...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.

[Joris]

Thank you

but that still makes me end up with two models (GROUPS and FINISH), although now both have the same records and are in the same order. When it comes to template rendering I still need a nester for loop yes? 

I suppose faking a foreign key relation in the FINISH model (which is the CPU intensive one) toward the GROUPS model will allow direct linking, but that generates an independent SQL lookup for each record, even with select_related turned on afaik.

[Melvyn Sopacua]

On 30-7-2012 11:54, Joris wrote:

> I'm trying to display data from a postgres backend into a HTML table. Most
> of this data comes from a single table. Some columns however come from a
> highly complex raw SQL query that is very CPU-expensive to run. The two
> datasets cannot be combined into a single model for performance reasons.
> Both models do however have an identical unique field .

If this is not implemented as a OneToOneField, then do so. You can then
access the related object in the same way as the reverse of a ForeignKey
and use related_name to make this more intuitive.
Example:
from django.db import models

class FastModel(models.Model) :
name = models.CharField(max_length=32)

class SlowModel(models.Model) :
fast = models.OneToOneField(FastModel, related_name='slow',
primary_key=True)
description = models.TextField()
@property
def result(self) :
return 1 + 1


>>> from one.models import FastModel, SlowModel
>>> fast = FastModel.objects.get(pk=1)
>>> slow = SlowModel(fast=fast, description='slow model 1')
>>> slow.save()
>>> fast.slow
<SlowModel: SlowModel object>
>>> fast.slow.description
'slow model 1'
>>> fast.slow.result
2
--
Melvyn Sopacua

[Joris]

On Monday, July 30, 2012 3:52:31 PM UTC+2, Melvyn Sopacua wrote:

If this is not implemented as a OneToOneField, then do so. You can then 
access the related object in the same way as the reverse of a ForeignKey
and use related_name to make this more intuitive.

 

Yes that works great. Thanks!!

[Jirka Vejrazka]

Hi there,

> As templates cannot do lookups into a dictionary....

Templates can do lookups into a dictionary, see
https://docs.djangoproject.com/en/1.4/topics/templates/#variables
(check the "Behind the scenes" section).

From what you've described, I'd try to prepare the necessary data in
a view (using caching, if possible at all), build the appropriate data
structure to be displayed and then use the template layer to do a
"dummy" display. Then you'd have the full power of Python at your
disposal to perform the best access to your data and combining the
models together into and array of items or a dictionary, depending on
your need.

HTH

Jirka

[Melvyn Sopacua]

On 30-7-2012 19:50, joris benschop wrote:
>
>
> Op maandag 30 juli 2012 16:06:38 UTC+2 schreef Joris het volgende:

> I'm sorry I spoke too soon. This does not work if the slow model is based
> on a raw SQL statement, as this statement is performed on an instance of
> the model. Referring to fastmodel.slow creates a new (unititialized)
> instance with no SQL backend and this crashes (relation does not exist).

Hmm, I guess I'm missing the "real world use case" for this. Most
importantly, I'm missing how this slow model relates to the fast model
and what kind of query it is executing. I'm especially curious about the
"as this statement is executed as an instance of the model" bit.

Using a view in PostgreSQL for that SQL statement and tying that to a
model seems like the obvious solution, but care should be taken with the
syncdb command (as in, the model tied to the view should not be 'synced').

--
Melvyn Sopacua

[Joris]

> Hmm, I guess I'm missing the "real world use case" for this. Most 
> importantly, I'm missing how this slow model relates to the fast model 
> and what kind of query it is executing. I'm especially curious about the 
> "as this statement is executed as an instance of the model" bit. 

Ok, I'll try to explain this better. The fast model is coupled directly to a table. The Slow model is not, it is activated by a specific sql query that depends on a specific condition.

AFAIK to get data from the slow model, I do:

SLOMO = SlowModel.objects.raw( SQLstatement ,[CONDITION]) 

However, if I set the primary_key of SlowModel to OneToOne (pointing to FastModel), as suggested above, and try to retreive SlowModel data from FastModel  (FastModelInstance.slow) I get an error that data is missing. My guess was that this was because I did not manage to tell the instance of Fastmodel that it first needs to pass the SQL to its SlowModel connection before I can ask it questions.

#-------------------------------

class FastModel(models.Model):
    idgroup = models.IntegerField(primary_key=True)
    field1 = models.IntegerField()
    field2 = models.CharField(max_length=250, blank=True)
    field3 = models.CharField(max_length=250, blank=True)
    class Meta:
        db_table = u'my_db_table'
   

class SlowModel(models.Model): 

    rid_fastmodel = models.IntegerField(primary_key=True)

    calcfield1 = models.TextField(blank=True)
    calcfield2= models.TextField(blank=True)

SQLstatement = "SELECT rid_fastmodel, calcfield1, calcfield2 from (SELECT ....) JOIN (...) JOIN (...) WHERE some_condition = %s"

Is this better?

thanks

joris

 

[Melvyn Sopacua]

On 31-7-2012 14:36, Joris wrote:

> class SlowModel(models.Model):
> rid_fastmodel = models.IntegerField(primary_key=True)
> calcfield1 = models.TextField(blank=True)
> calcfield2= models.TextField(blank=True)
>
> SQLstatement = "SELECT rid_fastmodel, calcfield1, calcfield2 from (SELECT
> ....) JOIN (...) JOIN (...) WHERE some_condition = %s"
>
> Is this better?

Yep, dive into PostgreSQL views [1]. It should be possible to create a
view that yields the same fields as your SlowModel class and you can use
a OneToOneField in the SlowModel corresponding the view's rid_fastmodel.
syncdb becomes your enemy, but it is the cleanest solution.

[1] http://www.postgresql.org/docs/9.1/static/sql-createview.html,
http://www.postgresql.org/docs/9.1/static/tutorial-views.html
--
Melvyn Sopacua

[Melvyn Sopacua]

On 31-7-2012 14:36, Joris wrote:
>

>> Hmm, I guess I'm missing the "real world use case" for this. Most
>> importantly, I'm missing how this slow model relates to the fast model
>> and what kind of query it is executing. I'm especially curious about the
>> "as this statement is executed as an instance of the model" bit.
>
> Ok, I'll try to explain this better. The fast model is coupled directly to
> a table. The Slow model is not, it is activated by a specific sql query
> that depends on a specific condition.

I thought about an alternative, since it seems your only reason to split
the models is performance and you prefer to have one model.
The prerequisite is that you know when you want to have access to the
slow data and make it available to the view.

The idea is borrowed from django.contrib.gis.db and it's GeoManager and
consists of implementing the raw sql as a method of the ModelManager and
adding the result of the query as attributes of the model.
For reference you can look at for example GeoQueryset.area()[1].

This will give you the ability to specify the queryset on a generic
class-based view to either be model.objects.all() for the fast version
or model.objects.calculate_slow_stuff() for the slow version.

[1]
https://docs.djangoproject.com/en/1.4/ref/contrib/gis/geoquerysets/#django.contrib.gis.db.models.GeoQuerySet.area
--
Melvyn Sopacua