Electronics & Programming

develissimo

Open Source electronics development and programming

  • You are not logged in.

#1 Nov. 8, 2005 22:04:57

Joey C.
Registered: 2009-11-02
Reputation: +  0  -
Profile   Send e-mail  

SQL 'group by' and 'having'?


Hello,

I'm using django to redevelop an old PHP app I wrote a while back.
Some of the queries I had written use the GROUP BY and HAVING clauses
in my sql queries (which may indicate insanity on my part, but it
seemed like a good idea at the time). The app itself is a
non-hierarchical keyword-based bookmark manager (I find it useful :).

Simplified, my model looks like:


class Bookmark(meta.Model):
title = meta.CharField(maxlength=64)
url = meta.CharField(maxlength=256)

class Tag(meta.Model):
name = meta.CharField(maxlength=32, unique=True)
bookmarks = meta.ManyToManyField(Bookmark, filter_interface=meta.HORIZONTAL)


If I select a couple of tags then I only want to see the bookmarks
that have all of those tags. So, I can do this easily enough
(non-essentials simplified):


from django.models.samwise2 import bookmarks, tags
def listing(request, **kw):
cur_tags_set = set(kw.get('cur_tags','').split('/')) # grab the
tags off the URL and parse

marks = bookmarks.get_list(select_related=True) # grab *all* bookmarks
marks_tags = [ (mark,set())
for mark in marks ]
if cur_tags:
marks =
marks_tags =


And that gives me the result I want (modulo typos). But, I expect
these tables to be relatively large, and the listing is probably the
most common action, so in the old PHP version, I did an SQL query that
looked like:

SELECT B.*
FROM bookmarks B, tags T, bookmark_tags BT
WHERE B.id=BT.bookmark_id AND BT.tag_id=T.id AND T.name IN ('tag1',
'tag2', 'tag3')
GROUP BY B.id
HAVING COUNT(B.id)=3

Well, the actual query was much less pretty, but it dealt with
permissions and ownership and whatnot. The general idea was to let
the DB do the hard work of sorting out the rows, because the DB has
got to be faster than PHP (and, ugly as SQL can be, it's better than
PHP for this kind of thing).

Admittedly, the python equivalent above isn't too bad (three cheers
for python's set datatype), but I suppose what I'm looking for is
something like:


from django.models.samwise2 import bookmarks,tags
def listing(request, **kw):
cur_tags_set = set(kw.get('cur_tags','').split('/'))
cur_tags = list(cur_tags_set)

if cur_tags:
marks = bookmarks.get_list(tables=,
where=,
group_by=,

having=,
select_related=True)
else:
marks = bookmarks.get_list(select_related=True)


Are there any plans to incorporate the GROUP BY and HAVING clauses
into the model API somehow, or is current best practice to just write
python to do it?

cheers,
--joey
(I hope this makes sense to someone)

Offline

#2 Nov. 8, 2005 22:35:01

Adrian H.
Registered: 2009-11-02
Reputation: +  0  -
Profile   Send e-mail  

SQL 'group by' and 'having'?


On 11/8/05, Joey Coleman <> wrote:
> Are there any plans to incorporate the GROUP BY and HAVING clauses
> into the model API somehow, or is current best practice to just write
> python to do it?

No, there aren't any immediate plans to incorporate GROUP BY and
HAVING. In your case, I'd suggest writing custom SQL, which is very
easy in Django. Check out this example:http://www.djangoproject.com/documentation/models/custom_methods/Adrian

--
Adrian Holovaty
holovaty.com | djangoproject.com | chicagocrime.org

Offline

#3 Nov. 9, 2005 08:39:11

Joey C.
Registered: 2009-11-02
Reputation: +  0  -
Profile   Send e-mail  

SQL 'group by' and 'having'?


On 11/8/05, Adrian Holovaty <> wrote:
>
> No, there aren't any immediate plans to incorporate GROUP BY and
> HAVING. In your case, I'd suggest writing custom SQL, which is very
> easy in Django. Check out this example:
>
>http://www.djangoproject.com/documentation/models/custom_methods/>

Ahh, that'll do it just fine.

thanks,
--joey

Offline

Board footer

Moderator control

Enjoy the 18th of November
PoweredBy

The Forums are managed by develissimo stuff members, if you find any issues or misplaced content please help us to fix it. Thank you! Tell us via Contact Options
Leave a Message
Welcome to Develissimo Live Support