laravel 5 query builder restrict

well i am stuck in this scenario there is 3 tables candidate table,position table and recruitment form table. Candidate-id and position-id is a foreign key in recruitment form table when candidate apply for a position which he already applied he can't apply for that post again i tried something like this but not working . If there some error please solve it or recommend some other method to achieve this thank you.

$check = DB::table('recruitmentform')->select('positionid')->where('candidateid',$request->cid)->get();

    for ($i = 0; $i < count($check); $i++) {
        if ($check[$i] == $request->position_id) {
            return redirect('/');
        }
    }

dmitov

9 years ago

Best Answer

Level 9

You aim for something like this:

$check = DB::table('recruitmentform')->where('candidateid', $request->cid)
                                     ->where('positionid',  $request->position_id)
                                     ->exists();

if ($check) {
    return redirect('/');
}

But I really suggest you to switch to models, and make use of the Laravel's relationship methods.

sairum

9 years ago

Level 1

can u guide me a bit building relationship according to this scenario.

dmitov

9 years ago

Level 9

Hey,

Sure thing! So whenever you're building something, try to express it in your mind like you'd feel it.

So an user can apply to positions. That means the User model has relationship to the Position.

But what relationship? An user has many positions? Or belongs to many positions? Has many would mean that on the positions table you have user_id field, which specifies that one position belongs to an user.

So:

* HasMany - identifier field is on the other table

* BelongsTo - identifier field is on the current table

But here you would need something more sophisticated - a pivot table (which you already have named recruitmentform) and a relation called BelongsToMany.

Now you can go in your User model and create the relation like that:

public function positions()
{
    return $this->belongsToMany(Position::class, 'recruitmentform', 'candidateid', 'position_id');
}

For consistency reasons, you might want to rename the candidateid field to user_id, and forget about the last 2 arguments on the belongsToMany function (just remove them, Laravel is smart enough to figure out how the fields are named). But... Eh, Python creators said "Fuck consistency" so, I guess you can do whatever you want (and that is how it should be, but that's another topic).

So moving on.

You would like to also setup an unique key on your recritmentform table which consists of both candidateid and position_id fields, so you won't end up with a candidate application for one position twice. You can later catch the exception thrown and decide what to do with it, or make a simple check before inserting, which would look like this:

if ($user->positions()->wherePositionId($request->position_id)->exists()) {
    redirect('/'); // And do something else
}

This might not be the best approach, you might want to encapsulate this behind another function if you will use it a lot, but that's the basics.

Furthermore, you will have the ability to let's say do:

$user->positions; // View all user positions
$user->positions()->whereStatus('pending')->all(); // View all positions the user has applied to and are still pending

And many more stuff available through the relationships.

sairum

9 years ago

Level 1

thank you so much for your help demitov much appreciated . I try what u said but i get an error

Call to a member function positions() on string

dmitov

9 years ago

Level 9

Well, $user has to be an User instance.

This gets the authenticated user:

$user = Auth::user();

Furthermore you should dig deeper in Laravel's structure.

Please or to participate in this conversation.