Full text search (match against) with ManyToMany relation

I have a following problem to solve and looking for best solution for it. I do have a table called person and I do have table language. Between these two tables there is ManyToMany relation. Right now I would like to have possibility to use fulltext benefits (MATCH AGAINST) and give the user possibility of searching for people and languages that they are speaking by simply typing for example: london french -spanish so it should find all people that are based in London and speaking french, but not spanish. I’m looking for a way now how I can combine fulltext and ManyToMany together to make it work. I was thinking about JOIN, but I’m not sure if that will work or will be performant enough. How can I solve it in a smart way?

Source code of both tables:

Person.php

/**
 * @ORMEntity(repositoryClass="AppRepositoryPersonRepository")
 * @ORMTable(indexes={@ORMIndex(columns={"city", "first_name", "last_name"}, flags={"fulltext"})})
 *
 * @ORMHasLifecycleCallbacks()
 */
class Person
{
    /**
     * @ORMId()
     * @ORMGeneratedValue()
     * @ORMColumn(type="integer")
     */
    private $id;
    
    /**
     * @ORMColumn(type="string", length=100)
     */
    private $firstName;

    /**
     * @ORMColumn(type="string", length=100)
     */
    private $lastName;

    /**
     * @ORMColumn(type="string", length=100)
     */
    private $city;

    /**
     * @ORMManyToMany(targetEntity=Language::class, inversedBy="people")
     */
    private $langs;

// (...)

Language.php

/**
 * @ORMEntity(repositoryClass=LanguageRepository::class)
 * @ORMTable(indexes={@ORMIndex(columns={"name"}, flags={"fulltext"})},
 *    uniqueConstraints={
 *        @UniqueConstraint(name="unique_languages",
 *            columns={"name", "slug"})
 *    }
 * )
 */
class Language
{
    /**
     * @ORMId()
     * @ORMGeneratedValue()
     * @ORMColumn(type="integer")
     */
    private $id;

    /**
     * @ORMColumn(type="string", length=255)
     */
    private $name;

    /**
     * @ORMColumn(type="string", length=255)
     */
    private $slug;

    /**
     * @ORMManyToMany(targetEntity=Person::class, mappedBy="langs")
     */
    private $people;

Source: Symfony Questions

Was this helpful?

0 / 0

Leave a Reply 0

Your email address will not be published. Required fields are marked *