Left join the same right.table.id_field to TWO left.table.field_1 and left.table.field_2

ggarciaa

I have two tables: teams, and players. (equpos and jugadores)
teams table structure: id, player_1_id, player_2_id (equipos.id_jugador_1 and equipos.id_jugador_2)
players table structure: id, name. (actually id, apellido, nombre, but that doesn't change things)
Each team has exactly two players. Players are selected from the players table. Each record in teams table has a unique team id, and has two different player ids, belonging to the players in that team.
I want to edit the teams table. player_1 should have a select drop down, populated from the players table. Edit field should be players.id, but the form should display players.name for the selected id. This is done with a left join. No problem. Now I want the second player to have his own select drop down list, populated from the very same table players, because both of them are players, and I store all of them on the same table.
I tried to add a second ->leftJoin() changing the condition. but against the same table.
I keep getting a PHP error:
"An SQL error occurred: SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'players'"

Here's my code:

    Editor::inst($db, 'equipos')
    ->field(
    Field::inst('equipos.numero'),
    Field::inst('equipos.id_torneo')
    ->options(Options::inst()
    ->table('torneos')
    ->value('id')
    ->label('nombre')
    )
    ->validator('Validate::dbValues'),
    Field::inst('equipos.id_jugador_1')
    ->options(Options::inst()
    ->table('jugadores')
    ->value('id')
    ->label(['apellido', 'nombre'])
    ->render(function ($row){return $row['apellido'].', '.$row['nombre'];})
    ),
    Field::inst('equipos.id_jugador_2')
    ->options(Options::inst()
    ->table('jugadores')
    ->value('id')
    ->label(['apellido', 'nombre'])
    ->render(function ($row){return $row['apellido'].', '.$row['nombre'];})
    )
    )
    ->leftJoin('torneos', 'torneos.id', '=', 'equipos.id_torneo')
    ->leftJoin('jugadores', 'jugadores.id', '=', 'equipos.id_jugador_1')
    ->leftJoin('jugadores', 'jugadores.id', '=', 'equipos.id_jugador_2')
    ->process($_POST)
    ->json();

The code has Spanish names. jugador = player, team = equipo. Also, I put my question in the simplest terms I could find, so the code is slighthly more complex. I hope it'll be fine.
Thanks.

allan

You can use an as alias in the leftJoin's first parameter to address this:

->leftJoin('jugadores2', 'jugadores2.id', '=', 'equipos.id_jugador_2')

There is an example of the alias being used here.

Allan

mankramo

Hi Alan, I am having a very similar challenge here as @ggarciaa narrated above (so no need to illustrate further ). Two different tables just like team and players table. Team has say four columns, first for the primary key and the other three for the players that make up the team. I tried following your example but i realized ( if not mistaking ) the example you gave relates to a leftjoin on a single table. But in this instance we are calling left join on two different tables . tagged @Alan & @rf1234

mankramo

@allan

mankramo

https://datatables.net//forums/discussion/comment/81166/#Comment_81166

I think the above link has the hack

allan

You should be able to use leftJoin multiple times with different aliases for each. Have you tried that, and if so, can you show me that code and any error messages that it is showing (since I'm presuming it isn't working as you've posted here).

Thanks,
Allan