Need Help Converting Complex MySQL Query

@vincej why not just use the db facade. And is it necessary to lock.

Probably a good idea to lock yeah, given this query could touch every record in the table.

If I'm not mistaken all you should have to do to populalte those values into the query is to use replacements:

public function createCategory($parent, $node) {
  DB::raw('LOCK TABLE categories WRITE');
  DB::select('SELECT @myRight := rgt FROM categories
    WHERE name = ?;
    UPDATE categories SET rgt = rgt + 2 WHERE rgt > @myRight;
    UPDATE categories SET lft = lft + 2 WHERE lft > @myRight;
    INSERT INTO categories(name, lft, rgt) VALUES(?, @myRight + 1, @myRight + 2)',
    [$parent, $node]);
  DB::raw('UNLOCK TABLES');
}

@jlrdw to be honest because I don't know how to convert this kind of query into a DB facade, hence my post. I did give it many tries though, and failed miserably.

@willvincent Hi Thanks for your help. Sorry to hassle you. I applied your fix exactly by copy and paste but I am still getting an error, albeit a different one:

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 'UPDATE categories SET rgt = rgt + 2 WHERE rgt > @myRight; ◀
    UPDATE categories ' at line 3 (SQL: SELECT @myRight := rgt FROM categories
    WHERE name = Cedar;
    UPDATE categories SET rgt = rgt + 2 WHERE rgt > @myRight;
    UPDATE categories SET lft = lft + 2 WHERE lft > @myRight;
    INSERT INTO categories(name, lft, rgt) VALUES(Red, @myRight + 1, @myRight + 2))

FYI - $parent equates to Cedar and $node equates to Red.

Any ideas ?

Many thanks !!

Looks like it's complaining about the @myRight

Might be because it's multiple queries running through a DB::select too. No really familiar with the syntax here, but suspect that probably ought to be a DB::raw rather than a DB::select.

as far as i know you can't do it that way because it's requires 2 queries. You should try to use whereColumn query i think will give you what you need. You can compare columns instead of values

@muzafferdede This has to be multiple queries though, it is updating existing records and inserting a new one.

Specifically what this code does is insert a new entry into a hierarchy/nested set in the DB as defined by the left/right values that define the traversal tree. Insertion of new items affects the left & right values of most of the other records in the table.

thanks @willvincent It's a mystery. It's a mystery. My sql works fine inside an sql console. Ok, I do not use replacements. I do this:

LOCK TABLES nested_categories WRITE;
SELECT @myRight := rgt FROM nested_categories
WHERE name = 'Root';
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('Materials', @myRight + 1, @myRight + 2);
UNLOCK TABLES;

When I use DB::raw() Laravel just skips right over it and ignores it.

So - I guess, I might try to convert this query into QueryBuilder using the passage in the docs called Parameter Grouping. I'm not very good with QB, hence I prefer raw SQL. Not sure how well this might work This is the only thing I'm not fond of with Laravel, the fact you can not just use raw SQL.

Cheers

@vincej if that works in sql console, have you tried to just write a stored procedure, just curious.

Good idea ... I'm not getting anywhere with "Parameter Grouping". I'll give it a try. Thanks.

yea for something like this a custom nested_insert stored procedure would be nice anyway ...

Can't believe it, I solved the problem. Yes, I tried the stored procedure route, and again, Laravel did not like the query. BUT, I found something on SO which outlines all the various DB::() functions, including DB::unprepared()

(https://stackoverflow.com/questions/25346162/difference-between-laravels-raw-sql-functions )

Yet, none of this is in the user docs. so - in the end, this works for me. I hope this might one day help someone else.

 public function addNode($category, $value)
    {
            DB::unprepared("
            LOCK TABLE nested_categories WRITE;
            SELECT @myLeft := lft FROM nested_categories
            WHERE name =  '$category';
            UPDATE nested_categories SET rgt = rgt + 2 WHERE rgt > @myLeft;
            UPDATE nested_categories SET lft = lft + 2 WHERE lft > @myLeft;
            INSERT INTO nested_categories(name, lft, rgt) VALUES( '$value', @myLeft + 1, @myLeft + 2); 
            UNLOCK TABLES;");
    }

@willvincent What is a nested_insert ... I see no mention of this on SO or the docs.

Everything is working perfectly. However, can you offer some advice on how I can protect this query from SLQ injection. @willvincent was using some substituions, but I don't seem to be able to make this work .. but then I might be doing things in correctly.

Many thanks !

public function addNode($request)
    {
            DB::unprepared("
            LOCK TABLE categories WRITE;
            SELECT @myLeft := lft FROM categories
            WHERE name =  '$request->parent_category';
            UPDATE categories SET rgt = rgt + 2 WHERE rgt > @myLeft;
            UPDATE categories SET lft = lft + 2 WHERE lft > @myLeft;
            INSERT INTO categories(parent_id, name, lft, rgt, depth) VALUES('$request->category_id', '$request->new_category', @myLeft + 1, @myLeft + 2,'$request->depth' +1); 
            UNLOCK TABLES;");

What is a nested_insert

It's not a thing. What I was saying is you could create a stored procedure and name it that

This is not one query it's multiple queries:

//Q1
LOCK TABLES nested_categories WRITE;

//Q2
SELECT @myRight := rgt FROM nested_categories
WHERE name = 'Root';

//Q3
UPDATE nested_categories SET rgt = rgt + 2 WHERE rgt > @myRight;

//Q4
UPDATE nested_categories SET lft = lft + 2 WHERE lft > @myRight;

//Q5
INSERT INTO nested_categories(name, lft, rgt) VALUES('Materials', @myRight + 1, @myRight + 2);

//Q6
UNLOCK TABLES;

The reason the work in the console is that they are handled as six seperate queries but what you tried to do is run it in Laravel which tries to parse it into one query thus failing.

I would use database transactions for this and use SELECT FOR UPDATE

https://dev.mysql.com/doc/refman/5.6/en/innodb-locking-reads.html