Avatar of Bruce Gust
Bruce Gust
Flag for United States of America asked on

How do I display and update a row in the context of a JOIN written using Laravel's Eloquent?

Here's my URL:  http://new.brucegust.com/admin/show/cuser/111116


Here's my Route: Route::get('/show/cuser/{id}', 'CUserController@show')->name('adminShowCUser');


Here's my Controller: 

    public function show($id)
    {
        //
      $pro = CeuUser::findOrFail($id);
      return view('admin/showCuser', compact('pro'));
     
    }


...and here's what the page looks like:



The challenge is this: I need to be able to display and edit the results of the quiz attached to the course content the user has ordered. 


On the "list" of orders, that information is displayed and when you look at the query that's running in the background, it looks like this:


$users = CeuUser::leftJoin('ceu_results', 'ceu_users.sid', '=', 'ceu_results.sid')->select('ceu_users.*','ceu_results.quiz_date','ceu_results.status','ceu_results.attempts','ceu_results.pass')->orderBy('name', 'asc')->get();
      return view('admin/listCusers', compact('users'));

Open in new window

I need to incorporate something similar when I display a single student, in that I've got to do a JOIN on the "ceu_results" table on the "sid" value.


Here's a screenshot of the ceu_results table:




How do I do a JOIN with a condition, the condition being the ID of the student in question?


And then once I've got the quiz results displayed, how do I update the "status" in the "ceu_results" table? 


This is an example of an UPDATE function on the current Controller...


 public function update(Request $request)
    {
        //
      $customer=CeuUser::findOrFail($request->the_id);
         //update ceu_users table
         $customer->winclose=$request->winclose;
         $success='User was successfully updated!';
         
         if($customer->save()) {
            Session::flash('message', 'Window closed successfully updated.');
            return redirect('/admin/show/cuser/'.$request->the_id);
         }  
    }


How do I tweak that philosophy / approach in a way where I can update two tables?


I imagine I'll need the sid value, but how do I pass that into the function?


Simple questions, but I'm a simple man! 

LaravelPHP

Avatar of undefined
Last Comment
Chris Stanyon

8/22/2022 - Mon
Chris Stanyon

Hey Bruce,

Before we try and get you sorted, couple of points that might need clarifying.

Firstly, I'm not entirey sure I follow your Data schema. You talk about Users (CeuUser), but you also talk about quizzes, results and orders - can't really see the relationship between all of these. The query you show just seems to JOIN the ceu_users and ceu_results table.

Secondly, is there a specific reason why you're manually calling the DB Builder for your joins instead of using Eloquent Relationships (HasMany / BelongsTo). Generally, Eloquent Relationships are much easier to code against and you don't have to worry about the joins etc. You just access the 'joins' as methods / properties on the model, for example.

$user->results()
$customer->orders()

This means that the 'join' query you have above becomes something like:

$users = CeuUser::with('results')->orderBy('name', 'asc')->get();

Open in new window

You can also use that same relationship to get the specific User and corresponding Results, so now your controller method becomes something like this:

public function show($id)
{
    $pro = CeuUser::with('results')->findOrFail($id);
    return view('admin/showCuser', compact('pro'));
}

Open in new window

That will now query the DB for the User and the related Results (automatically creating and running your JOIN). The Results will be accessible as a property on the model and you can access them like any other property in your blade template. The following assumes a one-to-many relationship:

@foreach ($pro->results as $result)
    <p>Status : {{ $result->status }} | Attempts : {{ $result->attempts }}</p>
@endforeach

Open in new window

Now for the Update, it really depends on how your planning to display the data and call the update, but based on the above, just remember that your User model has a property called Results - that is a collection of related Results objects. You update a Model by setting properties and calling save(). You could also use push(), but it depends on how you're sending data

public function update(Request $request)
{
    $customer = CeuUser::with('results')->findOrFail($request->the_id);
    $customer->winclose = $request->winclose;

    foreach ($customer->results as $result) {
        $result->someProperty = 'someNewValue';
    }

    $customer->push(); // save the customer and all related records

    return redirect('/admin/show/cuser/' . $customer->id);
}

Open in new window

Hope that all makes sense and gets you going in the right direction.
Bruce Gust

ASKER
Hey, Chris!

Thanks for your help! I'm going to pop the hood on what you're saying and see if I can't get it done. The query in question wasn't something I built, but even so, the "Eloquent" dynamic is still new enough where any feedback / input it going to be put to good use.

Stand by!

Thank you!
Bruce Gust

ASKER
Chris!

OK, first of all, for the sake of clarifying things, here's the "old school" SQL that will give me what I need. This is gold! I've run this and it shows exactly what I need:

select u.winopen, u.winclose, u.id, u.sid, u.orderno, r.status, r.attempts, r.pass, r.sid FROM ceu_users u JOIN ceu_results r ON u.sid=r.sid WHERE u.id=473

"u.id" is being passed in the URL...

http://new.brucegust.com/admin/show/cuser/473

The current method in the Controller is this:

    public function show($id)
    {
        //
      $pro = CeuUser::findOrFail($id);
      return view('admin/showCuser', compact('pro'));
      
    }

There's also a working query that's being used as part of the "list" functionality that has the fields / relationships I'm looking for sans the ID of a particular user.
 
$users = CeuUser::leftJoin('ceu_results', 'ceu_users.sid', '=', 'ceu_results.sid')->select('ceu_users.*','ceu_results.quiz_date','ceu_results.status','ceu_results.attempts','ceu_results.pass')->orderBy('name', 'asc')->get();

Here are the "models" that are involved...

CeuUser...

<?php

namespace App;

use Illuminate\Contracts\Auth\MustVerifyEmail;
use Illuminate\Foundation\Auth\User as Authenticatable;
use Illuminate\Notifications\Notifiable;
use Kyslik\ColumnSortable\Sortable;

class CeuUser extends Authenticatable
{
    use Notifiable, Sortable;

   protected $guard = 'ceu_user';

    /**
      * The attributes that are mass assignable.
      *
      * @var array
      */
    protected $fillable = [
        'orderno', 'email', 'occupation'
    ];
}

Open in new window

CeuResults...

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class CeuResult extends Model
{
   public $timestamps = false;
}


Open in new window

So, now the $20,000.00 question is how to create the query that gives me what I need.

While I'm on board with what you're saying as far as adjusting my Models, I'm all for the "long hand" approach, as far as just duplicating what my subcontractor did in terms of building a classic JOIN. The only thing that's missing is the syntax that qualifies the working query I mentioned earlier as far as limiting it to a specific ID.

What do you think?
Your help has saved me hundreds of hours of internet surfing.
fblack61
Chris Stanyon

Hey Bruce,

If you want to stick with the long-handed, query approach, you just need to add in your WHERE clause:

$pro = CeuUser::leftJoin('ceu_results', 'ceu_users.sid', '=', 'ceu_results.sid')
    ->select('ceu_users.*','ceu_results.quiz_date','ceu_results.status','ceu_results.attempts','ceu_results.pass')
    ->where('ceu_users.id', $id)
    ->orderBy('name', 'asc')
    ->get();

Open in new window

You are missing quite a bit of the Laravel power by doing it this way. For example, you're not retrieving instances of your Models this way - you're just retrieving a list of flat data records. Might make looping over the data a little trickier - will certainly make updating records more tricky - you won't have access to all the save(), push() methods etc.

If you get your relationships set up correctly (add a single method to each of your models), then the code above can become as simple as:

$user->load('results');

Open in new window

Much nicer, and you'll get a proper hierarchical grouping of your Model instances - but as they say ... more than one way to skin a cat ;)
Bruce Gust

ASKER
Chris, I appreciate you being willing to walk on the dark side for a little bit!

I was able to get everything to work thanks to the "flea-bag" version of the JOIN that I requested, so my page is now looking like this:





The Controller that's feeding this is your code:

 public function show($id)
    {
      //$pro = CeuUser::findOrFail($id);
      $pro = CeuUser::leftJoin('ceu_results', 'ceu_users.sid', '=', 'ceu_results.sid')
      ->select('ceu_users.*', 'ceu_results.quiz_date','ceu_results.status','ceu_results.attempts','ceu_results.pass')
      ->where('ceu_users.id', $id)
      ->orderBy('name', 'asc')
      ->get();
      return view('admin/showCuser', compact('pro'));
      
    } public function show($id)

Open in new window

  ...and the "showCuser.blade.php is 

<form action="{{ route('adminUpdateCUser') }}" method="POST">
                  <div class="form-group">
                     <input type="hidden" value="{{csrf_token()}}" name="_token">
                     <div class="row">
                        <div class="col-md-12 col-xs-12">
                           <label for="name">Name: {{ $pro[0]->name}}</label>
                        </div>
                     </div>
                     <div class="row">
                        <div class="col-md-12 col-xs-12">
                           <label for="name">Order: {{ $pro[0]->orderno}}</label>
                        </div>
                     </div>
                     <div class="row">
                        <div class="col-md-6 col-xs-12">
                           <label for="status">Quiz (Pass (1) or Fail (0)):</label>
                           <select name="pass" class="form-control">
                              <option selected> {{ $pro[0]->pass}}</option>
                              <option>0</option>
                              <option>1</option>
                           </select>
                        </div>
                     </div>
                     <div class="row">
                        <div class="col-md-6 col-xs-12">
                           <label for="name">Item Number:</label>
                           <input type="text" class="form-control" name="itemnumber" value="{{ $pro[0]->itemnumber}}">
                        </div>
                     </div>
                     <div class="row">
                        <div class="col-md-12 col-xs-12">
                           <label for="itemname">Window Close: </label><br>
                           <input type="text" class="form-control" name="winclose" id="datepicker" value="{{ $pro[0]->winclose }}">
                        </div>
                     </div>
                     <div class="row">
                        <div class="col-xs-12" style="text-align:center;">
                        <input type="hidden" name="user_id" value="{{Auth::user()->id}}">
                        <input type="hidden" name="the_id" value="{{ $pro[0]->id }}">
                        <input type="image" name="submit" src="{{ asset('assets/img/nomas_submit.jpg') }}" style="width:150px;">
                        </div>
                     </div></form>
                  </div>
               </form>

Open in new window

What I need to do now is take the current "update" method...

 public function update(Request $request)
    {
        //
      $customer=CeuUser::findOrFail($request->the_id);
         //update ceu_users table
         $customer->winclose=$request->winclose;
         $success='User was successfully updated!';
         
         if($customer->save()) {
            Session::flash('message', 'Window closed successfully updated.');
            return redirect('/admin/show/cuser/'.$request->the_id);
         }   
    }

...and tweak it so it can impact the two tables that I now want to update: ceu_user and ceu_results.

I was thinking that I could do what seemed to be happening above, as far as executing a SELECT (findorfail) and then updating the row that was returned.

I tried something like that with:

$customer= CeuUser::leftJoin('ceu_results', 'ceu_users.sid', '=', 'ceu_results.sid')
      ->select('ceu_users.*', 'ceu_results.quiz_date','ceu_results.status','ceu_results.attempts','ceu_results.pass')
      ->where('ceu_users.id', $request->the_id)
      ->orderBy('name', 'asc')
      ->find();
      $customer->winclose=$request->winclose;
      $success='User was successfully updated!';
      
      if($customer->save()) {
         Session::flash('message', 'Window closed successfully updated.');
         return redirect('/admin/show/cuser/'.$request->the_id);
      }  

...and that didn't work. What am I missing? How can I update the result of JOINed dynamic?
Chris Stanyon

Hey Bruce,

We're fighting convention a little here, and the problem that's coming from that is that you're mixing 2 different approaches to data - The Eloquent ORM and the Query Builder.

When you use Eloquent, you manage your DB records as instances of a PHP Class called a Model. The benefit of doing this is that it gives you an Active Record approach to Data Management, allowing you to easily access the columns of your DB as properties and the relationships as methods. It also gives you access to additional methods, such as save(), delete() etc.:

$customer = findOrFail(123);
$customer->name = "Chris";
$customer->save();

Open in new window

In the code above, we're using Eloquent, so $customer is an instance of the Model class and therefore has all the added methods and properties that come with it. This is one of the most powerful features of Laravel.

Now, in your case, you're choosing to use the query builder from a model, so when you call leftJoin on that, you're not getting a hierarchy of a CeuUser with several CeuResults - you're just getting several flat records of data - each record containing the fields from both tables. It does make it a little messier to output the data to your user - for example, in your Blade template, you don't seem to be ever looping over this data - you're just outputting the first record of what might be several - you're echoing out the data for $pro[0], but what about $pro[1], $pro[2] .... $pro[25] ?? If you really fo only have a 1-to-1 relationship, then ignore that last bit !

When it comes to Updates, like I said above, you're not using Eloquent, so everything has to be done manually. In order to run a manual update to a CeuResult, you're going to need the Primary Key for it (ceu_user.id). You'll need to include that in your SELECT statement. You'll also need to output that Result ID in your blade template, so that you can then pass it back to the server when it comes time to update. You'll need to use it in your Update Query, something like this:

DB::table('ceu_results')
    ->where('id', $someId)
    ->update(['pass' => $pass]);

Open in new window

It's probably gonne be slightly more complicated than that.

Basically, with joins, you don't update 2 tables at once - you update the tables independently. When your data is passed back from the browser, you're gonna have to run one Update query for the CeuUser data, and then separate queries for each of the CeuResults you want to offer. Building your HTML page in a way that makes that easier would be a good start (think HTML field arrays).
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Bruce Gust

ASKER
Chris, I appreciate your patience!

I'm almost there!

I've been able to validate the basic accuracy of this:

  public function update(Request $request)
    {
      $test_results= CeuResult::findOrFail($request->the_quiz_id);
      if($request->pass <> $test_results->pass) {
         $test_results->attempts=0;
         $test_results->pass=$request->pass;
         $test_results->quiz_date=NULL;
         $test_results->status=NULL;
         echo "done";
         exit();
      }
      
      $customer=CeuUser::findOrFail($request->the_id);
         //update ceu_users table
         $customer->winclose=$request->winclose;
         $success='User was successfully updated!';
         
         if($customer->save()) {
            Session::flash('message', 'Window closed successfully updated.');
            return redirect('/admin/show/cuser/'.$request->the_id);
         }   
    }

I have "the_quiz_id" and I'm getting an accurate result from the IF statement, but...

...although I'm getting no errors from the update section and I'll get a "done," nothing's being updated.

What am I missing?
ASKER CERTIFIED SOLUTION
Chris Stanyon

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question