Category: sql

Working with combined expressions in WHERE clauses in Laravel 4

·

laravel-combined

When retrieving records from tables in our database, you often have to write combined expressions in WHERE clauses like

[sourcecode language=”sql”]
SELECT * FROM users WHERE status=1 AND (fname = ‘John’ OR lname=’Doe’)
[/sourcecode]

The query above is pulling out all users who’s status is set to 1, and fname is equal to ‘John’ or status is 1 and lname is equal to ‘Doe’. Now this query is significantly different from the following one.

[sourcecode language=”sql”]
SELECT * FROM users WHERE status=1 AND fname = ‘John’ OR lname=’Doe’
[/sourcecode]

Can you tell us what can go wrong with a statement like this? Well to find out more, let’s have a look at how this query will be parsed by database engine

[sourcecode language=”sql”]
SELECT * FROM users WHERE (status=1 AND fname = ‘John’)

OR

SELECT * FROM users WHERE lname=’Doe’
[/sourcecode]

So this query will pull every record that has a status set to 1 and fname equivalent to ‘John’. Then, with the previously pulled records, it will also pull all the users who has a lname equal to ‘Doe’, but this time it will pull out all users disregarding their status. Which means that users with status = 0 or 1 or 2 will also be in the resultset. I bet that is not what you were looking for while you’re writing this query and clearly understands the difference between these two queries. So you got the point of grouping or combining logical expressions and the impact that can have in your resultset. Writing a combined logical expression is easy in SQL. But what if you’re told to do the same operation using Laravel 4’s built in ORM which is called Eloquent ORM. It’s a little tricky to do the same operation in eloquent because of this combined expression, and it needs an anonymous function 🙂 Let’s have a look at the following code in our User model

[sourcecode language=”php”]
static function searchByName($fname, $lname){
return User::where("status", 1)
->where(function ($query) use ($fname, $lname) {
$query->where("fname", ‘=’, $fname)
->orwhere("lname", ‘=’, $lname);
})
->get([‘*’]);
}
[/sourcecode]

Now searchByName(…) function will work as we expected. You can try the following one and see the difference and notice how it is VERY MUCH different from our expectation

[sourcecode language=”php”]
static function searchByName($fname, $lname)
{
return User::where("status", 1)
->where("fname", ‘=’, $fname)
->orWhere("lname", ‘=’, $lname)
->get([‘*’]);
}
[/sourcecode]

Eloquent ORM’s where function can make use of callbacks in this way. Please note how we used anonymous function as callback that also uses $fname and $lname from the arguments. That’s mainly it. I hope that you’ve enjoyed this article 🙂

Get a list of Top 10 authors in a WordPress blog, and sort them by firstname and last name

·

Getting top 10 authors’s list from WordPress is actually pretty simple with a SELECT query. Here is the SQL for that which sorts the list by number of posts of each authors
[sourcecode language=”sql”]
SELECT wp_users.id,
display_name,
user_login,
user_nicename,
count(wp_posts.id) AS number_of_posts
FROM wp_users
JOIN wp_posts
ON wp_posts.post_author = wp_users.id
GROUP BY wp_posts.post_author
ORDER BY number_of_posts DESC
LIMIT 10
[/sourcecode]

And sorting this list by first name is pretty simple as well
[sourcecode language=”sql”]
SELECT *
FROM (SELECT wp_users.id,
display_name,
user_login,
user_nicename,
count(wp_posts.id) AS number_of_posts
FROM wp_users
JOIN wp_posts
ON wp_posts.post_author = wp_users.id
GROUP BY wp_posts.post_author
ORDER BY number_of_posts DESC
LIMIT 10) AS SortedByCount
ORDER BY display_name
[/sourcecode]

However, sorting that list by Lastname is a little tricky, we need to take extra care for display_name and extract the last name from it.
[sourcecode language=”sql”]
SELECT *
FROM (SELECT wp_users.id,
display_name,
user_login,
user_nicename,
count(wp_posts.id) AS number_of_posts,
substring_index(display_name, ‘ ‘, -1) AS lastname
FROM wp_users
JOIN wp_posts
ON wp_posts.post_author = wp_users.id
GROUP BY wp_posts.post_author
ORDER BY number_of_posts DESC
LIMIT 10) AS SortedByCount
ORDER BY lastname
[/sourcecode]

Now all you need to do is run it like this
[sourcecode language=”php”]
$top_authors = $wpdb->get_results($sql, ARRAY_A);
[/sourcecode]

Sweet, eh?