vincej
2 months ago
878
16
Laravel

Need Help Converting Complex MySQL Query

Posted 2 months ago by vincej

I tend to write my complex queries in raw SQL and then try to fit them into Laravel. I'm not succeeding getting the two arguments, $parent and $node into the DB::select. The query works perfectly well inside an SQL console. In a previous post from me I was advised to simply wrap my raw query in DB::select(). additionally I found further advice on Laracasts on how to lock tables, and also here: ( https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html )

Any ideas ??

 public function createCategory($parent, $node) {
     DB::raw('LOCK TABLE categories WRITE');
     DB::select(
       'SELECT @myRight := rgt FROM categories
        WHERE name = $parent;                                     // $parent value not available 
        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($node, @myRight + 1, @myRight + 2)');   // $node value not available
        DB::raw('UNLOCK TABLES');
         }

Please sign in or create an account to participate in this conversation.