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.