Re: Recreating SQL query in ORM
Thanks Simon, that's a great help.
On Wednesday, 31 July 2019 15:03:55 UTC+1, Simon Charette wrote:
>
> Hello,
>
> assuming you have a FullMatch model mapped to your FullMatches table
> the following should do.
>
> FullMatch.objects.filter(
> job_id=job_id,
> ).values(
> seq=Concat('loading_code', ...),
> ids=Concat('loading_id', ),
> ).annotate(
> total=Count('*'),
> ).order_by('-total')
>
> Using .values() before an annotation of an aggregate function uses the
> provided columns
> from grouping.
>
> Cheers,
> Simon
>
> Le mardi 30 juillet 2019 12:56:56 UTC-4, Jonathan Spicer a écrit :
>>
>> Hello,
>>
>> I have an sql query that I would like to recreate using the ORM. Would it
>> be possible for someone to give me some pointers.
>>
>> select count(*) as total,
>>
>> concat(loading_code,code1_code,code2_code,code3_code,code4_code) as seq,
>>
>> concat(loading_id,',',code1_id,',',code2_id,',',code3_id,',',code4_id) as
>> ids
>> from FullMatches where job_id = %s group by seq, ids
>> order by total desc
>>
>> Thanks in advance.
>>
>> Johnny
>>
>
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/5f6f271a-4d8d-4d02-80c3-9085f15f6ce4%40googlegroups.com.
Re: Recreating SQL query in ORM
Hello,
assuming you have a FullMatch model mapped to your FullMatches table
the following should do.
FullMatch.objects.filter(
job_id=job_id,
).values(
seq=Concat('loading_code', ...),
ids=Concat('loading_id', ),
).annotate(
total=Count('*'),
).order_by('-total')
Using .values() before an annotation of an aggregate function uses the
provided columns
from grouping.
Cheers,
Simon
Le mardi 30 juillet 2019 12:56:56 UTC-4, Jonathan Spicer a écrit :
>
> Hello,
>
> I have an sql query that I would like to recreate using the ORM. Would it
> be possible for someone to give me some pointers.
>
> select count(*) as total,
>
> concat(loading_code,code1_code,code2_code,code3_code,code4_code) as seq,
>
> concat(loading_id,',',code1_id,',',code2_id,',',code3_id,',',code4_id) as
> ids
> from FullMatches where job_id = %s group by seq, ids order
> by total desc
>
> Thanks in advance.
>
> Johnny
>
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/276bf123-6990-4594-a9cf-8e9a8432d40e%40googlegroups.com.
Re: Recreating SQL query in ORM
yes On Tuesday, July 30, 2019 at 10:26:56 PM UTC+5:30, Jonathan Spicer wrote: > > Hello, > > I have an sql query that I would like to recreate using the ORM. Would it > be possible for someone to give me some pointers. > > select count(*) as total, > > concat(loading_code,code1_code,code2_code,code3_code,code4_code) as seq, > > concat(loading_id,',',code1_id,',',code2_id,',',code3_id,',',code4_id) as > ids > from FullMatches where job_id = %s group by seq, ids order > by total desc > > Thanks in advance. > > Johnny > -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/django-users/a24d2cbe-1230-4ef5-813f-6cc6739ccd0f%40googlegroups.com.
Re: Recreating SQL query in ORM
Hello John,
I did try that but it complained about the query not containing the primary
key.
I did wonder if there are methods within the ORM that can replicate it, the
tricky part being creating a value to group by from concatenated rows.
On Tuesday, 30 July 2019 19:14:32 UTC+1, John Bagiliko wrote:
>
> Do you want to make this exact query in Django?
> Then use Your_model.objects.raw("the query here except the last semi
> colon")
>
> On Tue, Jul 30, 2019, 4:56 PM Jonathan Spicer <
> joh...@spicersolutions.co.uk > wrote:
>
>> Hello,
>>
>> I have an sql query that I would like to recreate using the ORM. Would it
>> be possible for someone to give me some pointers.
>>
>> select count(*) as total,
>>
>> concat(loading_code,code1_code,code2_code,code3_code,code4_code) as seq,
>>
>> concat(loading_id,',',code1_id,',',code2_id,',',code3_id,',',code4_id) as
>> ids
>> from FullMatches where job_id = %s group by seq, ids
>> order by total desc
>>
>> Thanks in advance.
>>
>> Johnny
>>
>> --
>> 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...@googlegroups.com .
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/django-users/d91ae3ab-f111-4081-8a6e-26fad5a2f782%40googlegroups.com
>>
>>
>> .
>>
>
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/edd9244d-a320-4bfc-8859-b7aab1439c37%40googlegroups.com.
Re: Recreating SQL query in ORM
Do you want to make this exact query in Django?
Then use Your_model.objects.raw("the query here except the last semi colon")
On Tue, Jul 30, 2019, 4:56 PM Jonathan Spicer
wrote:
> Hello,
>
> I have an sql query that I would like to recreate using the ORM. Would it
> be possible for someone to give me some pointers.
>
> select count(*) as total,
>
> concat(loading_code,code1_code,code2_code,code3_code,code4_code) as seq,
>
> concat(loading_id,',',code1_id,',',code2_id,',',code3_id,',',code4_id) as
> ids
> from FullMatches where job_id = %s group by seq, ids order
> by total desc
>
> Thanks in advance.
>
> Johnny
>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/django-users/d91ae3ab-f111-4081-8a6e-26fad5a2f782%40googlegroups.com
>
> .
>
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/CAC26BE0cEq4c_UWcO9BKMThazgMEHOpLp3dXiU%2Bb_SNmPgU%2BSg%40mail.gmail.com.
Recreating SQL query in ORM
Hello, I have an sql query that I would like to recreate using the ORM. Would it be possible for someone to give me some pointers. select count(*) as total, concat(loading_code,code1_code,code2_code,code3_code,code4_code) as seq, concat(loading_id,',',code1_id,',',code2_id,',',code3_id,',',code4_id) as ids from FullMatches where job_id = %s group by seq, ids order by total desc Thanks in advance. Johnny -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/django-users/d91ae3ab-f111-4081-8a6e-26fad5a2f782%40googlegroups.com.
