Skip to content

Instantly share code, notes, and snippets.

@shangxiao

shangxiao/django-cte.md

Last active May 17, 2018 02:00
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save shangxiao/e35151f85a41764b28598aae4d649682 to your computer and use it in GitHub Desktop.
Save shangxiao/e35151f85a41764b28598aae4d649682 to your computer and use it in GitHub Desktop.
Download ZIP
CTE's in Django
Raw
django-cte.md

What's a CTE?

Slides 19-69 of Markus Winand's "Modern SQL":

Sample Problem

If I had a bunch of users with some activity each day, how can I construct a query
to get their last activity object on that day for each of the days?

Consider the following table:

+—————————————————————————————————————————————————————————————————————————————+
|                                 Activity                                    |
+-----------------------------------------------------------------------------|
|  Who?       |   When?   |    What?                                          |
+-------------+-----------+---------------------------------------------------|
|  g1eb       |   9am     |    Go to work                                     |
|  g1eb       |   5pm     |    Knock-off time!                                |
|  shangxiao  |   9am     |    Go to work                                     |
|  shangxiao  |   5pm     |    Knock-off time!                                |
|  shangxiao  |   6pm     |    Attend MelbDjango                              |
+-----------------------------------------------------------------------------+

We want to know what each person's final activity is:

+-----------------------------------------------------------------------------+
|  g1eb       |   5pm     |    Knock-off time!                                |
|  shangxiao  |   6pm     |    Attend MelbDjango                              |
+-----------------------------------------------------------------------------+

http://sqlfiddle.com/#!15/5bc43/2

https://repl.it/@rapilabs/django-cte

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment