Re: sql union all to def()

[Rafael E. Ferrero]

Tommy, an store procedure its a piece of sql code that its executed by the
data base server (take a look here
http://www.postgresql.org/docs/8.0/static/plpgsql.html), so you execute
that procedure from django with a raw sql statement.


Im not familiar with postgresql but in firebird you can do:
SET TERM ^ ;
CREATE  PROCEDURE COUNT_VALUES
RETURNS (
val Integer,
valcount integer
)
AS
BEGIN

for select val, count(*) as valcount
from (
select number_1 as val from xscale
union all
select number_2 as val from xscale
union all
select number_3 as val from xscale
union all
select number_4 as val from xscale
union all
select number_5 as val from xscale
union all
select number_6 as val from xscale
) as baseview
group by val order by valcount desc, val asc
into :val, :valcount
do begin
suspend;
end
END^
SET TERM ; ^


and call him with raw sql query SELECT p.VAL, p.VALCOUNT FROM COUNT_VALUES p

see ya



2013/4/11 Tommy 

> Hi Rafael,
>
> Thank you for the reply.  Not too sure if I undersatnd all of it, though.
> My learning curve is more down than up.
>
> You mentioned a possibly better process "using the store procedure".  Do
> you know of any examples of what you have in mind, perhaps?
>
> I have not found a function in Django nor Python that comes close to what
> I am trying to do. I asked here hoping that someone might have some
> pointers.
>
> You are correct I am attempting to collect an overall count of similiar
> values from across all the fields in the model.  Took a while to find the
> SQL on-line.
>
> Tommy.
>
>
> On Wednesday, 10 April 2013 23:03:20 UTC+1, vicherot wrote:
>
>> I think that you can get all your table data on a Django View and then
>> make an algorithm to have your data, maybe using a recursive function.
>>
>> Like i understand you are putting together every single field in one...
>> so your first result its one field (val) then you are counting all similar
>> value, so
>> 1- create an array and get all number_1
>> 2- append to previus object all number_2
>> 3- repeat for every field
>> 4- order ascending or descending the array
>> 5- take first element and compare with others and count equal values and
>> then delete that counted value (this reduce the extention of your array)
>> 6- move one position and repeat and proced, like point 5, from them.
>> 7- do that to the last element in the array
>> This is a quick aproach... but you have an idea now. You can do it better
>> than me.
>>
>> I dont say that is the best choice... maybe for this particulary example
>> using the store procedure its better ( for the health of the server cpu :P )
>>
>> I dont know if python or django have a function to resolve your
>> requirements.
>>
>> See ya
>>
>>
>> 2013/4/10 Tommy 
>>
>>> Hi.
>>>
>>> I have the SQL query below that works in PostgreSQL.
>>>
>>> I am attempting to learn to draw graphs/trend lines to a web page in the
>>> Django Framework
>>>
>>> Rather than use raw SQL queries I would like to learn how to get the
>>> same results in a Django purists fashion.  I have searched but I cannot
>>> find a solution or a solution may exist but I do not understand enough to
>>> recognize.
>>>
>>> Is this possible?  If so, how?
>>>
>>> select val, count(*) as valcount
>>>  from (
>>>  select number_1 as val from xscale
>>>  union all
>>>  select number_2 as val from xscale
>>> union all
>>>  select number_3 as val from xscale
>>> union all
>>>  select number_4 as val from xscale
>>> union all
>>>  select number_5 as val from xscale
>>> union all
>>>  select number_6 as val from xscale
>>>   ) as baseview
>>>  group by val order by valcount desc, val asc;
>>>
>>> models.py
>>>
>>> from django.db import models
>>>
>>> class XScale(models.Model):
>>> gate = models.TextField(null=False)
>>> number_1 = models.IntegerField(null=**False)
>>> number_2 = models.IntegerField(null=**False)
>>> number_3 = models.IntegerField(null=**False)
>>> number_4 = models.IntegerField(null=**False)
>>> number_5 = models.IntegerField(null=**False)
>>> number_6 = models.IntegerField(null=**False)
>>>
>>> class Meta:
>>> db_table = u'xscale'
>>>
>>> def __unicode__(self):
>>> return unicode (self.gate)
>>>
>>> Thanks for your help,
>>> Tommy.
>>>
>>>
>>>  --
>>> You received this message because you are subscribed to the Google
>>> Groups "Django users" group.
>>> To unsubscribe from this group and stop receiving emails from it, send
>>> an email to django-users...@**googlegroups.com.
>>> To post to this group, send email to django...@googlegroups.com.
>>>
>>> Visit this group at 
>>> http://groups.google.com/**group/django-users?hl=en
>>> .
>>> For more options, visit 
>>> ht

Re: sql union all to def()

[Tommy]

Hi Rafael,

Thank you for the reply.  Not too sure if I undersatnd all of it, though.  
My learning curve is more down than up.  

You mentioned a possibly better process "using the store procedure".  Do 
you know of any examples of what you have in mind, perhaps?

I have not found a function in Django nor Python that comes close to what I 
am trying to do. I asked here hoping that someone might have some pointers.

You are correct I am attempting to collect an overall count of similiar 
values from across all the fields in the model.  Took a while to find the 
SQL on-line.

Tommy.

On Wednesday, 10 April 2013 23:03:20 UTC+1, vicherot wrote:
>
> I think that you can get all your table data on a Django View and then 
> make an algorithm to have your data, maybe using a recursive function.
>
> Like i understand you are putting together every single field in one... so 
> your first result its one field (val) then you are counting all similar 
> value, so
> 1- create an array and get all number_1
> 2- append to previus object all number_2
> 3- repeat for every field
> 4- order ascending or descending the array
> 5- take first element and compare with others and count equal values and 
> then delete that counted value (this reduce the extention of your array)
> 6- move one position and repeat and proced, like point 5, from them.
> 7- do that to the last element in the array
> This is a quick aproach... but you have an idea now. You can do it better 
> than me.
>
> I dont say that is the best choice... maybe for this particulary example 
> using the store procedure its better ( for the health of the server cpu :P )
>
> I dont know if python or django have a function to resolve your 
> requirements.
>
> See ya
>
>
> 2013/4/10 Tommy >
>
>> Hi.
>>
>> I have the SQL query below that works in PostgreSQL.
>>
>> I am attempting to learn to draw graphs/trend lines to a web page in the 
>> Django Framework
>>
>> Rather than use raw SQL queries I would like to learn how to get the same 
>> results in a Django purists fashion.  I have searched but I cannot find a 
>> solution or a solution may exist but I do not understand enough to 
>> recognize.
>>
>> Is this possible?  If so, how?  
>>
>> select val, count(*) as valcount
>>  from (
>>  select number_1 as val from xscale
>>  union all
>>  select number_2 as val from xscale
>> union all
>>  select number_3 as val from xscale
>> union all
>>  select number_4 as val from xscale
>> union all
>>  select number_5 as val from xscale
>> union all
>>  select number_6 as val from xscale
>>   ) as baseview
>>  group by val order by valcount desc, val asc;
>>
>> models.py
>>
>> from django.db import models
>>
>> class XScale(models.Model):
>> gate = models.TextField(null=False)
>> number_1 = models.IntegerField(null=False)
>> number_2 = models.IntegerField(null=False)
>> number_3 = models.IntegerField(null=False)
>> number_4 = models.IntegerField(null=False)
>> number_5 = models.IntegerField(null=False)
>> number_6 = models.IntegerField(null=False)
>>
>> class Meta:
>> db_table = u'xscale'
>>
>> def __unicode__(self):
>> return unicode (self.gate)
>>
>> Thanks for your help,
>> Tommy.
>>
>>  -- 
>> You received this message because you are subscribed to the Google Groups 
>> "Django users" group.
>> To unsubscribe from this group and stop receiving emails from it, send an 
>> email to django-users...@googlegroups.com .
>> To post to this group, send email to django...@googlegroups.com
>> .
>> Visit this group at http://groups.google.com/group/django-users?hl=en.
>> For more options, visit https://groups.google.com/groups/opt_out.
>>  
>>  
>>
>
>
>
> -- 
> Rafael E. Ferrero
> Claro: (03562) 15514856 
>

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: sql union all to def()

[Rafael E. Ferrero]

I think that you can get all your table data on a Django View and then make
an algorithm to have your data, maybe using a recursive function.

Like i understand you are putting together every single field in one... so
your first result its one field (val) then you are counting all similar
value, so
1- create an array and get all number_1
2- append to previus object all number_2
3- repeat for every field
4- order ascending or descending the array
5- take first element and compare with others and count equal values and
then delete that counted value (this reduce the extention of your array)
6- move one position and repeat and proced, like point 5, from them.
7- do that to the last element in the array
This is a quick aproach... but you have an idea now. You can do it better
than me.

I dont say that is the best choice... maybe for this particulary example
using the store procedure its better ( for the health of the server cpu :P )

I dont know if python or django have a function to resolve your
requirements.

See ya


2013/4/10 Tommy 

> Hi.
>
> I have the SQL query below that works in PostgreSQL.
>
> I am attempting to learn to draw graphs/trend lines to a web page in the
> Django Framework
>
> Rather than use raw SQL queries I would like to learn how to get the same
> results in a Django purists fashion.  I have searched but I cannot find a
> solution or a solution may exist but I do not understand enough to
> recognize.
>
> Is this possible?  If so, how?
>
> select val, count(*) as valcount
>  from (
>  select number_1 as val from xscale
>  union all
>  select number_2 as val from xscale
> union all
>  select number_3 as val from xscale
> union all
>  select number_4 as val from xscale
> union all
>  select number_5 as val from xscale
> union all
>  select number_6 as val from xscale
>   ) as baseview
>  group by val order by valcount desc, val asc;
>
> models.py
>
> from django.db import models
>
> class XScale(models.Model):
> gate = models.TextField(null=False)
> number_1 = models.IntegerField(null=False)
> number_2 = models.IntegerField(null=False)
> number_3 = models.IntegerField(null=False)
> number_4 = models.IntegerField(null=False)
> number_5 = models.IntegerField(null=False)
> number_6 = models.IntegerField(null=False)
>
> class Meta:
> db_table = u'xscale'
>
> def __unicode__(self):
> return unicode (self.gate)
>
> Thanks for your help,
> Tommy.
>
>  --
> You received this message because you are subscribed to the Google Groups
> "Django users" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to django-users+unsubscr...@googlegroups.com.
> To post to this group, send email to django-users@googlegroups.com.
> Visit this group at http://groups.google.com/group/django-users?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>



-- 
Rafael E. Ferrero
Claro: (03562) 15514856

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

sql union all to def()

[Tommy]

Hi.

I have the SQL query below that works in PostgreSQL.

I am attempting to learn to draw graphs/trend lines to a web page in the 
Django Framework

Rather than use raw SQL queries I would like to learn how to get the same 
results in a Django purists fashion.  I have searched but I cannot find a 
solution or a solution may exist but I do not understand enough to 
recognize.

Is this possible?  If so, how?  

select val, count(*) as valcount
 from (
 select number_1 as val from xscale
 union all
 select number_2 as val from xscale
union all
 select number_3 as val from xscale
union all
 select number_4 as val from xscale
union all
 select number_5 as val from xscale
union all
 select number_6 as val from xscale
  ) as baseview
 group by val order by valcount desc, val asc;

models.py

from django.db import models

class XScale(models.Model):
gate = models.TextField(null=False)
number_1 = models.IntegerField(null=False)
number_2 = models.IntegerField(null=False)
number_3 = models.IntegerField(null=False)
number_4 = models.IntegerField(null=False)
number_5 = models.IntegerField(null=False)
number_6 = models.IntegerField(null=False)

class Meta:
db_table = u'xscale'

def __unicode__(self):
return unicode (self.gate)

Thanks for your help,
Tommy.

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.