symfony - doctrine - Recover all the data of my bdd except a few

Question

I need help for queryBuilder in doctrine.

I search a queryBuilderMethod for, get all data except few.

for example in sql with one table its something like this :

"select * from table Where user != "a" && user != "b" && user != c [...]"

i have 3 entity in my symfony apps :

User => one to many => Photo => one to many => RatingsPhoto

I want to get a user who has not yet rated by the current user.

the code below:

User

class User extends BaseUser
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;
    /** @ORM\Column(name="facebook_id", type="string", length=255, nullable=true) */
    protected $facebook_id;
    /** @ORM\Column(name="facebook_access_token", type="string", length=255, nullable=true) */
    protected $facebook_access_token;
/** @ORM\Column(name="sex_target", type="integer", nullable=true) */
protected $sex_target;
/** @ORM\Column(name="sex", type="integer", nullable=true) */
protected $sex;

/**
 * @ORM\OneToMany(targetEntity="Photo", mappedBy="user")
 */
protected $photo;

/**
 * @ORM\OneToMany(targetEntity="RatingsPhoto", mappedBy="userMap",
 * cascade={"persist", "remove"})
 */
protected $ratingsUser;

public function __construct()
{
    $this->photo = new ArrayCollection();
    $this->ratingsUser = new ArrayCollection();
}

}

Photo

class Photo
{
    /**
     * @var int
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /**
     * @ORM\ManyToOne(targetEntity="User")
     * @ORM\JoinColumn(name="user", referencedColumnName="id", nullable=false)
     */
    protected $user;

/**
 * @ORM\OneToMany(targetEntity="RatingsPhoto", mappedBy="photoMap",
 * cascade={"persist", "remove"})
 */
protected $ratingsPhoto;

/**
 * @var int
 *
 * @ORM\Column(name="numeroPlayer", type="integer", nullable=true)
 */
protected $numeroPlayer;

/**
 * @var int
 *
 * @ORM\Column(name="nbrChoosen", type="integer", nullable=true)
 */
protected $nbrChoosen;

/**
 * @var string
 *
 * @ORM\Column(name="photoName", type="string", length=255, unique=true,  nullable=false)
 */
protected $photoName;

/**
 * @ORM\Column(type="string", length=255, nullable=false)
 * @var string
 */
protected $image;

/**
 * @var \DateTime
 *
 * @ORM\Column(name="dateAdd", type="datetime")
 */
protected $dateAdd;
/**
 * Constructor
 */
public function __construct()
{
    $this->ratingsPhoto = new ArrayCollection();
}

}

RatingsPhoto

class RatingsPhoto {

public function __construct()
{

}

/**
 * @var int
 *
 * @ORM\Column(name="id", type="integer")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 */
protected $id;

/**
 * @ORM\ManyToOne(targetEntity="User", inversedBy="ratingsUser")
 * @ORM\JoinColumn(name="user", referencedColumnName="id", nullable=false, onDelete="CASCADE")
 */
protected $userMap;

/**
 * @ORM\ManyToOne(targetEntity="Photo", inversedBy="ratingsPhoto")
 * @ORM\JoinColumn(name="photo", referencedColumnName="id", nullable=false, onDelete="CASCADE")
 */
protected $photoMap;

/**
 * @var int
 *
 * @ORM\Column(name="note", type="integer", nullable=false)
 */
protected $note;

/**
 * @var \DateTime
 *
 * @ORM\Column(name="dateNote", type="datetime", nullable=false)
 */
protected $dateNote;

Request query i have try but doesent work

$result = $this->getEntityManager()
->createQuery(
    'SELECT p, u, r
     FROM AppBundle:Photo p
     LEFT JOIN p.user u
     LEFT JOIN p.RatingsPhoto r
     WHERE u != :user
     AND r.user != :user
     AND u.sex = :sex
     order By Rand()'
)
->setParameters(array('user' => $user, 'sex' => $user
->getSexTarget()))
->getResult(\Doctrine\ORM\AbstractQuery::HYDRATE_ARRAY);

you do not expect us to do all the work or? How does the query builder method you have tried looks like? — lordrhodos, Jun 14 '17 at 15:16
no of course :p, i search just the most optimized method and in one request, i can do that with 2 or 3 request and a loop i guess but its not optimized. — Arthur Crosnier, Jun 14 '17 at 15:25
please add the code to the question and not the comments. You can edit it ;-) — lordrhodos, Jun 14 '17 at 15:29

score 1 · Accepted Answer · answered Jun 14 '17 at 19:12

Just to clarify things a little bit first. This is not 100% correct:

User => one to many => Photo => one to many => RatingsPhoto

What you have there is a classical Many-to-many relation with additional attributes, wich is not a m:n relation any more, but introduces your dedicated relational entity RatingsPhoto

So what you basically have is

User <OneToMany> RatingsPhoto <ManyToOne> Photo

On top of that each user can have multiple photos, which is kind of an ownership for Photo

User <OneToMany> Photo

First a small suggestion, rename the User:photo attribute to User:photos so it reflects the correct relation.

/**
 * @ORM\OneToMany(targetEntity="Photo", mappedBy="user")
 */
protected $photos;

Regarding your query:

I want to get a user who has not yet rated by the current user.

you should build your query using a subquery, like get all users that are not in (a list of users which photos have been rated by the current user):

You can try something like this (untested):

$result = $this->getEntityManager()
    ->createQuery(
        'SELECT u
         FROM AppBundle:User u
         WHERE u.id not in 
             (
                 SELECT ru.i FROM AppBundle:RatingsPhoto r
                 LEFT JOIN u.photos p,
                 LEFT JOIN p.user ru
                 WHERE r.userMap = :user
             )
         AND u.sex = :sex
         order By Rand()'
    )
    ->setParameters(array('user' => $user, 'sex' => $user
    ->getSexTarget()))
    ->getResult(\Doctrine\ORM\AbstractQuery::HYDRATE_ARRAY);

Thanks a lot ! your reply have lot of help me ! I have found something bit similar i post that below. — Arthur Crosnier, Jun 15 '17 at 13:32

score 1 · Answer 2 · answered Jun 15 '17 at 13:33

Finally i found the answer, i put code below if that can help someone :

$result = $queryBuilder
    ->select(['p, ru, u'])
    ->from('AppBundle:Photo', 'p')
    ->leftJoin('p.ratingsPhoto', 'ru')
    ->leftJoin('p.user', 'u')
    ->where('ru.id IS NULL')
    ->Orwhere($queryBuilder->expr()->not($queryBuilder->expr()->exists('
            SELECT sr.id
            FROM AppBundle:RatingsPhoto sr
            WHERE sr.userMap = :user'
    )))
    ->andWhere('u.sex = :sex')
    ->setParameters(array('user' => $user, 'sex' => $user->getSexTarget()))
    ->getQuery()
    ->getResult(\Doctrine\ORM\AbstractQuery::HYDRATE_ARRAY)
;

symfony - doctrine - Recover all the data of my bdd except a few

2 Answers2