I'm very new to Symfony.
As a User I only want to see the entities I've created. A User may login and create games but I only want to see the game entities I've created. It's a ManyToMany relation for User and Games, where User is the owning side.
Example: the game "Game for user Joop1" can only be viewed by user Joop1. The game with title "Game for Joop2" may only be viewed by Joop2.
If Joop1 is logged as the current user, how can I only show the games he has created with DQL/querybuilder?
I use FOSUserBundle.
Update: I've changed the relation to: User->Game: many-to-many.
Question: what is the dql/querybuilder script for this sql statement:
SELECT * FROM game,users_games WHERE users_games.user_id = 1
This is my current querybuilder script based on Manuel DUVERNON answer:
$em = $this->getDoctrine()->getManager();
$qb = $em->getRepository( Game::class )->createQueryBuilder( 'game_t' );
$qb->join( 'users_games.user_id', 'ug' )
->where( 'ug.user_id = :userId' )
->setParameter( 'userId', 1);
return $qb->getQuery()->getResult();
But this is not working because I get the following error:
[Semantical Error] line 0, col 70 near '.user_id ug WHERE': Error: Identification Variable users_games used in join path expression but was not defined before.
GameController
<?php
namespace AppBundle\Controller;
use AppBundle\Entity\Game;
use AppBundle\Entity\PlayLog;
use AppBundle\Entity\User;
use AppBundle\Form\GameType;
use Symfony\Bundle\FrameworkBundle\Controller\Controller;
use Sensio\Bundle\FrameworkExtraBundle\Configuration\Method;
use Sensio\Bundle\FrameworkExtraBundle\Configuration\Route;
use Symfony\Component\Form\Extension\Core\Type\SubmitType;
use Symfony\Component\Form\Extension\Core\Type\DateType;
use Symfony\Component\HttpFoundation\Request;
/**
* Game controller.
*
* @Route("game")
*/
class GameController extends Controller
{
/**
* Lists all game entities.
*
* @Route("/", name="game_index")
* @Method("GET")
*/
public function indexAction(Request $request)
{
//get user_id
//
$usr = $this->getUser();
$id = $usr->getId();
//
// $em = $this->getDoctrine()->getManager();
//// $dql="SELECT g FROM AppBundle\Entity\Game g Join g.users u WHERE u.id := user_id";
// $dql = "SELECT game FROM AppBundle:Game game ";
// $query = $em->createQuery($dql);
// $query->setParameter('user_id', $id);
// $current_games = $query->getResult();
// $paginator = $this->get('knp_paginator');
// $result = $paginator->paginate(
//
// $request->query->getInt('page', 1),
// $request->query->getInt('limit', 25)
// );
// dump(get_class($paginator));
// $em = $this->getDoctrine()->getManager();
//
// $current_games = $em->getRepository('AppBundle:Game')->findAll();
// $em = $this->getDoctrine()->getManager();
//
// $current_games = $em->getRepository('AppBundle:Game')->findAllOrdered($id);
//
$em = $this->getDoctrine()->getManager();
$qb = $em->getRepository( Game::class )->createQueryBuilder( 'game_t' );
$qb->join( 'users_games.user_id', 'ug' )
->where( 'ug.user_id = :userId' )
->setParameter( 'userId', $id);
return $qb->getQuery()->getResult();
return $this->render('game/index.html.twig', array(
'games' => $current_games,
'max_limit_error' => 25
));
}
/**
* Creates a new game entity.
*
* @Route("/new", name="game_new")
* @Method({"GET", "POST"})
*/
public function newAction(Request $request)
{
$game = new Game();
$form = $this->createForm('AppBundle\Form\GameType', $game);
$form->handleRequest($request);
$game->setUser($this->getUser());
if ($form->isSubmitted() && $form->isValid()) {
$em = $this->getDoctrine()->getManager();
$em->persist($game);
$em->flush($game);
return $this->redirectToRoute('game_show', array('id' => $game->getId()));
}
return $this->render('game/new.html.twig', array(
'game' => $game,
'form' => $form->createView(),
));
}
/**
* Finds and displays a game entity.
*
* @Route("/{id}", name="game_show")
* @Method("GET")
*/
public function showAction(Game $game)
{
$deleteForm = $this->createDeleteForm($game);
return $this->render('game/show.html.twig', array(
'game' => $game,
'delete_form' => $deleteForm->createView(),
));
}
/**
* Displays a form to edit an existing game entity.
*
* @Route("/{id}/edit", name="game_edit")
* @Method({"GET", "POST"})
*/
public function editAction(Request $request, Game $game)
{
$deleteForm = $this->createDeleteForm($game);
$editForm = $this->createForm('AppBundle\Form\GameType', $game);
$editForm->handleRequest($request);
if ($editForm->isSubmitted() && $editForm->isValid()) {
$this->getDoctrine()->getManager()->flush();
return $this->redirectToRoute('game_show', array('id' => $game->getId()));
}
return $this->render('game/edit.html.twig', array(
'game' => $game,
'edit_form' => $editForm->createView(),
'delete_form' => $deleteForm->createView(),
));
}
/**
* Displays a form to edit an existing game entity.
*
* @Route("/{id}/log", name="game_log")
* @Method({"GET", "POST"})
*/
public function addLogAction(Request $request, Game $game)
{
$playlog = new PlayLog();
$form = $this->createForm(GameType::class, $game);
$form->handleRequest($request);
if($form->isSubmitted() && $form->isValid()) {
//Save playLog
$em = $this->getDoctrine()->getManager();
$em->persist($playlog);
$em->flush();
}
// Render / return view incl. formulier.
return $this->render('game/log.html.twig', array(
'game' => $game,
'form' => $form->createView(),
));
}
/**
* Deletes a game entity.
*
* @Route("/{id}", name="game_delete")
* @Method("DELETE")
*/
public function deleteAction(Request $request, Game $game)
{
$form = $this->createDeleteForm($game);
$form->handleRequest($request);
if ($form->isSubmitted() && $form->isValid()) {
$em = $this->getDoctrine()->getManager();
$em->remove($game);
$em->flush($game);
}
return $this->redirectToRoute('game_index');
}
/**
* Creates a form to delete a game entity.
*
* @param Game $game The game entity
*
* @return \Symfony\Component\Form\Form The form
*/
private function createDeleteForm(Game $game)
{
return $this->createFormBuilder()
->setAction($this->generateUrl('game_delete', array('id' => $game->getId())))
->setMethod('DELETE')
->getForm()
;
}
}
Game Entity
<?php
namespace AppBundle\Entity;
use Doctrine\ORM\Mapping as ORM;
use Doctrine\Common\Collections\ArrayCollection;
use Symfony\Component\Validator\Constraints as Assert;
/**
* Game
*
* @ORM\Table(name="game")
* @ORM\Entity(repositoryClass="AppBundle\Repository\GameRepository")
*/
class Game
{
/**
* @ORM\OneToMany(targetEntity="PlayLog", mappedBy="game")
* @ORM\OrderBy({"date" = "DESC"})
*
*/
private $playlogs;
public function __construct()
{
$this->playlogs = new ArrayCollection();
}
/**
* @ORM\ManyToOne(targetEntity="Type", inversedBy="games")
* @ORM\JoinColumn(name="type_id", referencedColumnName="id")
*/
private $type;
/**
* @ORM\ManyToOne(targetEntity="User", inversedBy="games")
*/
private $user;
/**
* @var int
*
* @ORM\Column(name="id", type="integer")
* @ORM\Id
* @ORM\GeneratedValue(strategy="AUTO")
*/
private $id;
/**
* @var string
* @Assert\NotBlank()
* @Assert\Length(
* min = "3",
* max = "100"
* )
* @ORM\Column(name="name", type="string", length=255, unique=true)
*/
private $name;
/**
* Get id
*
* @return int
*/
public function getId()
{
return $this->id;
}
/**
* @return mixed
*/
public function getUser()
{
return $this->user;
}
/**
* @param mixed $user
*/
public function setUser($user)
{
$this->user = $user;
}
/**
* Set name
*
* @param string $name
*
* @return Game
*/
public function setName($name)
{
$this->name = $name;
return $this;
}
/**
* Get name
*
* @return string
*/
public function getName()
{
return $this->name;
}
/**
* @return mixed
*/
public function getType()
{
return $this->type;
}
/**
* @param mixed $type
*/
public function setType($type)
{
$this->type = $type;
}
/**
* @return mixed
*/
public function getPlaylogs()
{
return $this->playlogs;
}
/**
* @param mixed $playlogs
*/
public function setPlaylogs($playlogs)
{
$this->playlogs = $playlogs;
}
public function addPlayLog(PlayLog $playlog)
{
$this->playlog->add($playlog);
$playlog->setPlayLogs($this);
}
}
You can just get the current logged user, then request your dql query using that user id .
Take a look :
$usr= $this->getUser();
$id=$usr->getId();
$em=$this->getDoctrine()->getManager();
$dql="SELECT * games FROM Game WHERE Game.user_id := user_id";
$query = $em->createQuery($dql);
$query->setParameter('user_id',$id);
$current_games = $query->getResult();