XF 2.0 Trying to figure out this query how to write it. Need help...

[ludak]

Just to say I come from .NET background and would know how to do this simply in .NET however Php I am just picking up, together with XENFORO stuff and trying to make sence of it.

all I am trying to do is get all the lets call the entity again Players that are (this time) not playing the Games entity.

$players = $this->finder('XX:Players')
            ->with('PlayerGamesLink', true)
            ->where('PlayerGamesLink.game_id', $game_id)->fetch();

This above will get me all the players that are playing the game, since they are all linked in the playergameslink table.

I am having hard time getting all the players from Players table, that are not already assigned to the game.

So query would be something like this

SELECT * FROM Players P
left join PlayerGamesLink PGL
on P.player_id = PGL.player_id
where PGL.player_id == null

HM, this might be achivable to do it... but is there a better way to do this? I am thinking there's gotta be an easier way to pull just the players I need.

 

[ludak]

actually I did some thinking and this query will not be sufficient. This is what I am looking to do with finder. How to do this? Please help.

SELECT * FROM player P
where not exists(
    select * from gameplayerlink g
where g.player_id == p.player_id && g.game_id= 1)

Game id would be passed in... Please help!! Anybody, this seems to me such a simple query, need to get a boost start

 

[Mouth]

SELECT *
FROM players
LEFT OUTER JOIN gameplayerlink
ON (players.player_id = gameplayerlink.player_id)
WHERE gameplayerlink.player_id IS NULL
AND gameplayerlink.game_id = x

 

[ludak]

No, I have this query, this is what I need.

SELECT * FROM player P
where not exists(
    select * from gameplayerlink g
where g.player_id == p.player_id && g.game_id= 1)

I want to write this in such way like this to get the player entity and fetch(), is that even possible? Something like this?

$players = $this->finder('XX:Players')
            ->with('PlayerGamesLink', true)
            ->where('PlayerGamesLink.game_id', $game_id)->fetch();

just with the query I have above? Does anybody know how to do this, I dont want to execute the sql query if I dont have to. Obviously this code above does not do the trick as I am joining to the gameslink table 1 to 1 (with the join), I need to be able to do

where not exists

Is this even possible to be done... I am hitting my head against the wall here

 

[ludak]

SELECT *
FROM players
LEFT OUTER JOIN gameplayerlink
ON (players.player_id = gameplayerlink.player_id)
WHERE gameplayerlink.player_id IS NULL
AND gameplayerlink.game_id = x

By the way this query would be able to be written in such way, but its not correct. It does not return anything, as there are no records with such criteria.

 

[ludak]

Ok,

So I was able to do this with 2 finder queries. @Chris D / @Brogan (Some of the xenforo guys), can you please tell me if there is an easier way to do this.

$playersAssigned = $this->finder('XX:Players')
            ->with('GamesPlayersLink', true)
            ->where('GamesPlayersLink.game_id', $game_id)->fetch();

        $playersLeft = $this->finder('XX:Players')
            ->where('player_id', '<>', array_keys($playersAssigned->toArray()))->fetch();

Thanks!!!

 

[Chris D]

There's probably a few ways to do this. This is another approach that uses PHP to filter:

$players = $this->finder('XX:Players')->with('GamesPlayersLink')->fetch();

$playersAssigned = $players->filter(function(XX\Entity\Players $player) use ($game_id)
{
    return ($game_id == $player->GamesPlayersLink->game_id);
});

$playersLeft = $players->filter(function(XX\Entity\Players $player)
{
    return !$player->GamesPlayersLink;
});

 

[ludak]

Thanks @Chris D for answer. I am just trying to grasp the concepts since I am newbie when it comes to php and dont want to pile up code that is not necessary unless needed. So there is no way to achieve this with 1 single filter.. Basically there is not a way to do where not exists without writing the query and doing the execute on query itself against db?

 

[Chris D]

If the objective is to get a list of players who are part of game 1 and a separate list of players who aren't, then I'm not sure I can see how that can be done with a single query.

If the objective is to only get a list of players who are not in game 1 then I guess it would be:

$playersLeft = $this->finder('XX:Players')
    ->with('GamesPlayersLink')
    ->where('GamesPlayersLink.player_id', NULL)
    ->where('GamesPlayersLink.game_id', 1)
    ->fetch();

I think that's it, as long as I'm understanding what you need correctly.

 

[ludak]

What I am trying to do is to have an action to add more players to the game. So just 1 query. In sql this is what I am trying to get.

SELECT * FROM player P
where not exists(
    select * from gameplayerlink g
where g.player_id == p.player_id && g.game_id= 1)

So when I pop the add new players action, I dont want to see the list fo the players that are already playing the game for the game_id

I jsut want to see the players who are not playing the game, so I can select them and add them to the game. Reason why I was doing two queries is because I did not know how to execute his query single time to return the list of players.

Hope it makes sense.

 

[ludak]

$playersLeft = $this->finder('XXlayers') ->with('GamesPlayersLink') ->where('GamesPlayersLink.player_id', NULL) ->where('GamesPlayersLink.game_id', 1) ->fetch();

Btw, this does not return correct items. It returns an empty set while the query above returns 2 records (which is what it should be returned)