I have following relationship

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

/**
 * @ORM\OneToMany(targetEntity=Tags::class, mappedBy="image",cascade={"persist"})
 */
private $tags;

and

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

/**
 * @ORM\ManyToOne(targetEntity=Image::class, inversedBy="tags")
 */
private $imageStock;

In my search form when I need to do a searching I must get the image which is matched to search inputs. The Search is done either by Image Name or Tag Name. I have following table.

Image

 id|image_name
  1|Spider Man

Tags

 id|tag_name  |image_id
  1|Spider Man|1
  2|Movie     |1
  3|Xyz       |1

I'm confused how to do query in Repository.

This is what I have tried:

       $query = $this->getEntityManager()->createQueryBuilder('ca1')
    ->add('select', 'c, i')
    ->add('from', 'App:Image c')
    ->leftJoin('c.tags', 'i')
    ->where('c.image_name = :search_name')
    ->orderBy('c.id', 'ASC')
    ->setParameters([
        'search_name' => "%$search_name%",
    ])
    ->getQuery();

return $query->getResult();

CodePudding user response：

If in image repository it would be something like this:

    $qb = $this->createQueryBuilder('c')
        ->leftJoin('c.tags', 'i')
        ->orderBy('c.id', 'ASC')
        ;

    return $qb
        ->andWhere(
            $qb->expr()->orX(
                $qb->expr()->like('c.image_name', ':search_name'),
                $qb->expr()->like('i.tag_name', ':search_name'),
            )
        )
            ->setParameter('search_name', "%$search_name%")
        ->getResult()
        ;

And it is better to follow camelCase for your parameters like $imageName and $tagName, and so on;

CodePudding user response：

You have to use LIKE instead of =:

/** @var Image[] $images */
$images = $this->getEntityManager()
    ->createQueryBuilder()
    ->from(Image::class, 'image')
    ->select('image')
    ->leftJoin('image.tags', 'imtag')
    ->addSelect('imtag') // Optional, use only if you want to access tags.
    ->where('image.image_name LIKE :search_name')
    ->orWhere('imtag.tag_name LIKE :search_name')
    ->setParameter('search_name', "%$search_name%")
    ->orderBy('image.id', 'ASC')
    ->getResult()
;

• Doctrine query builder is parsed to DQL, you can retrieve the DQL via getDQL() on the query builder.
• You can view the executed SQL query in the Symfony profiler in the Doctrine section.
• As mentioned in the comment, at first it is easier to write SQL and then DQL.