Why Does My Raw Query Not Work Inside Laravel DB?

You're not actually executing the query.. tack this on the end:

->get();

Edit: So the full thing should look something like this...

    $nodes = DB::raw(
           'SELECT CONCAT(REPEAT(" "  , COUNT(parent.name) - 1), node.name) AS name
            FROM nested_categories AS node,
            nested_categories AS parent
            WHERE node.lft BETWEEN parent.lft AND parent.rgt
            GROUP BY node.name
            ORDER BY node.lft')->get();

    return view('categories/categories', compact("nodes"));

Or, you should be able to do this directly with the query builder syntax thusly:

$nodes = DB::table(DB::raw('nested_categories AS node, nested_categories AS parent'))
         ->whereBetween('node.lft', 'parent.lft', 'parent.rgt')
         ->select(DB::raw('CONCAT(REPEAT(" ", COUNT(parent.name) - 1), node.name) AS name'))
         ->groupBy('node.name')
         ->orderBy('node.lft')
         ->get();

@willvincent Thanks so much for helping out ! I'm really stuck.

I tried both of your suggestions, and got the following error messages:

First One:

 $nodes = DB::raw(
            'SELECT CONCAT(REPEAT(" "  , COUNT(parent.name) - 1), node.name) AS name
            FROM nested_categories AS node,
            nested_categories AS parent
            WHERE node.lft BETWEEN parent.lft AND parent.rgt
            GROUP BY node.name
            ORDER BY node.lft')->get();

error:

Call to undefined method Illuminate\Database\Query\Expression::get()

Second Example:

 $nodes = DB::table(DB::raw('nested_categories AS node, nested_categories AS parent'))
            ->whereBetween('node.lft', 'parent.lft', 'parent.rgt')
            ->select(DB::raw('CONCAT(REPEAT(" ", COUNT(parent.name) - 1), node.name) AS name'))
            ->groupBy('node.name')
            ->orderBy('node.lft')
            ->get();

Error Message:

Argument 2 passed to Illuminate\Database\Query\Builder::whereBetween() must be of the type array, string given,

The DB::raw() returns an Illuminate\Database\Query\Expression instance, while the get() is a method on the Illuminate\Database\Query\Builder class. So instead of appending the get() call after the raw(), try appending it after select():

$nodes = DB::table('nested_categories')->select(DB::raw(
           'SELECT CONCAT(REPEAT(" "  , COUNT(parent.name) - 1), node.name) AS name
            FROM nested_categories AS node,
            nested_categories AS parent
            WHERE node.lft BETWEEN parent.lft AND parent.rgt
            GROUP BY node.name
            ORDER BY node.lft')
)->get()

When it comes to the error message from the second code, the second argument of the whereBetween() method requires an array, so something like this should work (note how I wrapped the last 2 arguments in an array):

 $nodes = DB::table(DB::raw('nested_categories AS node, nested_categories AS parent'))
            ->whereBetween('node.lft', ['parent.lft', 'parent.rgt'])
            ->select(DB::raw('CONCAT(REPEAT(" ", COUNT(parent.name) - 1), node.name) AS name'))
            ->groupBy('node.name')
            ->orderBy('node.lft')
            ->get();

@Flugg Thank you VERY VERY much for coming back !

As to your first suggestion, I am getting the following error message:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT CONCAT(REPEAT(" " , COUNT(parent.name) - 1), node.name) AS name
' at line 1 (SQL: select SELECT CONCAT(REPEAT(" " , COUNT(parent.name) - 1), node.name) AS name
FROM nested_categories AS node,
nested_categories AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft from `nested_categories`)

For the second suggestion, it is error free !! But I just get an empty array out of it.

@Flugg It looks like the error is coming from the double "select" in the first line. What next ?

No problemo! :) The new error is just an SQL error, it seems like you have a double select there. The select() method will append a "select" in front of the statement, so you can remove it from the SQL string.

Also, as a general rule, you can keep on chaining methods like where(), whereBetween(), select(), groupBy(), etc. which all return an instance of Illuminate\Database\Query\Builder. This class basically builds your SQL queries, but nothing will actually ever be executed on the database before you use any of the "finishing moves" like first(), get(), paginate(), etc.

If you're interested in what SQL the 2nd version outputs, you can use the toSql() method:

 $nodes = DB::table(DB::raw('nested_categories AS node, nested_categories AS parent'))
            ->whereBetween('node.lft', ['parent.lft', 'parent.rgt'])
            ->select(DB::raw('CONCAT(REPEAT(" ", COUNT(parent.name) - 1), node.name) AS name'))
            ->groupBy('node.name')
            ->orderBy('node.lft')
            ->toSql();

Then you can see what SQL is outputted and try to locate the error from there.

@Flugg Ok, so I pulled the second SELECT out, and now it tells me I have a further error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from `nested_categories`' at line 6 (SQL: select CONCAT(REPEAT(" " , COUNT(parent.name) - 1), node.name) AS name
FROM nested_categories AS node,
nested_categories AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft from `nested_categories`)

In your second example where I got an empty array, I output to SQL as suggested and got below. It appears to be missing the 'parent.lft' and' parent.rgt' values.

"select CONCAT(REPEAT(" ", COUNT(parent.name) - 1), node.name) AS name from nested_categories AS node, nested_categories AS parent where `node`.`lft` between ? and ? group by `node`.`name` order by `node`.`lft` asc"

apologies I have worked in raw sql but Laravel Query Builder is new to me.

Thanks !

The missing parent.lft and parent.rgt are simply for security reasons, the question marks will be replaced by the correct values once PDO makes the call to the database. As of the first error, I'm not really sure, there seems to be an error in the SQL somewhere, but the last part of the query: ORDER BY node.lft from 'nested_categories' seems a bit wrong. Why would we run from 'nested_categories' again?

@Flugg Ok, well, focusing then on the first example, all I get is an empty array. I put my test table data into paste bin: http://pastebin.com/254DQBhb

Any ideas ?

$nodes = DB::table(DB::raw('nested_categories AS node, nested_categories AS parent'))
            ->whereBetween('node.lft', ['parent.lft', 'parent.rgt'])
            ->select(DB::raw('CONCAT(REPEAT(" ", COUNT(parent.name) - 1), node.name) AS name'))
            ->groupBy('node.name')
            ->orderBy('node.lft')
            ->get();

@Flugg Correction: http://pastebin.com/254DQBhb

@vincej Hey. How exactly are you joining the parent to the node? Your parent seems to be pointing no where?

@vincej Just played with it in artisan tinker.. it'll work if you use whereRaw():

$nodes = DB::table(DB::raw('nested_categories AS node, nested_categories AS parent'))
         ->whereRaw('node.lft between parent.lft and parent.rgt')
         ->select(DB::raw('CONCAT(REPEAT(" ", COUNT(parent.name) - 1), node.name) AS name'))
         ->groupBy('node.name')
         ->orderBy('node.lft')
         ->get();

@jimmck it's not a join, it's a tree traversal within a single table.

Although I suppose technically it'd be what.. an outer self join? Something like that.

@jimmck

I think it is called a self join. I freely admit that I found this article on nested hierarchies on the web. Take a look at the section on the "depth of the nodes" specifically the second section using "depth value to indent" . As I said the code works in MyAdmin.

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

Here is the Eloquent generated SQL. How exactly do I fill it in without node being selected. I and need to supply values to the two between parameters with what? Its recursive but you have to compare a node to p[art of the tree.

SELECT
    CONCAT(REPEAT(" ", COUNT(parent.name) - 1), node.name) AS name
FROM
    nested_categories AS node,
    nested_categories AS parent
WHERE
    `node`.`lft` BETWEEN ? AND ?
GROUP BY
    `node`.`name`
ORDER BY
    `node`.`lft` ASC

@vincej Yeah I know. Can you post your SQL, not what Eloquent thinks it is. I got your table built.

@willvincent

You are a rock star !! Yes, it works ! Here is my real problem. If you look at the examples from the article, the SQL gets in some examples pretty involved. In a perfect world, I would like to just pick up that code and bang it into Query builder as a raw statement. Unlike you I am not really skilled enough to convert it into syntax which QB is going to accept.

Any suggestions on how I might be able to just paste it into QB ??

Thanks !

@jimmck This works in MyAdmin and comes straight off the article.

@vincej I got it going. Your order by supplies the nodes to test against. @willvincent Eloquent is truly the 'Lisp' of SQL abstractions to me. If you are using db::raw why do you need whereRaw? I see no context in an Eloquent construction??? Of course I would need to actually construct one :)

Glad you are going VinceJ!

@jimmck How the heck am I going to convert some of the other SQL on this article? This was one of the smaller and easier ones ! Is there anyway that I can simply paste the raw sql into QB and be done?

@vincej Well I would let you into my Git Repo as your Example just became a test case. But for now I will have to read about DB Raw. I knew this day would come. Good article BTW. I have encountered the gentleman before. Since I have a working table and some SQL I will look at raw. How much code do you want to write? And its got to feed into Laravel views. I have seen tons of database tree stuff. Check packagist.

@jimmck I am pretty well going to use most of his examples, as I need to manage Major categories, sub categories and products within the categories, just like you would within a cms. Although I am not building a cms as such.

honestly, I don't know why the whereraw was necessary, but without it as you noted the 'between' conditions weren't getting set. So whereraw lets you just inject the actual sql for the where condition.

@vincej The trouble with your original query was.. well two fold, a select within a select, but the initial part of the querybuilder string was specifying the 'from' portion of the query.

So you were basically ending up with something like:

SELECT (SELECT CONCAT(REPEAT(" "  , COUNT(parent.name) - 1), node.name) AS name
            FROM nested_categories AS node,
            nested_categories AS parent
            WHERE node.lft BETWEEN parent.lft AND parent.rgt
            GROUP BY node.name
            ORDER BY node.lft) 
FROM nested_categories

which, obviously is not a valid query.. no syntax errors, but since the subquery returns multiple rows it won't work.

Just work through the queries.. it's really not too hard to convert things to query builder syntax.

However.. if you really feel that you can't wrap your head around it, copy/pasting queries into your code should be ok (as long as they're valid queries) like this:

$nodes = DB::select('SELECT CONCAT(REPEAT(" "  , COUNT(parent.name) - 1), node.name) AS name
                     FROM nested_categories AS node, nested_categories AS parent 
                     WHERE node.lft BETWEEN parent.lft AND parent.rgt
                     GROUP BY node.name ORDER BY node.lft');

@vincej Here is standalone script to test out Eloquent queries as @willvincent has suggested to you. The DB::table simply creates the FROM string. It is pretty much hard coded for single table queries. The whole builder is geared towards one style of query building .

Create a bin folder under laravel project tree and plop this script in and you can play. Did you ever get your debugger setup? ???? :) Anyway, good luck. Good hear from ya.

<?php

require dirname(__DIR__) . '/vendor/autoload.php';
require dirname(__DIR__) . '/bootstrap/app.php';

// use Illuminate\Database\Connection;
//use Illuminate\Support\Facades\DB as DB;
use Illuminate\Database\Capsule\Manager as DB;

$capsule = new DB;
$capsule->addConnection([
  'driver' => 'mysql',
  'host' => 'localhost',
  'database' => 'test',
  'username' => 'root',
  'password' => '',
  'charset' => 'utf8',
  'collation' => 'utf8_unicode_ci',
  'prefix' => '',]);
  
// Make this Capsule instance available globally via static methods
$capsule->setAsGlobal();
// Setup the Eloquent ORM
$capsule->bootEloquent();

//$table = DB::table(''); // table removes any extra tables from the string.

$result = DB::select('select CONCAT(REPEAT(" ", COUNT(parent.name) - 1), node.name) AS name
FROM nested_categories AS parent, nested_categories AS node 
WHERE
    node.lft BETWEEN parent.lft and parent.rgt
GROUP BY
    node.name
ORDER BY
    node.lft ASC
');

//$query = $query->toSql(); // ->get();
  
  $d = 1;
           
/*
            SELECT
    CONCAT(REPEAT(" ", COUNT(parent.name) - 1), node.name) AS name
FROM
    nested_categories AS node,
    nested_categories AS parent
WHERE
    node.lft BETWEEN parent.lft and parent.rgt
GROUP BY
    node.name
ORDER BY
    node.lft ASC
    
            */

BTW The result set array is totally flat i.e. 1 diemension.

@jimmck Thanks Jim for all your work and attention as always ! I'll give this some study on Monday. Nope, I tried to get my debugger working and gave up. It's the only thing about Storm which I think is a real pain. Nice to hear from you also. I haven't been on the forums for quite a while. Perhaps it means I'm getting better :o)

@jimmck @willvincent

Will, thanks again fro your great answer yesterday and Jim, thanks for the testing code. I installed it and it works great. I'm really sorry to hassle you guys again. I owe you one, big time.

However, Once again I have spent the whole day trying to get something which runs fine in PHPMyAdmin to work inside Laravel. It fails at the "select". I can't see how I can convert it to eloquent in this case. So the "whereRaw" trick doesn't work. Right now L5 is a pain :o)

 public function addNodes(){
    DB::statement('LOCK TABLE nested_categories WRITE;
    SELECT @myRight := rgt FROM nested_categories
    WHERE name = "MATERIALS";
    UPDATE nested_categories SET rgt = rgt + 2 WHERE rgt > @myRight;
    UPDATE nested_categories SET lft = lft + 2 WHERE lft > @myRight;
    INSERT INTO nested_categories(name, lft, rgt) VALUES("WINDOWS",@myRight+1,@myRight+2 );
    UNLOCK TABLES');
    }

The error message:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT @myRight := rgt FROM nested_categories
WHERE name = "MATERIALS";
' at line 2 (SQL: LOCK TABLE nested_categories WRITE;
SELECT @myRight := rgt FROM nested_categories
WHERE name = "MATERIALS";
UPDATE nested_categories SET rgt = rgt + 2 WHERE rgt > @myRight;
UPDATE nested_categories SET lft = lft + 2 WHERE lft > @myRight;
INSERT INTO nested_categories(name, lft, rgt) VALUES("WINDOWS",@myRight+1,@myRight+2 );
UNLOCK TABLES)

@vincej you do know laravel has getPdo(). You can write regular complex normal PDO queries.

@vincej Hey. What is your default database for your connection? Try use ; as your first line.

whereRaw just adds a raw where clause. In the example I showed you can inject a complete SQL statement or statements terminated by a semi-colon. ';'

You are going to have to navigate the mindless chatter of the 'Google-plex' sadly. I have your table built when I run your code with the use statement I get an error further on.

You need to set a default value for created_at in DB schema.

 21:14:49  [USE - 0 rows, 0.003 secs]  Command processed. No rows were affected
Code: 1046 SQL State: 3D000 --- No database selected
 21:14:49  [LOCK - 0 rows, 0.003 secs]  Command processed. No rows were affected
 21:14:49  [SELECT - 1 rows, 0.002 secs]  Result set fetched
 21:14:49  [UPDATE - 5 rows, 0.011 secs]  Command processed
 21:14:49  [UPDATE - 4 rows, 0.003 secs]  Command processed
 21:14:49  [INSERT - 0 rows, 0.002 secs]  [Code: 1364, SQL State: HY000]  Field 'created_at' doesn't have a default value
Code: 1364 SQL State: HY000 --- Field 'created_at' doesn't have a default value
Code: 1364 SQL State: HY000 --- Field 'updated_at' doesn't have a default value
 21:14:49  [UNLOCK - 0 rows, 0.001 secs]  Command processed. No rows were affected
Code: 1364 SQL State: HY000 --- Field 'created_at' doesn't have a default value
Code: 1364 SQL State: HY000 --- Field 'updated_at' doesn't have a default value
... 7 statement(s) executed, 10 rows affected, exec/fetch time: 0.025/0.000 sec  [6 successful, 3 SQL warnings, 1 errors]

use test;
LOCK TABLE nested_categories WRITE;
    SELECT @myRight := rgt FROM nested_categories
    WHERE name = "MATERIALS";
    UPDATE nested_categories SET rgt = rgt + 2 WHERE rgt > @myRight;
    UPDATE nested_categories SET lft = lft + 2 WHERE lft > @myRight;
    INSERT INTO nested_categories(name, lft, rgt) VALUES("WINDOWS",@myRight+1,@myRight+2 );
    UNLOCK TABLES

This is Not an optimal way of doing DB work. What mean is Eloquent translates down into it but you get more error control hopefully. But you can do this, but you have manage the database state. How are setting up the DB connection?

Note test is the default MySQL database where I created your table.

@vincej The problem is Eloquent is running a Query statement. You need a plain execute.