table join where clause

@dmcglone Both work, one doesn't do what you expect.

Your first piece does this:

left join on `pageInfo`.`id` = `pages`.`id` where `pageInfo`.`pages_id` = '1'

while 2nd :

left join on `pageInfo`.`id` = `pages`.`id` where `pageInfo`.`pages_id` = 'pages.id'

Notice that 2nd where looks for string pages.id not column value.

So depending on what you want to achieve, you can use:

            ->where('pageInfo.pages_id', '=', DB::raw('pages.id'))

or just use another on in the join closure.

Ah I had a hunch my 'pages_id' wasn't being assigned the value of it's contents. I tried your examples and with DB::raw laravel complains it can't find the class DB and adding another on produces a white blank screen, unless I am adding the extra on wrong. Here's the way I understand doing it is this correct?

$p = Pages::leftJoin('pageInfo', function($join){
                $join->on('pageInfo.id', '=', 'pages.id');
                $join->on('pages.id', '=', 'pageInfo.pages_id');
            })
            ->where('pageInfo.pages_id', '=', 'pages.id')
            ->get();
            dd($p);

I don't know why I said "blank white screen". I meant to say "nothing is shown in the dumped array. Brain seems to be malfunctioning.

@dmcglone You get no results because of this where I suppose. You don't have anything that matches pages_id = 'pages.id', right? So get rid of it and double on will do the job.

Next, you need to use \DB::raw(...) if you're in a namespace and I suppose that's the case. Alternatively you can always do this:

            ->whereRaw('pageInfo.pages_id  = pages.id')

and it will work without using DB facade.

Also, shouldn't it be or?

                $join->on('pageInfo.id', '=', 'pages.id')
                    ->orOn('pages.id', '=', 'pageInfo.pages_id');

With 2 on you call join pageInfo on pageInfo = pages.id AND pageInfo.pages_id = pages.is - I suppose you didn't want such condition.

the two ON's has the same intent as the originally intended JOIN/ON/WHERE. But with two ON's you want to lose the non-functional where.

    $p = Pages::leftJoin('pageInfo', function($join){
        $join->on('pageInfo.id', '=', 'pages.id')
             ->on('pages.id', '=', 'pageInfo.pages_id');
    })->get();

    dd($p);

@willvincent There is huge difference between having 2 on vs on + where when you use left join. It would be the same for inner join.

I have a hard time wrapping my head around query builders.. SQL makes more sense to me.

@JarekTkaczyk That's true.

I am also having a hard time wrapping my head around this too. My goal of this exercise was to see if I could grab a page id from the pages table and it's description from the pageInfo table and have only that info displayed on the correct page based on the URL. So the URL: http://buddy/test_project/public/1 should only show the entry in the db that has an id of 1 and so on and so forth. Here's the code. routes:

Route::get('/{id}', 'WelcomeController@Page');

Controller:

public function Page()
    {
        $p = Pages::leftJoin('pageInfo', function($join){
                $join->on('pageInfo.id', '=', 'pages.id')              
            })
            ->where('pages.id', '=', \DB::raw('pageInfo.pages_id'))
            ->get();
            dd($p);
    }

Then of course the Pages model

class Pages extends Model {
    protected $table = 'pages';
}

@JarekTkaczyk both solutions you listed worked, but listed all the rows in the db instead of only the row # from the URL.

The reason I started this exercise was because I needed to learn how to work with databases better and at the same time I needed to learn how to have the data in a database and grab that data according to which page the user is on, because these two tasks have been my biggest problem so far and I can't find any clear and concise explanation and examples anywhere.

@dmcglone Ooook, a few things to learn then:

// 1 -  you never passed the parameter from url to the method, so let's do it first
public function Page($id)
{
  // 2 - you want to fetch single Page, so don't use get() but find() / findOrFail()
  // 3 - use relations and eloquent eager loading to fetch related pageInfo
  $page = Page::with('pageInfo')->findOrFail($id);

  // 4 - return some view template with fetched $page
  return view('some.view')->with('page', $page);
}

// then in the view you can do something like this:
<h1> {{ $page->title }} </h1>
<p> {{ $page->pageInfo->description }} </p>
// and so on

You don't need no joins, just simple relation:

// Page model - better use singular names for models
// define the relation assuming each page has one related pageInfo row
public function pageInfo()
{
  return $this->hasOne('PageInfo', 'page_id'); // also create PageInfo model of course
}

That's just a quick introduction - read the docs, watch the laracasts and you'll be good.

I don't know what to say... That was an great explanation. Thank you very much. There's a couple things you mentioned that I believe I need to study up on. Like "eager loading". I just looked it up in the Doc's and it gives an example, but I was still wondering what you meant by using eager loading. It really didn't say much to me, so I googled it and it says.. Eager loading is the process whereby a query for one type of entity also loads related entities as part of the query......

So, that explains it.. :-/

@dmcglone eager loading in eloquent is used (in this very case not necessary in fact) to avoid so called n+1 query issue. Meaning, when you load 20 pages and then want to access pageInfo for each of them, you would run db query 20+1 times, unless you use eager loading - then only 2 queries will be executed.

Thanks. I think I understand that. I just went and watched the Jeff's N+1 video and that helped explain it also. I'm going to practice this for a couple days to get a really good grasp on it, then I'll move back to table joins.

I can't thank you enough for these explanations. They helped a lot! :-)

@dmcglone I'm glad I could help. You can tick the answer, I suppose it will be enough for thanks ;)