Problem with inserting from input in database

I have many to many relationship between UserProfile model and UserTv model. Here are the tables.

user_profiles

id    user_id    username
 1       1       AuthUser

id    name
 1    Action
 2    Drama
 3    Comedy
 4    manually added some genre from input from authenticated user

user_tv

id    user_id    tv_id
 1      1          2
 1      1          4

For example, these first three ids in tv table (Action, Drama, Comedy) are inserted through seeders and this fourth id is inserted manually through input text from form by that user who is authenticated. And there lies the my problem. I want that those values that are manually added through input in form to only be able to see that user that inserted those values, and all other users can't. But also I want all users to remain to see those first three values that are generated through seeder. Currently everything works so that all users can see everything. Any help is appreciated. Here is my code.

bobbybouwmann

6 years ago

Level 88

You need some way to determine if the tv was added manually or through your seeder. Normally you can just add a boolean or the user_id to the tv table and use that as reference. This should be a nullable field. If it's null it comes from the seeder, if it's filled it's created by a user as user input.

So it depends on how you want to build this, but just a boolean to determine this is fine in most cases.

dr24

6 years ago

Level 2

@bobbybouwmann

Can you give me short example how to achieve this? So add a column in tv table, in my migration I put name to be nullable, in seeder I put it to be default(0), and how would I write logic from input to be 1?

bobbybouwmann

6 years ago

Level 88

Well you need to replace this part

        $data = $request->get('tvsOptions', '[]');
        $userProfile->tvs()->sync($data);

$data = $request->get('tvsOptions', '[]');

$tv = Tv::create(['name' => 'Custom TV', 'custom' => 1]);
$data[] = $tv->id;

$userProfile->tvs()->sync($data);

This is the basic idea. In this case, the custom column is the one you can use for specifying this is created by someone else.

1 like

dr24

6 years ago

Level 2

@bobbybouwmann

I did this and it updates column custom to 1 in my table when I enter from input, but when I log in with other user it still shows that input that other user entered. And also when I uncheck checkbox value that I get from input and hit submit it writes NULL value in database. How can I solve those two things?

public function updateTvOptions(UpdateTvOptionsRequest $request)
{
    $user = Auth::user();

    $userProfile = UserProfile::where('user_id', Auth::id())->first();
    $userProfile->update($request->all());

    $data = $request->get('tvsOptions', '[]');
    
    $tv = UserTv::create(['name' => $request->name, 'custom' => 1]);
    $data[] = $tv->id;

    $userProfile->tvs()->sync($data);

    return redirect()->route('profile.show', [$user->username]);
}

bobbybouwmann

6 years ago

Level 88

So you need to limit the query for other users. So the best option is doing something like this

$userProfile = UserProfile::with(['tvs' => function ($query) {
    return $query->where('custom', 0);
}])->find(1);

In general, you need these constraints on a lot of different places. Since you want this to be specific for a customer, the custom column should maybe hold the user_id that created the tv.

It's pretty hard to explain all of this.

2 likes

dr24

6 years ago

Level 2

@bobbybouwmann

Ok, I understand now. And I now have only one problem. When I enter something in input text it stores in database and it shows in foreach in checkboxes. But when I later uncheck that value from checkbox and hit submit it writes NULL value in name column in tv table. How can I resolve this so that when input text left blank it doesn't store anything?

Please or to participate in this conversation.