Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
I have this sql query that works perfectly (returns one row):
SELECT player_stats.*, opposing_team.nombre AS opposing_team_name
FROM player_stats
JOIN partidos ON partidos.id = player_stats.partido_id
JOIN equipos AS player_team ON (
(player_team.id = partidos.equipo_local_id OR player_team.id = partidos.equipo_visitante_id)
AND EXISTS (
SELECT 1
FROM equipos_jugadores
WHERE equipo_id = player_team.id
AND jugador_id = player_stats.jugador_id
)
)
JOIN equipos AS opposing_team ON (
opposing_team.id <> player_team.id
AND (
opposing_team.id = partidos.equipo_local_id
OR
opposing_team.id = partidos.equipo_visitante_id
)
)
WHERE player_stats.jugador_id = 337;
I want to change it to eloquent and i am using this code:
PlayerStat::query()
->join('partidos', 'partidos.id', '=', 'player_stats.partido_id')
->join('equipos as player_team', function ($join) {
$join->on(function ($query) {
$query->where('player_team.id', '=', 'partidos.equipo_local_id')
->orWhere('player_team.id', '=', 'partidos.equipo_visitante_id');
})
->whereExists(function ($query) {
$query->from('equipos_jugadores')
->where('equipo_id', '=', 'player_team.id')
->where('jugador_id', '=', 'player_stats.jugador_id');
});
})
->join('equipos as opposing_team', function ($join) {
$join->on('opposing_team.id', '<>', 'player_team.id')
->where(function ($query) {
$query->where('opposing_team.id', '=', 'partidos.equipo_local_id')
->orWhere('opposing_team.id', '=', 'partidos.equipo_visitante_id');
});
})
->select('player_stats.*', 'opposing_team.nombre as opposing_team_name')
->where('player_stats.jugador_id', '=', $this->jugador_id);
but it doesnt give any rows, any ideas?
Your code is quite unreadable because it's not formatted. Use three backticks (`) to add code blocks to your post.
When you've built your query, you can call ->toSql() on it to get the compiled SQL code. Print that out and see how it differs from the SQL you intended.
The statement you showed builds the query but doesn't run it. Do you call ->get() later to execute it?
@JussiMannisto thanks, i change it now. I am using the Builder to pass a query to rappasoft tables and I have no idea how to use ->toSql() on that scenario
@manuelpmgt You can get the SQL code for debugging like this:
$sql = PlayerStat::query()
->join('partidos', 'partidos.id', '=', 'player_stats.partido_id')
....
->toSql();
Then just dump that and see how it differs from your original SQL.
Or if you have Telescope installed, you can just check the queries page.
Or if you use Debugbar, you can check the queries tab.
@JussiMannisto found the problem, couldn't had done without telescope. The problem is that when the query is created, it encases all the query in backticks and there are some values that have to be pass as raw, so i changed it to:
PlayerStat::query()
->join('partidos', 'partidos.id', '=', 'player_stats.partido_id')
->join('equipos as player_team', function ($join) {
$join->on(function ($query) {
$query->where('player_team.id', '=', DB::raw('partidos.equipo_local_id'))
->orWhere('player_team.id', '=', DB::raw('partidos.equipo_visitante_id'));
})
->whereExists(function ($query) {
$query->from('equipos_jugadores')
->where('equipo_id', '=', DB::raw('player_team.id'))
->where('jugador_id', '=', DB::raw('player_stats.jugador_id'));
});
})
->join('equipos as opposing_team', function ($join) {
$join->on('opposing_team.id', '<>', 'player_team.id')
->where(function ($query) {
$query->where('opposing_team.id', '=', DB::raw('partidos.equipo_local_id'))
->orWhere('opposing_team.id', '=', DB::raw('partidos.equipo_visitante_id'));
});
})
->select('player_stats.*', 'opposing_team.nombre as equipo')
->where('player_stats.jugador_id', '=', $this->jugador_id);
and its working like a charm. Thank you!
@manuelpmgt No problem. Telescope and debugbar are both great for debugging and optimization.
Please or to participate in this conversation.