Re: Need Help Converting SQL statement into Django Query Syntax

On Sun, 6 May 2012 10:44:30 -0700, Mark Phillips
<mark@phillipsmarketing.biz> declaimed the following in
gmane.comp.python.django.user:


> It is valid SQL for MySql - see the documentation for joins (
> http://dev.mysql.com/doc/refman/5.1/en/join.html). Quoted from the
> reference:
>
> A table reference is also known as a join expression.
>
> The syntax of *table_factor* is extended in comparison with the SQL
> Standard. The latter accepts only*table_reference*, not a list of them
> inside a pair of parentheses.
>
> This is a conservative extension if we consider each comma in a list of *
> table_reference* items as equivalent to an inner join. For example:
>

Okay -- while I've used MySQL, I've stayed with straight (or as much
as possible) standard SQL. Standard SQL should be more amenable to
translation into any object-oriented database system.

> Not sure whay it matters, but email_type is one of (home, work, mobile,
> other). Primary_emial is a boolean value to indicate the person's preferred
> email.
>

It was more a concern that the status might have belonged in the
email address list (ie, no intersect table needed in that case, but...)

>
> In my particular application (youth sports teams), some of the people
> (youth players) use their parent's email for the team emails, so there are
> instances when two people share one email address. I have similar tables
> for addresses and phone numbers.
>

... this may justify it...

OTOH; you may risk an update anomaly... If one of the players should
obtain their own email address rather than using their parents'
address... You can't validly "update" the player address without risking
changing the parents' address -- you have to /add/ a new address, then
link the player to the new address.

While it may duplicate email address entries, I believe the nature
of the data should be a one-many relationship, with NO intersect table
needed.

This is unlike a book<>author relationship where author includes a
contact address. Regardless of the book, you want only one author
address. You have a case that would be the equivalent of a book author
changing address /only for one book/.

> Back to my original question....how would one translate this SQL query into
> a django query?
>
Have you explored
https://docs.djangoproject.com/en/dev/topics/db/models/
https://docs.djangoproject.com/en/dev/ref/models/fields/#django.db.models.ManyToManyField.through

and
https://docs.djangoproject.com/en/dev/ref/models/querysets/
(search for select_related)

Object-Relational Mappers may not automate into one SQL select what
you are asking for -- they may break it into repetitive selects for the
related data /when asked for/. (That is, you'd retrieve the persons
based on some criteria, but the email addresses won't be fetched until
you ask for the email address of a specific person in the first result
set).


--
Wulfraed Dennis Lee Bieber AF6VN
wlfraed@ix.netcom.com HTTP://wlfraed.home.netcom.com/

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