在Laravel Eloquent模型中使用特殊的联合函数

Question

I've started developing a website using Laravel, and im pretty much finding everything through the official documentation and answers that I find here. However there is 1 thing that -even though i've found a way to do-, I have a feeling that could be done in another, more optimized way than the one I'm doing it right now. Let me explain.

For my website, I have a table called "players", which has data about some players extracted from a football game. Let's say the structure is like this:

---

ID (int, primary key, A_I)

GameID (int, unique) //what the game uses

PlayerName

Data (basically many different columns)

---

Since the purpose of my website is to allow users to do modifications on the game content, I also have another another table that I call "userplayers", which I use for doing modifications on the players that exist on the original table, or for adding new ones. The structure is like the "players" table, however with just one column added, called userID, which is to identify which modification belongs to which user.

---

ID (int, primary key, A_I)

GameID (int, unique together with userID)

PlayerName

Data (basically many different columns)

UserID (int, unique together with GameID)

---

If I add an entry on the userplayers table, if that entry has the same GameID as any entry on the players table (and the user that has created it is logged in), then on runtime it overwrites the players' table entry. If the GameID of the new entry doesnt exist on the original players table, then it just gets appended to it.

If the user is not logged in, then I just return the players table.

By using eloquent laravel model I can easily retrieve the players table for when the user is not logged in. However, I can't figure out an efficient way to return the whole DB + the user created content with just using core eloquent model functions.

In the past (without Laravel) I was using this DB query:

SELECT * FROM (SELECT *, NULL FROM players WHERE NOT EXISTS (SELECT * FROM userplayers WHERE players.gameid=userplayers.gameid AND userId=$userId) UNION (SELECT * FROM userplayers WHERE userId=$userId)) AS pl;

And the way I've "found" to do something like this in Laravel is by adding a local scope inside the Players model like this:

public function scopeIncludeCustom($query, $user)
{
    return \DB::select('SELECT * FROM (SELECT *, NULL FROM players WHERE NOT EXISTS (SELECT * FROM userplayers WHERE userplayers.gameid=players.gameid AND userId='.$user.') UNION (SELECT * FROM userplayers WHERE userId='.$user.')) AS players_full);
}

However you can understand that this doesn't return the $query as intended by scopes, but just a php array, which im returning back, and I think that's not the correct way to do this. For example, when I'm just searching the players table (without using user created content), it takes a much much shorter time to return results than returning results with the custom content.

Any ideas are hugely appreciated.

Answer 1

So, after some days of researching my issue and possible solutions, I came up with this solution.

So, lets take this step by step.

---

I had a "Player" model, that fetched data from the "players" table.

I also had a "Userplayer" model, that fetched data from the "userplayers" table.

---

I had to create a relation between those 2 models. An entry in the "players" table may have many entries related to them in the "userplayers" table. So, in the Player Model I added this:

public function userplayers()
{
    return $this->hasMany('App\Userplayer', 'gameid', 'gameid');
}

While in the Userplayer Model, I added this:

public function player()
{
    return $this->belongsTo('App\Player', 'gameid', 'gameid');
}

---

When requesting data, the first step was to remove every row from the "players" table that had the same "GameId" as any row returned from the "userplayers" table, which also had a restriction that the "userId" in every row of this table must be a specific one.

This is the code that does this in my SearchPlayerController:

$orig_players = \App\Player::whereDoesntHave('userplayers', function ($query) use($user) 
{
    $query->where('userId', $user);
})->get();

At the same time, I need to get every row from the "userplayers" table that has the "userid" I want

$userplayers = \App\Userplayer::where([
                ['pesid', $search]
            ])->get();

Now, I just merge the 2 results (I can't use UNION because the data I fetch from the "players" table has one less column).

$players = $orig_players->merge($userplayers)
            ->sortBy('registeredName')
            ->take($limit);

And everything works perfectly fine, and a lot faster than before!