Multiple Inner joins in the same table

If you substitute ->get() with ->toSql(), you should be able to see the SQL that's generated. It probably won't be quite as neat a your raw attempt, but if you could please post it, I would like to see what's going on.

You might also be missing the operator for your where clauses:

->where('l.type', '=', 'menuLevel')
->where('l.value', '=', 0)
->where('t.type', '=', 'menuTitre')
->where('p.type', '=', 'menuParent')
->where('u.type', '=', 'menuUrl')

However, knowing Taylor, Eloquent is smart enough to figure that out, so perhaps that's not necessary.

@thefuzzy0ne Hi! Thank for you reply. I tried with the ->toSql method and pasted it directly in SequelPro and replaced the values but always the same syntax error. Here's the query:

"select `l`.`origin, t`.`value, l`.`value, p`.`value, u`.`value` from `datas l` inner join `datas t` on `l`.`origin` = `t`.`origin` inner join `datas p` on `p`.`origin` = `l`.`origin` inner join `datas u` on `u`.`origin` = `l`.`origin` where `l`.`type` = ? and `l`.`value` = ? and `t`.`type` = ? and `p`.`type` = ? and `u`.`type` = ?"

I also tried with the missing operator in my where clause.

I've neatened it up a little, and I'm totally gob-smacked at how similar it looks to your raw query!

SELECT `l`.`origin, t`.`value, l`.`value, p`.`value, u`.`value` 
FROM `datas l` 
INNER JOIN `datas t` on `l`.`origin` = `t`.`origin` 
INNER JOIN `datas p` on `p`.`origin` = `l`.`origin` 
INNER JOIN `datas u` on `u`.`origin` = `l`.`origin` 
WHERE `l`.`type` = ? 
AND `l`.`value` = ? 
AND `t`.`type` = ? 
AND `p`.`type` = ? 
AND `u`.`type` = ?

Sadly, I've no idea what the problem is. May I suggest you put you 0 in quotes? Maybe this is being misinterpreted as false or null or something

Aha! I think I've spotted the problem. I think it's to do with how your aliases are escaped in back-ticks. Maybe try using "as" to assign your aliases?

DB::table('datas as l')->select('l.origin, t.value, l.value, p.value, u.value')
->join('datas as t', 'l.origin', '=', 't.origin')
->join('datas as p', 'p.origin', '=', 'l.origin')
->join('datas as u', 'u.origin', '=', 'l.origin')
->where('l.type', 'menuLevel')
->where('l.value', 0)
->where('t.type', 'menuTitre')
->where('p.type', 'menuParent')
->where('u.type', 'menuUrl')->get();

@thefuzzy0ne I found a solution! I used the aliases but not at the same place as you suggest. Here's a working code:

return DB::table(DB::raw('datas l'))
    ->select(DB::raw('l.origin AS origin, t.value AS titre, l.value AS level, p.value AS parent, u.value AS url'))
    ->where(DB::raw('l.type'), DB::raw('\'menuLevel\''))
    ->where(DB::raw('l.value'), DB::raw(0))
    ->where(DB::raw('l.site_id'), DB::raw($this->id))
    
    ->join(DB::raw('datas t'), DB::raw('l.origin'), '=', DB::raw('t.origin'))
    ->join(DB::raw('datas p'), DB::raw('p.origin'), '=', DB::raw('l.origin'))
    ->join(DB::raw('datas u'), DB::raw('u.origin'), '=', DB::raw('l.origin'))
    
    ->where(DB::raw('t.type'), DB::raw('\'menuTitre\''))
    ->where(DB::raw('p.type'), DB::raw('\'menuParent\''))
    ->where(DB::raw('u.type'), DB::raw('\'menuUrl\''))
->get();

It's probably not the nicest way to perform the query, but for now this is working. I just hope it doesn't need to much memory. Thanks for you help!

@beeInteractive Did my solution not work? It seems to work at my end, or rather, the query looks the way I'd expect it to.

The only place you should need to use raw() is where you use table aliases. The rest should work great. With the method you're using, there's not much benefit to using Eloquent at all. It would make more sense just to execute the entire query raw().

@thefuzzy0ne I always get this response if I don't use the raw function:

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
'.`value, p`.`value, u`.`value` from `datas` as `l` inner join `datas` as `t` on ' at line 1 (SQL: select `l`.`origin, t`.`value, l`.`value, p`.`value, u`.`value` from `datas` as `l` inner join `datas` as `t` on `l`.`origin` = `t`.`origin` inner join `datas` as `p` on `p`.`origin` = `l`.`origin` inner join `datas` as `u` on `u`.`origin` = `l`.`origin` where `l`.`type` = menuLevel and `l`.`value` = 0 and `t`.`type` = menuTitre and `p`.`type` = menuParent and `u`.`type` = menuUrl)

Some of the value doesn't seem to be escaped and that cause the error. That's why I used the raw functions..

That's due to your SELECT statement, which needs to be like this, I believe:

DB::table('datas as l')->select('l.origin', 't.value', 'l.value', 'p.value', 'u.value')

All those DB::raw()'s seem unnecessary to me.

This should work:

return DB::table('datas',  'l')
    ->join('datas AS t', 'l.origin', '=', 't.origin')
    ->join('datas AS p', 'p.origin', '=', 'l.origin')
    ->join('datas AS u', 'u.origin', '=', 'l.origin')
    ->where('l.type', '=', 'menuLevel')
    ->where('l.value', '=', 0)
    ->where('l.site_id', '=', $this->id)
    ->where('t.type', '=', 'menuTitre') // <-- should that be menuTitle?
    ->where('p.type', '=', 'menuParent')
    ->where('u.type', '=', 'menuUrl')
    ->select('l.origin AS origin', 't.value AS titre', 
             'l.value AS level', 'p.value AS parent', 'u.value AS url')
->get();

Hi @willvincent ! Yes, "menuTitre" is right, some of the code is in french :-)

It doesn't work with my code, it says that column l.origin is not found:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'l.origin' in 'field list'

ok, so you might need db::raw() for the selects, but that should be the only place it's needed.

@jlrdw Ha! :)

It is too late for reply, however I use this method for joining to one table multiple time on different conditions :

 $qr = DB::table("MainTable")
                ->leftJoin("SysCode as t1", function($join){
                    $join->on("t1.Code", "=", "MainTable.Code1")
                        ->whereRaw("t1.Type=1");
                })
                ->leftJoin("SysCode as t2", function($join){
                    $join->on("t2.Code", "=", "MainTable.Code2")
                        ->whereRaw("t1.Type=2");
                })->Select(.....)