appyapp's avatar

Custom database queries not linked to any model - where to put

I am working on an existing site developed using plain PHP and have started developing a section of the site using Laravel. I am new to Larvel and it's concepts and have some questions.

I only have App/Users.php class in my app at the moment but there are many other tables in the database.

I need to query some data which is not linked to users table. I know that I can run a query, say, in a controller as below -

//App/Http/Controllers/HomeController.php
public function index() 
{
     $data = DB:select("My SQL Goes In Here");
}

However, If I need to query same data again - then I'll have to duplicate it.

I was thinking if I can put it in a function inside App/User.php

public function someStats($category_id)
{
     $sql = "SELECT st.*, u.name FROM stats 
    INNER JOIN categories ct ON ct.id = st.category_id AND ct.approved = 1
    INNER JOIN comments cmt ON cmt.id = st.comment_id AND cmt.approved=1
    INNER JOIN users u ON u.id = cmt.user_id
    WHERE st.organisation_id=1456
    ORDER BY st.date_updated
      ";
    return DB:select($sql);
}

I know that there are lots handy functions available in Eloquent. E.g.

$flights = App\Flight::where('active', 1)
               ->orderBy('name', 'desc')
               ->take(10)
               ->get();

So please don't suggest me to go for that option as I will come back and update my code later on. I just need some quick and dirty way to show some information. It's a bit learning curve for me with Laravel but I am sure with some experience I'll use all the best practices recommended.

My questions are -

  1. How do I call above method inside a controller? Do I need to declare the method as static etc?
  2. DB:select() returns an array of stdClass objects. if I wanted to make use of collection helpers - do I just need to do collect($data) ?? or is there any other way I can make DB:select() data useable like the $flights above? Please advise.
0 likes
9 replies
martinbean's avatar
martinbean
7 years ago
Best Answer
Level 80

@appyapp The time you spend writing raw SQL queries and littering your controllers with them, could be time spent just creating models for your database’s tables and then getting the power of Eloquent…

If you really want to encapsulate some SQL queries then you can do just that, by wrapping them in a class:

class SomeStats
{
    public static function run($categoryId)
    {
        // Build SQL

        return DB::select($sql);
    }
}

class SomeController extends Controller
{
    public function someAction()
    {
        $stats = SomeStats::run($categoryId);
    }
}

But again, if you’re creating classes for queries to be re-used, then you may as well just create an Eloquent model class and get the power of the query builder.

appyapp's avatar

Thanks for your ideas @martinbean - I'll certainly give that a go. Baby steps for me my friend :)

1 like
jlrdw's avatar

WHERE st.organisation_id=1456

If you are going to use some normal queries at times, have proper bindings:

Here is an example:

https://laracasts.com/discuss/channels/laravel/sql-native-to-query-builder

But most queries, not all can still be done in either eloquent or query builder, where bindings are handled for you. As example:

$quy = Powner::query()->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')
                ->select('dc_powners.ownerid', 'dc_powners.oname')->distinct()
                ->selectRaw('max(dc_pets.petid) as maxPetId')
                ->where('dc_powners.ownerid', '<', 3)
                ->groupby('dc_powners.ownerid')
                ->orderby('dc_powners.oname')
                ->get();

Again just an example

But for some things try to use eloquent relations. I use regular sql myself for complex joins with a group by if it's a report.

appyapp's avatar

@martinbean - do you have any advice on question 2 in my original post above? re using data. I would like to turn all data into array rather than one array of objects.

appyapp's avatar

@JLRDW - Thanks for pointing that out but yes I know how to use bindings when passing arguments. Above was just an example.

jlrdw's avatar

I would like to turn all data into array rather than one array of objects.

How about a large result set. That would defeat pagination. If a db result returned 100,000 rows, surely you don't want an array or collection.

Or are you referring to some smaller result sets, to do some special handling.

There is also getPdo(), where you can return assoc results.

https://laracasts.com/discuss/channels/guides/getpdo-usage

To note getPdo() is just using the PDO instance, and is straight PDO, where you can properly bind parameters.

Also what some call a raw query here is not correct terminology, raw is no bindings, proper bindings is not a raw query.

Eloquent converts to normal sql at run time with bindings.

appyapp's avatar

@jlrdw I've taken a look at your getPdo and paginater posts. I'm sure I'll be able to learn some useful info from your posts. Thank you.

magmatic's avatar

Using a query builder is like holding a prosthetic arm that is holding a tool. The best thing you can do is throw the prosthetic arm away and use the tool yourself. Just use SQL. It's good for you. It's your friend.

Any query builder is like a mean person who is trying to keep you from your friends. SQL makes a wonderful friend. He likes you, and you like him. We're best friends.

Why bother learning any query builder, when you can go straight to SQL? Why bother eating the salad when you can go straight to the steak? SQL is the steak. It tastes good. It satisfies you. It's good for you. It's wonderful stuff!

If SQL were a famous person, a query builder would be his body guard, keeping you from him. It's a good day when the body guard goes away and you get full access to the famous person.

This extra layer of abstraction between you and your SQL is like a layer of bubble wrap around your eyes. It might make you feel better, but you'd be better off if you could actually see what you're doing.

Query builder code is a zit in otherwise beautiful code. Programming code is beautiful. SQL is beautiful. Let's keep our code beautiful.

Query builders are like a veil over a beautiful woman's face. The sooner you get rid of it the better.

It's take to shake off the query builders we have been using, like removing the training wheels from our bicycles. It's time to grow up and take control over our lives and our data, without an unnecessary layer of abstraction getting in our way. We're not noobs anymore, we're programmers! We're not afraid of languages, we enjoy them! And we will enjoy Structured Query Language too!

Long live SQL!

2 likes
martinbean's avatar

Spoken like someone who doesn’t understand a query builder. By your logic, you may as well write your web applications in Assembly instead of using some “ugly” high level language like PHP. And you definitely shouldn’t be using a framework on top of PHP like Laravel. Remove that veil!

I’d dread to see your codebase if it’s just got SQL queries strewn throughout it…

Given this tirade has been your only post on Laracasts, I’m going to take a stab in the dark and assume you’re a troll.

2 likes

Please or to participate in this conversation.