|

How To Select Raw SQL Using Eloquent

How To Select Raw SQL Using Eloquent

In this installment of our games app, we are going to add a sidebar for the main games and reviews views. In other words, when the site user is listing all games to view or all reviews to see, there will be a sidebar displayed. This sidebar will hold information about the games and reviews that have been submitted by users of the site. Specifically, we would like to show the user who has submitted the most games first, the user who has submitted the second most games second, and so on. It’s a way to feature so to speak, the active contributors to the site. How can we do this? We are going to do this by making use of raw sql queries combined with Eloquent. Let’s see how to do this now.


Figure Out Your Raw Query First

Ok here’s the deal Eloquent fans. When you need to perform queries that start to get a little more than just simply fetching all records in the database, you’re going to need to know what the actual query you’re trying to send to the database is, before you can use Eloquent to perform the action for you. If you don’t have a grasp of how to construct sql queries on your own, trying to create queries using Eloquent is only going to frustrate you. With that said, what are we trying to do with our query?


What do you want, in natural English

We want to get the users from the database who have submitted the most games, and place the most active submitter at the top of our results. Let’s figure this out in raw MySql first. For this, we will make use of DataGrip which is a fantastic database tool brought to you by JetBrains. JetBrains is also the maker of PHP Storm, a great piece of software.

First off, we need the user names. We can do this with a simple SELECT statement.
jetbrains data grip select query

Next, we need information about the games each user submitted.
Hmm, we might have a small problem. We are selecting data from the users table, and we now have the names of users. This is helpful, but in the users table, there is nothing about games or which games a user might have submitted. The games table however, does have information about the games that users have submitted. It looks like we are trying to get information from two tables at the same time. Are we stuck? No we are not. In a case like this, you want to make use of a JOIN to help you. By using a JOIN statement, you can get data from two tables at once. We will join the games table and the users table on the condition that the user_id field of games is equal to the id field of users.
data grip join statement

Ok what happened there? We now get the result of Mario, Mario, Mario, Toad, Luigi, and Toad. Not really what we need just yet. We need to somehow count the number of games each user submitted. For this, we are going to need to make use of the aggregate function count() and the GROUP BY clause. Here, we must specify a count of all games submitted and rename the temporary table as an alias of submitted_games. With that complete, we would need to group those results by the user’s names. Let’s see:
data grip count and group by

We’re getting close! Now we can see that Luigi has submitted 1 game, Mario has submitted 3 games, and Toad has submitted 2 games. We want to highlight the most active game submitters however, so we want Mario at the top and Luigi at the bottom. We can just add a handy ORDER BY clause to get this done. What will we order by? We shall order by the number of submitted games in descending order.
the final query in data grip


Our Final Raw SQL Query

This is exactly what we want, and taking this small incremental approach to building up database queries is helpful. We now know what we are trying to do, and how to do it with a raw query. Let’s get this into Laravel now.


Convert Your Raw Query into Eloquent

If we convert that raw query that we came up with in Laravel, it may look something like this:


Add the query to the index() method on Games Controller

When we visit the /games route, recall that this fires the index() method on the Games Controller. We’d like to be able to see this sidebar when viewing all games, so we are going to need to fetch the most active users from the database in this method. We can update the Games Controller like so:


app/Http/Controllers/GamesController.php


Update the games/index.blade.php view file

Our current view file for listing all games does so with one simple column. We need to change this up just a bit to allow for room on the side to display a sidebar which will list our new most active users section. Here is how we updated that file. Note that we are extracting the actual markup inside the sidebar to it’s own partial.


resources/views/games/index.blade.php


resources/views/partials/activeusers.blade.php

The markup for the partial view file.

Looking Good!
laravel sidebar powered by selectraw


Display Most Active Users on Reviews Page

It might also be cool to show the top submitters on the site when users visit /reviews as well. So if they are viewing all games, or all reviews, they see a sidebar with the top game submitters. Looks like we’ll need to update the Reviews Controller then in addition to the reviews/index.blade.php file. Let’s update those so we can get the goodness on both pages.


resources/views/reviews/index.blade.php

We now have the most active users displaying on both the /reviews and /games endpoints.
active user component on reviews page


Refactor Time

Those queries in our controller methods are not the best looking. We must fix this with vigor and tenacity at once. Let us extract this functionality elsewhere, where it can hide it’s complexity in the shadows.

First we will write out the code, as we would like to have it in the controller. It would be nice if we could simply fetch our active game submitters with something as simple as $activeusers = User::activeusers(); In fact, we will update the Games Controller as such right now.


app/Http/Controllers/GamesController.php


Update the User Model

We must now actually define the activeusers() method on our User model in order for it to work in our Games Controller. Here is how we can make that happen.


app/User.php


app/Http/Controllers/ReviewsController.php

We can now make our Reviews Controller pretty as well.


Like A Boss

sidebar working great


How To Select Raw SQL Using Eloquent Summary

In this tutorial, we had a look at how you can incorporate raw sql queries into your Eloquent code. This is sometimes needed when you want to apply more granular type queries that might involve alias tables or other very specific type queries. During this process, we saw that it makes sense to figure out what you are actually trying to get from the database in plain English. Then you can translate this to actual sql code to see if it is returning the data you are looking for. Once you are happy, you can convert this query into Eloquent and test it out in your application. In our case, we made use of the selectRaw() method to insert raw sql into our query where needed. With this approach, we came up with a custom query to find the most active submitters of games ordered by the top submitters so we could create a widget to display on our games website. Very cool!