Creating more comprehensive associations with Querybuilder in Symfony-CodePudding

I'm building a query that would be for creating a list of Posts that have a Project that is associated to the user, and within that structure hit the right criteria for "tierAccess."

My query builder:

$qb = $this->em->createQueryBuilder();

        foreach($subs as $sub)
        {
            if($sub->getDisabled() == true)
            {
                continue;
            }

            $qb->select('p')
               ->from('App\Entity\ProjectPost', 'p')
               ->where('project = '.$sub->getProject()->getId())
               ->andwhere('p.Published = true')
               ->andwhere('p.TierAccess = '.$sub->getProjectTier()->getId())
               ->orderBy('p.PostTime', 'DESC');


            $query = $qb->getQuery();

           $object[] = $query->execute();
        }

What I am aiming to do is add posts that the user subscription will allow for, and within that subscription making sure their access to this post is allowed (ie: tierAccess).

I then return the object variable to pass along to my Twig template file.

The error I'm receiving is:

[Semantical Error] line 0, col 45 near 'project = 3 AND': Error: 'project' is not defined.

My ProjectPost entity:

class ProjectPost
{
    /**
     * @ORM\Id
     * @ORM\GeneratedValue
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\Column(type="string", length=255)
     */
    private $PostTitle;

    /**
     * @ORM\Column(type="text", nullable=true)
     */
    private $PostHero;

    /**
     * @ORM\Column(type="string", length=255, nullable=false)
     */
    private $PostType;

    /**
     * @ORM\Column(type="text")
     */
    private $PostBody;

    /**
     * @ORM\ManyToOne(targetEntity=Project::class, inversedBy="projectPosts")
     * @ORM\JoinColumn(nullable=false)
     */
    private $Project;

    /**
     * @ORM\Column(type="array", nullable=true)
     */
    private $TierAccess = [];

    /**
     * @ORM\Column(type="datetimetz", nullable=true)
     */
    private $PostTime;

    /**
     * @ORM\ManyToOne(targetEntity=User::class, inversedBy="projectPosts")
     * @ORM\JoinColumn(nullable=true)
     */
    private $PostBy;

    /**
     * @ORM\Column(type="array", nullable=true)
     */
    private $PostCategories = [];

    /**
     * @ORM\Column(type="boolean")
     */
    private $Published;

    /**
     * @ORM\Column(type="string", length=255, nullable=true)
     */
    private $PostCover;

    /**
    * @ORM\Column(type="boolean")
    */
    private $PostSupporter = 0;
}

CodePudding user response：

The basic mistake is this one:

->where('p.Project = '.$sub->getProject()->getId())

Notice that you declare p to be the alias of Post, and then you don't use it. And even if you define the property as Project, you were trying to use it as project.

Nevertheless, the whole thing is rather suspect. Executing a query within a loop usually points to something wrong with the design.

A simpler approach, using WHERE IN instead of a loop and multiple selects:

// get the "subs" ids in an array:
$subsIds = array_map(fn($s) => $s->getProject()->getId(), $subs); 

qb->select('p')
   ->from('App\Entity\ProjectPost', 'p')
   ->where('p.Project IN :subsIds')
   ->andwhere('p.Published = true')
   ->andwhere('p.TierAccess = '.$sub->getProjectTier()->getId())
   ->orderBy('p.PostTime', 'DESC')
   ->setParameter('subsIds', $subsIds)
;

$result = $qb->getQuery()->getResult;