SQL select statements to Django ORM

[Gerald Brown]

As I have said previously, I am new to Django ORM so I would like to know howto/find reference to Django ORM Query to the following SQL Select statements:

1. From Mariadb.

SELECT name, date_of_birth, TIMESTAMPDIFF(YEAR,date_of_birth,'2014-08-02'/or curdate()) AS age FROM some table.

2. General SQL statement
SELECT field1, field2, field10 from some table

3. How to see all of the data from those statements which SQL shows me. 

I have read (several times) the documentation @ https://docs.djangoproject.com/en/2.0/topics/db/queries/. They show numerous 
examples of Queries but none of them show the results from those queries.

When I do get results to show ONLY the fields that are in my __str__ statement show up. If I add additional fields they also
show up in my FK list, which I don't want.

Thanks for all previous help from this group.

[Todor Velichkov]

You can use Raw SQL expressions only for the custom field which you want to annotate.

MyModel.objects.all().annotate(age=RawSQL("TIMESTAMPDIFF(YEAR, date_of_birth, curdate())))

[Melvyn Sopacua]

On zondag 13 mei 2018 03:44:37 CEST Gerald Brown wrote:
> As I have said previously, I am new to Django ORM so I would like to know
> howto/find reference to Django ORM Query to the following SQL Select
> statements:

Do you want to use SQL or use Django? Pick one.

> 1. From Mariadb.
>
> SELECT name, date_of_birth,

> *TIMESTAMPDIFF(YEAR,date_of_birth,'2014-08-02'/or curdate())* AS age FROM
> some table.

The Django way:

Use case: I want to gather a person's name, date of birth and age for all
people modeled using Person.

from datetime import date

class Person(models.Model):
name = models.CharField(max_length=100)
dob = models.DateField()

@property
def age(self) -> int:
diff = date.today() - self.dob
return diff.year

people = Person.objects.defer('name', 'dob').all()

> 2. General SQL statement
> SELECT field1, field2, field10 from some table

See defer() as above.

> 3. How to see all of the data from those statements which SQL shows me.

Use case: I want to show people's name, date of birth and age.

Using above "people":

print('name', 'date of birth', 'age')
for person in people:
print(people.name, people.dob, people.age)

--
Melvyn Sopacua

[Matthew Pava]

You could use the ORM to get the difference of two dates like so:
Person.objects.annotate(age=ExpressionWrapper(Cast(Now(), DateField()) - F('dob'), output_field=DurationField()))
https://docs.djangoproject.com/en/2.0/ref/models/database-functions/#cast
https://docs.djangoproject.com/en/2.0/ref/models/database-functions/#now

--
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 https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/4418595.HAJx60YL8B%40fritzbook.
For more options, visit https://groups.google.com/d/optout.

[Melvyn Sopacua]

On maandag 14 mei 2018 15:38:01 CEST Matthew Pava wrote:
> You could use the ORM to get the difference of two dates like so:
> Person.objects.annotate(age=ExpressionWrapper(Cast(Now(), DateField()) -
> F('dob'), output_field=DurationField()))
> https://docs.djangoproject.com/en/2.0/ref/models/database-functions/#cast
> https://docs.djangoproject.com/en/2.0/ref/models/database-functions/#now

Sure, but what's the upside? The downside is that your objects are bigger,
more data transferred from db to python layer, more complex query.

What I'm trying to conveigh here, is not to shoehorn the ORM into your notion
of SQL, but to think in models, model fields, object properties and model
relations.

There will be times where the ORM needs help from SQL, but pick your battles.
--
Melvyn Sopacua

[Matthew Pava]

Maybe it's just me, but I had a report I was generating that took 3.5 minutes to complete that utilized for loops and the ORM. I managed to generate the same report in 18 seconds using a RawSQL query.
So what's the upside? Speed. I would understand if the times were comparable, but they're not.
I find myself becoming more and more blocked by the limited functionality of the ORM in this regard. Perhaps it's just my application--maybe Django isn't the best fit for it.

But if in your use case, you had a million records in which you were trying to filter by age using Python loops, I have a suspicion that it would be slower than using the database functions that Django does include. (Saying that, I would like to see the Django community make a greater attempt to making those functions more pythonic.)

-----Original Message-----
From: django...@googlegroups.com [mailto:django...@googlegroups.com] On Behalf Of Melvyn Sopacua
Sent: Monday, May 14, 2018 9:29 AM
To: django...@googlegroups.com
Subject: Re: SQL select statements to Django ORM

--
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 https://groups.google.com/group/django-users.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/4047248.Nn77cOrblS%40fritzbook.

[Melvyn Sopacua]

On maandag 14 mei 2018 16:42:49 CEST Matthew Pava wrote:

> But if in your use case, you had a million records in which you were trying
> to filter

Full stop right there. No one is filtering anything in the original use case,
so you're comparing apples and oranges.
Filtering you would do in the database, but you'd still not use annotate,
cause you can simply filter on the dob using standard __gte/__lte etc. lookups.
--
Melvyn Sopacua

[Matthew Pava]

It was a conditional statement, albeit a seemingly poorly constructed one.

"If you had a million records you were trying to filter..."

Would you not just replace "annotate" with "filter" and use the same DurationField() expression?

Nevertheless, in my case, I would use the annotation for a report that shows counts based on certain criteria.

-----Original Message-----
From: django...@googlegroups.com [mailto:django...@googlegroups.com] On Behalf Of Melvyn Sopacua
Sent: Monday, May 14, 2018 4:32 PM
To: django...@googlegroups.com
Subject: Re: SQL select statements to Django ORM

--
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 https://groups.google.com/group/django-users.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/1618227.LY2WuIfJ59%40fritzbook.