Laravel Join tables using Where

Question

I would like to make a join like this. where do i go wrong ?

<?php
    for($i=1; $i<=$someValue; $i++){
              $allProjectsList = DB::table('user')
                         ->join('firstTable', 'user.id', '=','firstTable.id')
                         ->join('secondTable', 'user.pidm', '=','secondTable.id') 
                         //->where('id', '=', '$anotherValue')
                         ->first();
                         $anotherValue++;
    }
?>

---Update---

This one works nicely but,

<?php
        for($i=1; $i<=$someValue; $i++){
                  $allProjectsList = DB::table('user')
                             ->join('firstTable', 'user.id', '=','firstTable.id')
                             ->join('secondTable', 'user.pidm', '=','secondTable.id') 
                             ->where('user.id', '=', '123456')
                             ->first();
        }
?>

I would like to use a variable like $x instead of '123456', but it doesn't work. Any suggestions ?

You should be using relationships, which completely eliminates the need of constructing queries yourself. It's however impossible to give you an example out of context as you renamed your tables to firstTable and secondTable etc. — rdiz
– rdiz, Commented Feb 5, 2015 at 9:57
@Jishah i don't get an error, just blank screen and also i'm not good at writing tests. Example above works nice but when i add the where clause, i get that blank screen. — saimcan
– saimcan, Commented Feb 5, 2015 at 10:50

Anoua · Accepted Answer · 2015-02-05 16:01:28Z

1

Using a where statement with first does work actually. But it will only return a single user model where as a get will return an array of collection objects. This array can hold zero, one, or many user models.

I think one of the main issue you have is that you have your query (which returns one model) in a loop. Queries in loops should always be avoided and I honestly can't even tell why you have it in a loop, as the $i isn't even used and then you overwrite the $allProducts value every iteration through the loop.

Without more information and context I can't really help you further (other than what you seem to have figured for yourself), but I thought it'd important to have an answer with correct information about first and where's for others who come to this question for help.

Another note is that you seem to be using a user model as something other than a user model. And I would encourage you to look into relationships because you really shouldn't be returning multiple user modals with the one user id. http://laravel.com/docs/4.2/eloquent

answered Feb 5, 2015 at 16:01

Anoua

1212 silver badges3 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

saimcan Over a year ago

Yes i completely agree with you. But the db i am working on forces me to do this, at least for now. i need relations and use them with eloquent, but not this time.

Anoua Over a year ago

I don't use relationships for everything either just wanted to make sure you (and others who visit) are aware of them. I do understand being constrained by an already set database design.

saimcan · Accepted Answer · 2015-02-05 13:27:38Z

0

Using where statement with ->first() does not work. Gotta use where statement with ->get()
This one solved my problem :

<?php
   $allProjectsList = DB::table('user')
                             ->join('firstTable', 'user.id', '=','firstTable.id')
                             ->join('secondTable', 'user.pidm', '=','secondTable.id') 
                             ->where('user.id', '=', $aVariable)
                             ->get();

  foreach($allProjectsList as $rowValues){
     echo('$rowValue->someJoinedFieldValue');
  }
?>

Thank you for sharing your ideas.

answered Feb 5, 2015 at 13:27

saimcan

1,7827 gold badges33 silver badges67 bronze badges

Collectives™ on Stack Overflow

Laravel Join tables using Where

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related