Using a variable in join function

replied 10 years ago

Date and idOrg are outside of the scope of your callback. You must use the use statement to pass them.

$persons = Person::where('t_persons.idOrganisation','=',$idOrg)
  ->join('t_personrelations', function($join) use($idOrg, $date){
    $join->on('t_personrealtions.idPerson','=','t_persons.id')
      ->on('t_personrelations.idOrganisationLicense','=',$idOrg)
      ->on('t_personrelations.datStart','<=',$date)
      ->on('t_personrelations.datEnd','>',$date);
  })->get();

Last updated 2 years ago.

0

diwaffm

replied 10 years ago

pmall said:

Date and idOrg are outside of the scope of your callback. You must use the use statement to pass them.

OK. I saw, that they are out of scope, but I didn't know, how to put in...

But now, I get the message "Unknown column '16321'" (where 16321 is the content of $idOrg).

If I do change the "->on" into "->where", as descripted on http://laravel.com/docs/queries#joins,

        ->join('contacts', function($join)
        {
            $join->on('users.id', '=', 'contacts.user_id')
                 ->where('contacts.user_id', '>', 5);
        })
        ->get();

I get the message "Call to undefined method Illuminate\Database\Query\JoinClause::where()"

Thanks

dirk

Last updated 2 years ago.

0

jarektkaczyk

replied 10 years ago

Solution

The thing is, you want to use where not on.

Difference between the two is, that you use on() to compare 2 fields, while where() to compare a field against provided value to escape (as PDO binding, as usually. Both are JoinClause methods in context of join closure):

->join('t_personrelations', function($join) use ($idOrg, $date) {
    // this shouldn't be bound since it's field name, so use on()
    $join->on('t_personrealtions.idPerson', '=', 't_persons.id') 

      // while these should be, thus where()
      ->where('t_personrelations.idOrganisationLicense', '=', $idOrg)
      ->where('t_personrelations.datStart', '<=', $date)
      ->where('t_personrelations.datEnd', '>', $date);
  })

Last updated 2 years ago.

0

diwaffm

replied 10 years ago

thanx - it seems, I mixed up something...

But the example confuses me...

Ciao

dirk

Last updated 2 years ago.

0

Moderators