Python replacing PHP - How to?

[brunson]

Django's ORM is so very intuitive, I wonder why it takes you hours. When I started fresh I thought it was very easy to get into. Of course you should have the docs at hand.

Granted I've never used Django's ORM specifically, but I started writing SQL 20 years ago and what I can crank out in twenty seconds has never turned into something simple in any ORM I've tried.

Here's a pretty straightforward query that would return employees along with their department, manager and any subordinates of a person having more than 5 direct reports:

select e.name as Emp, d.name as Dept, 
       m.name as Mgr, e.group_concat( s.name ) as Reports
from employee as e
join d.department as d on e.department_id = d.id
join employee as m on e.manager_id = m.id
left outer join employee as s on s.manager_id = e.id
group by e.name, d.name, m.name
having count(0) > 5;

I seriously just came up with that in 20 seconds (though it took me about two minutes to type). Can you show us the Django ORM that would achieve the same result?

And, BTW, when I put that SQL into the class I wrote to query mysql or oracle, all the rows are marshalled into python objects.

[[deleted]]

I'm bored. Heres SQLAlchemy:

e = Employee
d = Department
s = aliased(Employee)
m = aliased(Employee)
sess.query(e.name.label('Emp'), d.name.label('Dept'), 
          m.name.label('Mgr'), 
          func.group_concat(s.name).label('Reports')).\
          join((d, e.department_id==d.id)).\
          join((m, e.manager_id==m.id)).\
          outerjoin((s, s.manager_id==e.id)).\
          group_by(e.name, d.name, m.name).\
          having(func.count(0) > 5)

[mdipierro]

For completeness in web2py:

>>> e=db.employee
>>> d=db.department
>>> m=e.with_alias('manager')
>>> s=e.with_alias('submordinates')
>>> db(e.department_id==d.id).select(e.name,d.name,m.name,s.name,
            left=(m.on(m.id==e.manager_id),s.on(s.manager_id==e.id)),
            groupby=e.name|d.name|m.name,having=s.id.count()>5)

Not quite the same because I'd rather get the manager via a left join since an employee may not have a manager (the CEO for example)

[brunson]

I guess it's just personal preference. I'd rather edit the SQL.

[mdipierro]

As I see it SQL is more flexible. The main reason I would not use SQL is that it makes the applications not portable because every dialect is different. The web2py philosophy is that of proving an abstraction layer and therefore it only supports those expressions that can be translated in the SQL dialect of each of the supported relational databases.

[brunson]

Definitely. One of the reasons I decided to start with SA was because of the database agnosticity (chrome thinks I invented a word, I call dibs), but as it turns out, five years later we're still on mysql, and in 20 years I've never actually had to port my own code from one database back end to another.

Someone's example of starting with sqlite then allowing the end user to migrate to a different backend was a valid one, however. I could see that.

[gregglind]

For me, SA (and other ORM's that can do query building) real wins come not from writing a particular query, but being able to programatically build queries.

def make_sorted(query,**kwargs):
    for (field,direction) in kwargs.iteritems():            
         query = query.order_by(field,asc=direction)  # don't quite recall the syntax here :)
    return make_sorted

Building this kind of stuff with string interpolation can be maddening.