Subquery inside the 'FROM' statement in doctrine?

Question

I would like to write this SQL query

select id FROM
(SELECT article.id AS id, MATCH(titre, intro, contenu) AGAINST ('query') as score FROM article ORDER BY score DESC) t1 
where score>0

in doctrine with a querybuilder subquery.

I'am using a doctrine extension to interpret the MATCH AGAINST.

I cannot find the doctrine/querybuilder syntax to create the subquery inside the 'FROM'

So I do :

 $this->createQueryBuilder('a')
            ->andWhere('MATCH(a.titre, a.intro, a.contenu) AGAINST (:q boolean) >0')
            ->orderBy('MATCH(a.titre, a.intro, a.contenu) AGAINST (:q boolean)', 'DESC')
            ->setParameter('q', $query)

This syntax is working but i would like to do it efficiently without repeating the 'MATCH(a.titre, a.intro, a.contenu) AGAINST (:q boolean) twice.

So I need to write the subquery version.

mkilmanas mkilmanas · Accepted Answer · 2019-11-21T11:33:43

I think there are at least 2 approaches here and neither requires a subquery:

1) You can use HAVING instead of WHERE and reference the selected column name (+ hide that column from results with HIDDEN keyword). Though this might be less performant as it would do condition matching and sorting on the already selected data, so it is not available for query optimizer to optimize it.

$this->createQueryBuilder('a')
    ->select('a.id, HIDDEN MATCH(a.titre, a.intro, a.contenu) AGAINST (:q boolean) AS score')
    ->andHaving('score > 0')
    ->orderBy('score', 'DESC')
    ->setParameter('q', $query);

2) If repetition is the only issue you are having, just define the repeated part as a variable and use that instead:

$score = 'MATCH(a.titre, a.intro, a.contenu) AGAINST (:q boolean)'
$this->createQueryBuilder('a')
        ->select('a.id')
        ->andWhere("{$score} > 0")
        ->orderBy($score, 'DESC')
        ->setParameter('q', $query)

As for repetition in the query causing performance penalty - as far as I remember you don't need to worry about it - MySQL should handle that for you by reusing the same value (not recalculating it). In other words it would not be doing the matching twice just because it is present in your query twice - as long as the expression is identical, optimizer will take of that. (Although now I can't find the exact place in the docs where it dais that)

Subquery inside the 'FROM' statement in doctrine?

1 Answers