So I know SQL... but what's that ORM he assumed I'm familiar with?
Object-relational mapping.
Learn SQL, dammit
41–50 of 118 posts
Re: Learn SQL, dammit
#42A class of query I love that scares off a lot of developers is a correlated sub-query, where the subquery references a value from the outer query. For example, finding all employees with at least one assignment: SELECT * FROM employees e WHERE EXISTS (SELECT 1 FROM assignments a WHERE a.employee_id = e.id) For a while in Oracle this was a lot faster than IN/NOT IN. I'm not sure if that's still the case, or if it's tr…
Why would you ever want to use such a horrible statement instead of using an inner join?
scope :with_assignments, joins(:assignments)
(or :assignment, depending on how your association is defined)
Re: Learn SQL, dammit
#43Earlier quoted context omitted.
If you've already learned SQL and are comfortable using it directly, do you think there is any reason that it'd be wrong to continue doing so?
it's really not as much about the querying (though there is a lot of time-saving automation to be had there) as it is about integrating the data in your object model with the tuples being shuttled to/from the database. Like, at what point do you get sick of writing redundant "INSERT INTO " over and over again? Are there really people who still don't see the time-wasting, code-cluttering repetition in that?
I'm still amazed that there are developers who would rather spend more time maintaining/debugging/optimizing ORM code in a mature app that just learning a simple INSERT statement.
Re: Learn SQL, dammit
#44A class of query I love that scares off a lot of developers is a correlated sub-query, where the subquery references a value from the outer query. For example, finding all employees with at least one assignment: SELECT * FROM employees e WHERE EXISTS (SELECT 1 FROM assignments a WHERE a.employee_id = e.id) For a while in Oracle this was a lot faster than IN/NOT IN. I'm not sure if that's still the case, or if it's tr…
This is a textbook semijoin, but you can see what the postgres planner does with explain and explain analyze tacked in front of a query.
Re: Learn SQL, dammit
#45For a while I used to ask interview candidates to explain the difference between WHERE and HAVING, to see if they'd ever done anything beyond the basics. I'm still not sure if that's too hard, but people who could answer it did tend to do much better in the rest of the interview as well.
This prompted me to go look it up, since I didn't know. HAVING is WHERE for aggregate functions (SUM, etc). Funny thing is, I've used HAVING a lot in the past, but couldn't have explained the difference succinctly without cheating and looking it up.
[deleted]
Re: Learn SQL, dammit
#46Most of my experience is with the Django ORM. The point of the django ORM is not to replace SQL knowledge, even though that is feasible. The main point is reusability.
I had to work with SQL through PHP for a while and I found myself "composing" SQL queries in a myriad of ways. I tried to not repeat myself, but it felt like the Django ORM would have gone a lot further in cleaning up the query-building.
In conjunction with Django forms and Django Admin, maybe even the template language, the ORM makes query construction reusable.
One of the kickers is the ability to unify object construction from table columns. It's easy to convert a string or number to some Python field. It's more elaborate with Decimal, Json, or whatever you want to cook up.
Re: Learn SQL, dammit
#47[deleted]
Re: Learn SQL, dammit
#48A class of query I love that scares off a lot of developers is a correlated sub-query, where the subquery references a value from the outer query. For example, finding all employees with at least one assignment: SELECT * FROM employees e WHERE EXISTS (SELECT 1 FROM assignments a WHERE a.employee_id = e.id) For a while in Oracle this was a lot faster than IN/NOT IN. I'm not sure if that's still the case, or if it's tr…
You can use CROSS APPLY on tsql, and it allows you to use an order by in the inner query, for example, if you wanted to know the id of the employee's latest assignment.
SELECT e.*
, assignment_id = b.id
FROM employees e
CROSS APPLY (SELECT top 1 a.id
FROM assignments a
WHERE a.employee_id = e.id
ORDER BY a.assignment_date DESC
) b
In tsql, you are not allowed to have order by in a subquery (if memory serves me right)
Edit: Oh crap, I forgot this isn't SO, and the formatting went out the window.
Re: Learn SQL, dammit
#49Learn accounting, too!
Don't forget econ and statistics.
Re: Learn SQL, dammit
#50A class of query I love that scares off a lot of developers is a correlated sub-query, where the subquery references a value from the outer query. For example, finding all employees with at least one assignment: SELECT * FROM employees e WHERE EXISTS (SELECT 1 FROM assignments a WHERE a.employee_id = e.id) For a while in Oracle this was a lot faster than IN/NOT IN. I'm not sure if that's still the case, or if it's tr…
Why would you ever want to use such a horrible statement instead of using an inner join? scope :with_assignments, joins(:assignments) (or :assignment, depending on how your association is defined)
Well, adding joins to the outermost query can limit your options re how you use the scope. For instance, `joins` sets a read-only flag on the AR result, so you can't say Employee.with_assignments.destroy_all, for example, to, um, fire all your busy people. :-)