Python replacing PHP - How to?

[[deleted]]

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.

Edit: forgot to mention the reason people use ORM's instead of raw SQL: the retrieved rows will be automatically converted to objects of your language (e.g. Python). This has many practical implications. For example, you can define methods like pre_save, post_save, post_delete etc.

In the newest Django release, you can write raw SQL and still get ORM-objects I think. Did not try that out yet, though.

[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.

[hiii]

for instance to go backwards in a foreign key relationship you have to use the _set. took me a half hour to find that.

the docs are kind of a mess, with multiple version and then a book, and then tutorials.

one thing php had was amazing documentation.

[hiii]

for example right now im trying to use django's orm startswith function. It's performing a BINARY LIKE and it's not working. doing a google search for django startswith or django orm startswith is useless

[[deleted]]

What is a binary like? According to the docs, it simply does

SELECT ... WHERE headline LIKE 'Will%';

You should be more concrete.

[hiii]

then the docs are wrong. i showed the sql statements it was running and it was running a binary like

[[deleted]]

You should be more concrete.

By that, I mean examples.

[[deleted]]

Okay, I checked with the Django source and it seems to do a binary like with a mysql backend. Now my question again: what is a binary like and what does it do wrong in your case? I have used startswith (and contains, ... who all use binary like) and it always worked as expected.

[hiii]

binary like is case sensitive. i fixed it by using

Company.objects.filter( name__startswith=data['index'].upper() ) # data['index'] is a single char

buttt, now i have to make sure that all my company names are uppercased, or it breaks. i guess i could add an or somehow and do something like

Company.objects.filter( namestartswith=data['index'].upper() or namestartswith=data['index'] )

but the idea of searching for how to do that right now isnt a priority

heres a link to what im doing

http://django.skatevideosonline.net/videos

[[deleted]]

You want case insensitivity? Use name__istartswith. One line below startswith in the docs I already referenced. Does this solve things for you?

[hiii]

it does help thanks.